Useful VBA Code to Execute Against REST API / Run TI Script

[WilliamSmith]

Hoping some may find this helpful. I still use VBA for a lot of legacy processes, so having a direct API connection to TM1 has been helpful, especially for running Turbo Integrator processes, I've been able to essentially bypass the need for connecting to TM1 via PAfE and using the proprietary TI launcher buttons.

VBA References
Microsoft Scripting Runtime
Microsoft XML, v6.0

External Dependencies
https://github.com/VBA-tools/VBA-JSON

HTTP Post

Code: Select all

Function TM1_Post(endPoint As String, payload As String)

    Dim x As MSXML2.ServerXMLHTTP60
    Set x = New MSXML2.ServerXMLHTTP60
    
    x.Open "POST", "https://Your_Server:Your_Port" & endPoint, False, "Basic_Auth_Username", "Basic_Auth_Password"
    'Additional http info is needed for other auth methods, consult IBM TM1 REST API documentation
    
    x.setRequestHeader "WWW-Authenticate: Basic Realm", "TM1"
    x.setRequestHeader "Content-Type", "application/json"
    x.setRequestHeader "Accept", "application/json"
    x.setRequestHeader "Prefer", "wait"
    x.SetOption 2, 13056 'Bypass HTTPS cert authentication (internal network)
    
    x.Send payload
    
    TM1_Post = x.responseText

End Function

Run Process (Turbo Integrator Script)

Code: Select all

Function TM1_Run_Process(processName As String, Optional paramDictionary As Dictionary) As String

    Dim payload As String
    payload = ""

    Dim runStatus As String
    runStatus = ""

On Error GoTo ErrHandler

    If Not paramDictionary Is Nothing Then

        payload = "{""Parameters"":["
        
        For Each Key In paramDictionary.Keys
        
            payload = payload & "{""Name"":""" & Key & """, ""Value"":""" & paramDictionary(Key) & """},"
        
        Next
        
        payload = payload & "]}"
        
    End If

    Dim responseText As String
    responseText = TM1_Post("/api/v1/Processes('" & processName & "')/tm1.ExecuteWithReturn", payload)
        
    Dim json As Object
    Set json = JsonConverter.ParseJson(responseText)

    runStatus = json("ProcessExecuteStatusCode")

ErrHandler:

    If Err.Number <> 0 Then
    
        runStatus = Err.Description
    
    End If

    If runStatus <> "CompletedSuccessfully" Then
    
        MsgBox "Turbo Integrator script error!: " & responseText
    
    End If
    
    TM1_Run_Process = runStatus

End Function

Example Usage

Code: Select all

Sub Run_Your_Turbo_Integrator_Script()

    Dim parameters As New Dictionary

    parameters.Add "Your_Parameter_1", ThisWorkbook.Names("parameter1").RefersToRange.Value
    parameters.Add "Your_Parameter_2", ThisWorkbook.Names("parameter2").RefersToRange.Value
    parameters.Add "Your_Parameter_3", ThisWorkbook.Names("parameter3").RefersToRange.Value
    parameters.Add "Your_Parameter_4", ThisWorkbook.Names("parameter4").RefersToRange.Value
    'parameters.Add ...

    Debug.Print TM1_Run_Process("Your_Turbo_Integrator_Process_Name", parameters)

    parameters.RemoveAll

End Sub