report burst (vba)

[yyi]

If anyone was interested in trying a vba method, thought i'd share this code snippet

TM1 Process/Action Button

Code: Select all

vCmd = '"C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe" "E:\Data\TM1\test\P & L Report.xls';
ExecuteCommand(vCmd, 0);

Excel Workbook

Code: Select all

Private Const srvName As String = "tm1serv"
Private Const savPath As String = "E:\Data\TM1\test"
Private S1 As Excel.Worksheet, s2 As Excel.Worksheet
Private ret As Variant

Sub burst()
On Local Error GoTo ERR_burst
Dim ws As Excel.Worksheet
n = Run("SUBSIZ", srvName & ":SAP Dept", "Test")
Set S1 = Sheet1                 'cookie cutter
For i = 2 To n    '1 is the 1st one
 'Debug.Print i, Run("SUBNM", srvName & ":SAP Dept", "Test", i, "Code+Desc")
 sCopy Run("SUBNM", srvName & ":SAP Dept", "Test", i, "Code+Desc")
Next

'Exit Sub

emailMessage "P & L Reports", "Process Complete"
'savPath && always overwrite
Application.DisplayAlerts = False
Me.SaveAs Filename:= _
    Me.Path & "\" & Left(Me.Name, Len(Me.Name) - 4) & "_Values.xls", FileFormat:=xlNormal _
    , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
    
Exit Sub
ERR_burst:
    Debug.Print Err.Description
    emailMessage "P & L Reports - Error", Err.Description
End Sub

Sub sCopy(elName As String)
  S1.Copy , S1
  Set s2 = ActiveSheet
  s2.Range("vDept").Value = elName
  s2.Name = Left(elName, 11)
  Set S1 = s2    'duplicate fwd in series
End Sub

Sub TI_Connect()
ret = Run("N_CONNECT", srvName, "admin", "apple")
Debug.Print ret
End Sub

Sub TI_Disconnect()
ret = Run("N_DISCONNECT")
Debug.Print ret
End Sub

Private Sub Workbook_Open()
On Local Error GoTo ERR_Open
If Application.Calculation <> xlCalculationManual Then Application.Calculation = xlCalculationManual
If Application.CalculateBeforeSave <> False Then Application.CalculateBeforeSave = False
If InStr(1, Me.Name, "_Value", vbTextCompare) <> 0 Then Exit Sub
If openTextFile = 0 Then Exit Sub
TI_Connect
burst
TI_Disconnect
'Me.Close

With Application
  .Wait Now + TimeSerial(0, 0, 3)
  .DisplayAlerts = False
  .Quit
End With

Exit Sub
ERR_Open:
Debug.Print Err.Number, Err.Description
emailMessage "P & L Reports - Error", Err.Description
Application.DisplayAlerts = False
Application.Quit
End Sub

Function openTextFile() As Byte
    Dim S1 As String
    Close #1                                            ' Close file.
    Open Me.Path & "\reportConfig.txt" For Input As #1  ' Open file for input.
    Input #1, S1                                      ' Read data into variable.
    Close #1                                            ' Close file.
    openTextFile = Val(S1)
End Function

'this will only work on the server, 9476 = Ok
Sub emailMessage(subject As String, body As String, Optional recipient As String = "tm1admin@myco.com.au")
    Dim vCmd As String
    vCmd = "cmd /c D:\sendmail\sendmail.exe -ini -to " & recipient & " -sub """ & subject & """ -body """ & body & ""
    ret = Shell(vCmd, vbHide)
    Debug.Print ret
End Sub

btw, i haven't changed my avatar Alan, but tell Gavin he takes a great shot!

[Plunge pool]

Hi there

I am noobie in TM1 and a little familiar with report bursting, what does this actually do?

Does it send a snapshot of a view to an email"?

[yyi]

it doesn't exist for an uploaded excel report template to the applications folder. an enhancement request was put through to ibm: COGCQ00802666.

this code is just a shell type command to "report burst" mabye a hundred reports from an excel template sitting on the tm1 server. ie: launched with a chore/process/action button.