TM1 Forum

Posted: **Mon Oct 21, 2013 10:06 am**

Hi there, I am new to TM1 (4 Months) and I just wanted to find out if anybody can help me
with the development of VBA code in an excel macro to mimmick the print report function on
perspectives? This code also has to automatically group Parent elements with their children
in each of the generated workbooks.

Any kind of help would be much appreciated, thanks.

Posted: **Mon Oct 21, 2013 10:29 pm**

Mathula Jr wrote:Hi there, I am new to TM1 (4 Months) and I just wanted to find out if anybody can help me
with the development of VBA code in an excel macro to mimmick the print report function on
perspectives? This code also has to automatically group Parent elements with their children
in each of the generated workbooks.

Any kind of help would be much appreciated, thanks.

I suggest that you start with the Reference Guide, specifically the section on TM1 Macro Functions.

You would most likely start with a master Active Form, modify that as needed at runtime, recalculate and use standard VBA code to copy the sheet as needed.

If you run into any specific problems you can always post for help.

If you want an end to end solution, though, and lack either the time or the skills to build it yourself, there are a lot of good consultants in the market who should be able to create something for you.

Posted: **Tue Oct 22, 2013 8:22 am**

I've been going through both the TM1 Macro Functions and Chapter 5 Worksheet Functions sections
and they were pretty insightful, I just wasn't sure of whether or not I should use active forms.

Hopefully this is the way to go, thanks so much for the help Alan.

Posted: **Sun Oct 27, 2013 8:44 pm**

Hi Alan, since we last spoke I managed to recycle some code I found on the forum. I've modified this code to loop through certain subsets of a particular dimension (Cost Centre). It then creates reports based on one original high level sheet (Budget) in the form of single sheet workbooks for each subset element it iterates through.

Do you know any way that I can get this code to create a single workbook for every subset processed with multiple sheets representing all of the elements inside it, rather than a million single sheet workbooks for every subset element?...I've attached the code below, thanks.

Code: Select all


Option Explicit

Sub Merge()

    Dim NewName As String
    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 folder As String
    Dim destination As String
    Dim Temp_File_Name As String
    Dim File_Name As String
   
    Temp_File_Name = "C:\Program Files\ibm\cognos\tm1_64\samples\tm1\gti_dev\dev\Cost Centre}subs\" & "Shadow*.sub"
    File_Name = Dir(Temp_File_Name, vbNormal)
    destination = "C:\Users\SK\Documents\Totem\TM1\Projects\Standard Bank\Cost Centre Reports"
    server = "gti_dev"
    myDim = "Cost Centre"
    fullDim = server & ":" & myDim
         
If Run("dimix", server & ":}Dimensions", myDim) = 0 Then
        MsgBox "The dimension does not exist on this server"
        Exit Sub
    End If
    
    
    While File_Name <> ""
         
        MsgBox File_Name
        File_Name = Dir
        
           'loop over all elements of the Cost Centre dimension subset
           For i = 1 To Run("subsiz", fullDim, File_Name)
    
         TM1Element = Run("SUBNM", fullDim, File_Name, i, "Name")
      
      Range("$O$1").Value = "=SUBNM(""" & fullDim & """, "" & File_Name & "", """ & TM1Element & """, ""Name"")"
        
        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
                  
                    'Sheets(Array("Budget", "CopyMe2")).Copy
                    Sheets(Array("Budget")).Copy
                    On Error GoTo 0
                     
                     '       Paste sheets as values
                     '       Remove External Links, Hperlinks and hard-code formulas
                     '       Make sure A1 is selected on all sheets
                    For Each ws In ActiveWorkbook.Worksheets
                        ws.Cells.Copy
                        ws.[A1].PasteSpecial Paste:=xlValues
                        ws.Cells.Hyperlinks.Delete
                        Application.CutCopyMode = False
                        Cells(1, 1).Select
                        ws.Activate
                    Next ws
                    Cells(1, 1).Select
                     
                     'name report after the cost centre name
                    NewName = Right(Range("$O$1").Value, 30)
                     
                     
                     'Save it in the cost centre folder of the same name
                      folder = Dir(destination & NewName & "*", vbDirectory)
   
                    ActiveWorkbook.SaveCopyAs destination & folder & "\" & NewName & "_report.xlsx"
                    'skip save file confirmation
                    ActiveWorkbook.Saved = True
                    ActiveWorkbook.Close SaveChanges:=False
                    .ScreenUpdating = True
                End With
    Next i
    
 Wend

End Sub

TM1 Forum

VBA Code to Mimmick the Print Report Function

VBA Code to Mimmick the Print Report Function

Re: VBA Code to Mimmick the Print Report Function

Re: VBA Code to Mimmick the Print Report Function

Re: VBA Code to Mimmick the Print Report Function