Useful VBA Code to Execute Against REST API / Run TI Script
Posted: Wed Jan 10, 2024 2:20 am
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
Run Process (Turbo Integrator Script)
Example Usage
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
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
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