VBA - Bulk Reporting; including subsets

[l_stoker]

Good Afternoon,

I'm looking for some guidance on amending the following VBA code to not only create reports based on department codes which are n level items, but also create reports based on subsets which amalgamate numerous departments - for those managers controlling larger teams.

Any advice would be appreciated. I'm currently struggling to see what i need to update and how.

Many thanks in advance

Sub BulkReport()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=359
'+ admin@bihints mods
'+ some of Martin Ryan code
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
Dim TM1Element As String
Dim i As Integer
Dim myDim As String
Dim server As String
Dim fullDim As String
Dim total As Long
Dim folder As String
Dim destination As String
Dim Department, RowStart, RowEnd As Long
Dim ThisWorkbook, ThatWorkbook, OpexCurrency, Comparison, Company, OpexMeasure, Period As String
Dim OpexDept, Year As Long

destination = Sheets("Front Sheet").Range("F12")
server = "domforecast2"
myDim = "OraDept"
fullDim = server & ":" & myDim
If Run("dimix", server & ":}Dimensions", myDim) = 0 Then
MsgBox "The dimension does not exist on this server"
Exit Sub
End If

'loop over all elements of the branch dimension
For i = 1 To Run("dimsiz", fullDim)

TM1Element = Run("dimnm", fullDim, i)

'Name reference points for use later in macro

Sheets("Front Sheet").Select

ThisWorkbook = ActiveWorkbook.Name
Period = Range("F5")
Year = Range("F6")
Company = Range("F7")
Comparison = Range("F8")
OpexCurrency = Range("F9")
VVersion = Range("F10")
OpexMeasure = Range("F11")

'see if there are any sales for that branch

total = Application.Run("DBRW", "domforecast2:OracleOpexReporting", Range("F5").Value, Range("F6").Value, Range("F9").Value, Range("F10").Value, Range("F7").Value, TM1Element, Range("F11").Value)
'process only level 0 elements and sales <> 0 otherwise skip it
If ((Application.Run("ellev", fullDim, TM1Element) = 0) And (total <> 0)) Then

'update the dimension
Sheets("Opex Report").Range("$E$5").Value = "=SUBNM(""" & fullDim & """, """", """ & TM1Element & """, ""Name"")"
Sheets("Opex Report").Range("$B$8").Value = "=SUBNM(""" & fullDim & """, """", """ & TM1Element & """, ""Description"")"
'refresh worksheet
Application.Run ("TM1RECALC")

Sheets("Opex Month View").Select
Application.Run ("TM1RECALC")

With Application
.ScreenUpdating = False

' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo ErrCatcher
'Sheets(Array("Sheet1", "CopyMe2")).Copy
Sheets(Array("Opex Report", "Opex Month View")).Select
Sheets("Opex Month View").Activate
Sheets(Array("Opex Report", "Opex Month View")).Copy
Sheets("Opex Report").Activate
On Error GoTo 0

' Paste sheets as values
' Remove External Links, Hperlinks and hard-code formulas
' Make sure A1 is selected on all sheets

ThatWorkbook = ActiveWorkbook.Name

Sheets("Opex Report").Select
Cells.Select
Application.CutCopyMode = False
With Selection
.MergeCells = False
End With

Selection.AutoFilter Field:=1

Cells.Copy
Range("A1").PasteSpecial Paste:=xlValues
Cells.Hyperlinks.Delete
Application.CutCopyMode = False

Selection.AutoFilter Field:=1, Criteria1:="1"

Cells(1, 1).Select

Sheets("Opex Month View").Select
Cells.Select
Application.CutCopyMode = False
With Selection
.MergeCells = False
End With


Cells.Copy
Range("A1").PasteSpecial Paste:=xlValues
Cells.Hyperlinks.Delete
Application.CutCopyMode = False

Cells(1, 1).Select

Sheets("Opex Report").Select
Range("A1").Select

' 'Remove named ranges except print settings
' For Each nm In ActiveWorkbook.Names
' If nm.NameLocal <> "Sheet1!Print_Area" And nm.NameLocal <> "Sheet1!Print_Titles" Then
' nm.Delete
' End If
' Next nm

'name report after the branch name
' NewName = Left(Range("$B$9").Value, 4)

'Save it in the branch folder of the same name
'folder = Dir(destination & NewName & "*", vbDirectory)

ActiveWorkbook.SaveCopyAs destination & Period & "_" & Year & "_Dept" & TM1Element & ".xls"
'skip save file confirmation
ActiveWorkbook.Saved = True
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With

End If
Next i

Exit Sub

ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub

[Martin Ryan]

Not sure which of that code is mine, but nice to be credited!

There is no way in VBA to get a list of the subsets that a dimension has. You could create another dimension that contains the list of subsets and cycle through that.

However what I'd suggest you do instead is create consolidations in the dimension instead of using subsets. Then create an attribute called "Report" that has a 1 or 0 against every N level or C level element that you want to report. Then you can do something like this...

Code: Select all

For i = 1 To Run("dimsiz", fullDim)
TM1Element = Run("dimnm", fullDim, i)
if run ("attrn", fullDim, tm1Element, "Report")=1 then
' Do your reporting
end if
next i

HTH,
Martin

[Alan Kirk]

Not sure which of that code is mine, but nice to be credited!

There is no way in VBA to get a list of the subsets that a dimension has.

For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"... but I like your idea way better.

[LoadzaGrunt]

For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"...

You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...

YMMV

[Alan Kirk]

For the purposes of completeness only... ITYM "There is no way in VBA to get a list of the subsets that a dimension has unless you resort to calling the TM1 API with it"...

You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...

YMMV

The mileage will usually vary depending on whether it's an admin or an end user who's doing the looking. I try to shy away from solutions that need to look at the data directory these days since it's impossible to deploy them to end users. (Unless of course you get TI to do the listing and outputting the list somewhere, but that might be over-complicating it.) And that even assumes that the Admin has access to the data directory, which some highly paranoid IT departments won't allow.

But you're correct, it is another source of the information.

[l_stoker]

Appreciate your guidance guys

[Wim Gielis]

A bit off-topic, but the code in your VBA procedure could be clearly improved. Some of the issues:

- Dim ThisWorkbook, ThatWorkbook, OpexCurrency, Comparison, Company, OpexMeasure, Period As String
ONLY Period is now declared as string, the other variables are Variants. You need to repeat "As String" for the other variables

- ThisWorkbook is part of VBE, do not use it in your code. Same for Year, which is a function. You should use other variable names.

- You use:
server = "domforecast2"

but below in the code I still see instances of "domforecast2"

- instead of Range("F5"), Range("F6"), Range("F7"), ..., Range("F11"): why not use Named Ranges in Excel? If you insert a row in the sheet above or in that area, you need to change the VBA code

- Selecting and Activating sheets and ranges is not needed in the VAST majority of statements.

-

Code: Select all

                Sheets("Opex Report").Select
                Cells.Select
                Application.CutCopyMode = False
                With Selection
                    .MergeCells = False
                End With

Obviously, merged cells should NEVER be used, but if you do, code it as:

-

Code: Select all

                Sheets("Opex Report").UsedRange.MergeCells = False

- Using With ... End With is advised, but use it in a better way then you do it here.

Wim