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
VBA - Bulk Reporting; including subsets
- Martin Ryan
- Site Admin
- Posts: 1989
- Joined: Sat May 10, 2008 9:08 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Wellington, New Zealand
- Contact:
Re: VBA - Bulk Reporting; including subsets
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...
HTH,
Martin
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
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Jodi Ryan Family Lawyer
- Alan Kirk
- Site Admin
- Posts: 6622
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: VBA - Bulk Reporting; including subsets
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.Martin Ryan wrote: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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
- LoadzaGrunt
- Posts: 72
- Joined: Tue May 26, 2009 2:23 am
- Version: LoadzaVersions
- Excel Version: LoadzaVersions
Re: VBA - Bulk Reporting; including subsets
You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...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"...
YMMV
- Alan Kirk
- Site Admin
- Posts: 6622
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: VBA - Bulk Reporting; including subsets
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.LoadzaGrunt wrote:You could potentially look at the content of the DIMENSION_NAME}subs folder in the data folder...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"...
YMMV
But you're correct, it is another source of the information.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 8
- Joined: Thu Oct 14, 2010 3:22 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
Re: VBA - Bulk Reporting; including subsets
Appreciate your guidance guys
-
- MVP
- Posts: 3182
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: VBA - Bulk Reporting; including subsets
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.
-
Obviously, merged cells should NEVER be used, but if you do, code it as:
-
- Using With ... End With is advised, but use it in a better way then you do it here.
Wim
- 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
-
Code: Select all
Sheets("Opex Report").UsedRange.MergeCells = False
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly