Run TI from an Excel Macro (TM1 9.5.1/Excel 2007)

[Pim van Putten]

Dear Forum,

Is there a simple way to run a TI process from a macro using TM1 9.5.1 and Excel 2007?

There seems to be some TM1 API code on this forum for older versions of TM1 and Excel but this refuses to work somehow.

Thanx in advance,

Pim

[Martin Ryan]

This works in 2010 so should in 2007. It's from an upcoming release of the TM1 tools add in

Code: Select all

' v 1.0 23 August 2010
' Written by Martin Ryan based on code from Mike Cowie in the TM1 Forum
' http://forums.olapforums.com/viewtopic.php?f=3&p=4468#p4468

Declare Function TM1_API2HAN Lib "tm1.xll" () As Long

' #### SAMPLE CALL ####
Sub sampleTICall()
    Dim tm1ServerName As String, procName As String
    Dim p1, p2, p3, p4, p5, p6, p7 As String
    
    '--- Update this section as required
    tm1ServerName = "tm1_library" ' the name of the TM1 server
    procName = "DataExport_AnyDimension" ' the name of the process being called
    p1 = "ID" ' The first parameter
    p2 = "No" ' The second parameter
    '--- end updates section
    
    MsgBox RunTIProcess(tm1ServerName, procName, p1, p2)
End Sub
' #### END SAMPLE CALL ####

'HELPER FUNCTION TO GET TM1 API SESSION HANDLE
'  This allows you to leverage TM1 Excel Client's active connection to your TM1 Server
Public Function GetExcelSessionHandle() As Long
    GetExcelSessionHandle = Application.Run("TM1_API2HAN")
End Function

'EXECUTE PROCESS FUNCTION
Public Function RunTIProcess(ByVal ServerName As String, ByVal ProcessName As String, ParamArray ProcessParameters()) As String
  Dim SessionHandle As Long, ValPoolHandle As Long
  Dim ServerHandle As Long, ProcessHandle As Long
  Dim TotParams As Long, i As Long, RawParameterArray() As Variant, ArrOffset As Byte
  Dim InitParamArray() As Long, ParamArrayHandle As Long
  Dim ExecuteResult As Long, numResults As Integer, k As Integer, myMsg As String
  Dim iType As Integer, errStr As String * 75, tempVal As Long, returnStr As String * 75
  
  'Get handles
  SessionHandle = GetExcelSessionHandle()
  If SessionHandle = 0 Then
    RunTIProcess = "ERROR: Cannot communicate with TM1 API."
    Exit Function
  End If
  
  ValPoolHandle = TM1ValPoolCreate(SessionHandle)
  On Error GoTo Cleanup_API
    
  'Get handle to server; check if connected
  ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
  If ServerHandle = 0 Then
    RunTIProcess = "ERROR: Not connected to server " & ServerName & "."
    GoTo Cleanup_API
  End If
  
  'Get handle to process; check if valid
  ProcessHandle = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle, TM1ServerProcesses, TM1ValString(ValPoolHandle, ProcessName, 0))
  If TM1ValType(SessionHandle, ProcessHandle) <> TM1ValTypeObject Then
    RunTIProcess = "ERROR: Unable to access process " & ProcessName & "."
    GoTo Cleanup_API
  End If
  
  If TM1ValObjectCanRead(SessionHandle, ProcessHandle) = 0 Then
     RunTIProcess = "ERROR: No permissions to execute process " & ProcessName & "."
    GoTo Cleanup_API
  End If
  
  ' Get parameters, build array of param & type
  TotParams = UBound(ProcessParameters)
  If TotParams < 0 Then 'No parameters passed
    ReDim InitParamArray(1)
    InitParamArray(1) = TM1ValString(ValPoolHandle, "", 1)
    ParamArrayHandle = TM1ValArray(ValPoolHandle, InitParamArray, 0)
  Else
    ReDim RawParameterArray(TotParams, 1)
    
    'Collect the params, get their type
    For i = 0 To TotParams
      RawParameterArray(i, 0) = ProcessParameters(i)
      If VarType(ProcessParameters(i)) = vbString Then
        RawParameterArray(i, 1) = "S"
      Else
        RawParameterArray(i, 1) = "N"
      End If
    Next i
    
    'Now array of handles to params for process
    ReDim InitParamArray(TotParams)
    For i = 0 To TotParams
      If RawParameterArray(i, 1) = "S" Then
        InitParamArray(i) = TM1ValString(ValPoolHandle, CStr(RawParameterArray(i, 0)), 0)
      Else
        InitParamArray(i) = TM1ValReal(ValPoolHandle, CDbl(RawParameterArray(i, 0)))
      End If
    Next i
        
    'Set the parameters in TM1 array
    ParamArrayHandle = TM1ValArray(ValPoolHandle, InitParamArray, TotParams + 1)
    For i = 0 To TotParams
      TM1ValArraySet ParamArrayHandle, InitParamArray(i), i + 1
    Next i
  End If
  
  'Execute process
  ExecuteResult = TM1ProcessExecuteEx(ValPoolHandle, ProcessHandle, ParamArrayHandle)
  'ExecuteResult is an array containing information about the error type (minor, ProcessQuit, etc) and a reference to the error log location
  iType = TM1ValType(SessionHandle, ExecuteResult)

    If iType = CInt(TM1ValTypeIndex) Then
        RunTIProcess = "Process executed successfully"
    ElseIf iType = CInt(TM1ValTypeArray) Then
        'Retrieve the error
        TM1ValErrorString_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(1)), errStr, 75
        'Retrieve the error log file
        TM1ValStringGet_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(2)), returnStr, 75
        showProcessError errStr, returnStr
        RunTIProcess = "Errors occurred during process execution"
    ElseIf iType = CInt(TM1ValTypeError) Then
        RunTIProcess = "Returned an error value.  The process was not run for some reason"
    ElseIf iType = CInt(TM1ValTypeBool) Then
        RunTIProcess = "Returned a boolean value.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeObject) Then
        RunTIProcess = "Returned an object.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeReal) Then
        RunTIProcess = "Returned a real number.  This should not happen."
    ElseIf iType = CInt(TM1ValTypeString) Then
        RunTIProcess = "Returned a string.  This should not happen."
    Else
        RunTIProcess = "Unknown return value: " & iType
    End If
  
