TM1 Forum

HI All,

I have the following snippet of code that I found on this board, however it is not running the process, I am getting a successful connect though.

Could anybody shed some light as to what I'm doing wrong?

Option Explicit
Global arr()
Declare Function TM1_API2HAN Lib "tm1.xll" () As Long

Sub tm1EP()

Dim hUser As Long
Dim hPool As Long
Dim hServer As Long
Dim vServerName As Long
Dim vClientName As Long
Dim vClientPassword As Long
Dim ServerName As String * 75
Dim ClientName As String * 75
Dim ClientPassword As String * 75
Dim ErrorString As String * 75
Dim ServerHandle As Long, ProcessHandle As Long
Dim TotParams As Long
Dim InitParamArray() As Long, ParamArrayHandle As Long
Dim ExecuteResult As Long
Dim SessionHandle As Long
Dim ProcessParameters()
Dim i
' initialize the API
tm1api.TM1APIInitialize
hUser = TM1SystemOpen()
'Set the Admin Host Server Name
TM1SystemAdminHostSet hUser, "tm1qa.test"
' Create a Pool Handle
hPool = TM1ValPoolCreate(hUser)
' Establish Login information
ServerName = "tm1qa"
ClientName = "admin"
ClientPassword = "test"
vServerName = TM1ValString(hPool, Trim(ServerName), 0)
vClientName = TM1ValString(hPool, Trim(ClientName), 0)
vClientPassword = TM1ValString(hPool, Trim(ClientPassword), 0)
'Log in to a TM1 Server
hServer = TM1SystemServerConnect(hPool, vServerName, vClientName, vClientPassword)
' Check to see if we were successful...
If (TM1ValType(hUser, hServer) = TM1ValTypeObject()) Then MsgBox "You Logged in Successfully"
If (TM1ValType(hUser, hServer) = TM1ValTypeError()) Then MsgBox "The server handle contains an error code."
'TM1ValErrorString_VB hUser, hServer, ErrorString, 0

SessionHandle = GetExcelSessionHandle()
If SessionHandle = 0 Then
MsgBox "ERROR: Cannot communicate with TM1 API."
Exit Sub
End If

ProcessHandle = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerProcesses, TM1ValString(hPool, "AutolockPeriod", 0))

' Get parameters, build array of param & type
TotParams = -1 'UBound(ProcessParameters)
If TotParams < 0 Then 'No parameters passed
ReDim InitParamArray(1)
InitParamArray(1) = TM1ValString(hPool, "", 1)
ParamArrayHandle = TM1ValArray(hPool, 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(hPool, CStr(RawParameterArray(i, 0)), 0)
Else
InitParamArray(i) = TM1ValReal(hPool, CDbl(RawParameterArray(i, 0)))
End If
Next i

'Set the parameters in TM1 array
ParamArrayHandle = TM1ValArray(hPool, InitParamArray, TotParams + 1)
For i = 0 To TotParams
TM1ValArraySet ParamArrayHandle, InitParamArray(i), i + 1
Next i
End If

'Execute process
ExecuteResult = TM1ProcessExecuteEx(hPool, ProcessHandle, ParamArrayHandle)

tm1api.TM1APIFinalize

Cleanup_API:
TM1ValPoolDestroy hPool

End Sub

Hi Swain,

Do you mind if you can add this validation in?
Personally, i prefer double-checking every object i get, just to make sure it is the right one if not i will terminate the code there and then. Helps me to know where and what went wrong.
Just a simple check if you don't mind...

Validation after this code:
ProcessHandle = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerProcesses, TM1ValString(hPool, "AutolockPeriod", 0))
If TM1ValType(hUser, ProcessHandle) = TM1ValTypeError() Then 'Exit Sub and Finalize API or Prompt for string value again.

Personal Opinion:
After you have got your session handle, you retrieve process handle by name of string value "AutolockPeriod".
I would prefer using a combo box and float them with processes names and let the user select them by index.
In that case i can then retrieve process handle by index - "TM1ObjectListHandleByIndexGet";

Hope to hear from you!
-Brandon Chua

Hi Brandon,

Thanks for your reply !

The code fails on the session handle as you suspected because the user is not logged into TM1 through Excel. Does a user really need to log in to run a VBA process since the 'admin' is already successfully logged in? ('....MsgBox "You Logged in Successfully"')

Hi Swain,

Nope, the user doesn't need to login through Excel.
TM1 API exists so that users can live outside Excel TM1 session, talking to TM1 directly.

The author of code you initially posted has wrote an unneccessary step of connecting to an existing Excel TM1 session.

You can essentially remove that and start the work on getting TM1Object.

Yay !

Thanks Brandon

I used the hUser as the session handle and it works.