Cleanup_API:
  TM1ValPoolDestroy ValPoolHandle
End Function

Sub showProcessError(reason As String, fileName As String)
Dim myMsg As String, filePath As String, logPath As String, fso

On Error GoTo errorHandler

Set fso = CreateObject("Scripting.FileSystemObject")
' Retrieve the log path that the user set in the options dialog box.
logPath = GetSetting(gSC_APP_NAME, gSC_REG_SECT_OPTS, gSC_REGKEY_LOGDIR, "") & "\"
filePath = logPath & fileName

' Check they've specified a log path
If logPath = "\" Then
    myMsg = "There was an error when running your process.  Specify a logging directory " & _
    "in the TM1 Tools options dialog box to be able to view the error log from this workbook.  The error message was " & reason
    MsgBox myMsg, vbOKOnly, "An error occurred"
' Check they've specified a valid log path
ElseIf Not fso.FolderExists(logPath) Then
    myMsg = "There was an error when running your process, but I can't find the error directory.  Check you have specified it " & _
    "correctly in the TM1 Tools options dialog box and that you have access privileges.  The error message was " & reason
    MsgBox myMsg, vbOKOnly, "An error occurred"
 ' If we can find the error file then give them the option of opening it
ElseIf fso.FileExists(filePath) Then
    myMsg = "There was an error when running your process.  Do you wish to view the error log?  The error message was " & reason
    If MsgBox(myMsg, vbYesNo, "View error log?") = vbYes Then
        If Dir(filePath) = "" Then
            MsgBox "I was unable to open the file.  The path is " & filePath
        Else
            Workbooks.Open filePath
        End If
    End If
Else
    ' Incorrect parameters (either how many of them, or incorrect numeric/string type) sticks a '$' on the end of the error
    ' log and is locked by TM1 so we can't open it.  There may be other reasons too.
    myMsg = "An error occurred but I cannot find the error log.  This may something is wrong with " & _
     "the parameters, or that the error log folder you have specified is incorrect. " & _
     Chr(13) & Chr(13) & "The error message was: " & reason
     MsgBox myMsg, vbOKOnly, "An error occurred"
End If
Exit Sub 'avoid error handler
errorHandler:
    MsgBox "An unknown error occured in the showProcessError sub of the bas_TurboIntegrator module in the TM1_Tools addin"
End Sub

[Wim Gielis]

Nice code!

Just a note. If you use:

Dim p1, p2, p3, p4, p5, p6, p7 As String

ONLY p7 will be a String, the other will be Variant. You must repeat the "As String" for each variable.

Wim