VBA Function p_ExecuteTM1process

[Jan]

Hello,

I've written a VB function that executes a TM1 process with a given name 'tm1process' , specified parameters 'parameters' (is an array), loginname 'login', password 'password'.
The function also expects that the following constant variables to be defined:

Code: Select all

Public Const c_ServerName = "INSTANCENAME"
Public Const c_AdminHost = "SERVERNAME"      

Example:
p_ExecuteTM1process 'Test process',Array('parameter1','parameter2'),'Admin','Password')

Code: Select all

Public Function p_ExecuteTM1process(ByVal tm1process As String, ByVal parameters As Variant, ByVal login As String, ByVal password As String) As Boolean
'---------------------------------------------------------------------------------------------
' Auteur:
'           Jan
' Parameters:
'           tm1process  -> Name of TM1 process
'           parameters  -> Array containing the parameters for the TM1process
'           login       -> TM1 loginname
'           password    -> TM1 password
' Function:
'           Executes TM1 process 'tm1process'
'           REMARK:
'               Expects the following constants to be declared and initialized:
'                   Const c_ServerName   'Name TM1 server
'                   Const c_AdminHost    'Hostname of TM1 server
' Return:
'           TRUE     -> Function succeeded
'           FALSE    -> Function failed
'---------------------------------------------------------------------------------------------
On Error GoTo AfterError:
    Dim nServerName As Long
    Dim hServerName As Long
    Dim nClientID As Long
    Dim nPassword As Long
    Dim Retlong As Long
    Dim voprocess As Long
    Dim hParametersArray As Long
    Dim iparamarray() As Long
    Dim nNrOfParams As Long
    Dim sprocessname As String
    Dim RetType As Long
    Dim ErrorCode As Long
    Dim nI As Integer
    Dim sErrMsg As String * 100
    
    'Connection
    TM1APIInitialize
    hUser = TM1SystemOpen()
    If hUser = 0 Then
        MsgBox ("Error opening the system")
        GoTo ExitFunction
    End If
    
    'Create pool
    pGeneral = TM1ValPoolCreate(hUser)
    If pGeneral = 0 Then
        MsgBox ("Error creating memory pool")
        GoTo ExitFunction
    End If
    
    'Admin host
    Call TM1SystemAdminHostSet(hUser, c_AdminHost)
    Retlong = TM1SystemServerNof(hUser)
    
    ' Locate server and connect
    nServerName = TM1ValString(pGeneral, c_ServerName, 75)
    nClientID = TM1ValString(pGeneral, login, 75)
    nPassword = TM1ValString(pGeneral, password, 75)
    Call TM1SystemAdminHostSet(hUser, c_AdminHost)
    hServerName = TM1SystemServerConnect(pGeneral, nServerName, nClientID, nPassword)
    
    sprocessname = tm1process
    
    nNrOfParams = UBound(parameters) + 1
    ReDim iparamarray(nNrOfParams - 1)
    
    'For each parameter
    For nI = 1 To nNrOfParams
        iparamarray(nI - 1) = TM1ValString(pGeneral, parameters(nI - 1), Len(parameters(nI - 1)))
    Next nI
    
    hParametersArray = TM1ValArray(pGeneral, iparamarray, nNrOfParams)
    
    'For each parameter
    For nI = 1 To nNrOfParams
        TM1ValArraySet hParametersArray, iparamarray(nI - 1), nI
    Next nI
    
    voprocess = TM1ObjectListHandleByNameGet(pGeneral, hServerName, TM1ServerProcesses(), TM1ValString(pGeneral, sprocessname, 100))
    
    Retlong = TM1ProcessExecute(pGeneral, voprocess, hParametersArray)
    RetType = TM1ValType(hUser, Retlong)
    
    If RetType = TM1ValTypeError Then 'TM1ValTypeObject Then
    'Return value is an error object
        TM1ValErrorString_VB hUser, Retlong, sErrMsg, 100
        Err.Raise vbObjectError + 514, , sErrMsg
    Else
    'Return value is a boolean value
        Retlong = TM1ValBoolGet(hUser, Retlong)
        If Retlong = 0 Then
            Err.Raise vbObjectError + 515, , "Process generated errors" & vbCrLf & "Check log file."
        Else
            'Process executed successfully
        End If
    End If

    p_ExecuteTM1process = True
ExitFunction:
    'DECONNECTION
    If pGeneral > 0 And hServerName > 0 Then
        Retlong = TM1SystemServerDisconnect(pGeneral, hServerName)
    End If
    
    'Destruction of pool
    If pGeneral > 0 Then
        TM1ValPoolDestroy (pGeneral)
    End If
    
    If hUser > 0 Then
        Call TM1SystemClose(hUser)
    End If
    Exit Function

AfterError:
    MsgBox "p_ExecuteTM1process: There was an error while executing process '" & tm1process & "':" & vbCr & vbCr & Err.Description, vbCritical, "Oops"
    Resume ExitFunction
End Function

This works perfectly, even the errorhandling is complete but I want to expand this function with the API function 'TM1ProcessExecuteEx' instead, and when the process generates an logfile, I want the location of the logfile as result.
Can anybody help me with this? I know it is possible but I can't seem to retrieve the location of the logfile

Thanx in advance,

Jan

[chiefouko]

Hi Guys am trying to reuse your code basically to run a TM1 TI process and i keep getting a "Compile Error - Sub or Function not defined". This error breaks when calling "TM1ValArray" function. Can you please advise how you declared this function?

Code fails here

hParametersArray = TM1ValArray(pGeneral, iparamarray, nNrOfParams)


Is it part of TM1 API or its built by the developer?


Thanks in advance.

[Alan Kirk]

Hi Guys am trying to reuse your code basically to run a TM1 TI process and i keep getting a "Compile Error - Sub or Function not defined". This error breaks when calling "TM1ValArray" function. Can you please advise how you declared this function?

Code fails here

hParametersArray = TM1ValArray(pGeneral, iparamarray, nNrOfParams)

Is it part of TM1 API or its built by the developer?

If you need to ask that, you probably shouldn't be using the API to do this.

TM1ValArray is a function that needs to be declared from the tm1api.dll library. All of those declarations are in a file called tm1api.bas, which you would need to incorporate into your project. The file will be found somewhere in the API folder of your TM1 client installation folder.

A far easier alternative is to get a copy of TM1 Tools which allows you to simply call a standard function called RunTIProcess. You don't need to know anything about the API to use it; as long as you know how to call a function, you can pass the name of the process and any specific parameters and it'll run the thing.

Alternatively if this is to be triggered by a user, you should probably take a look at whether Action Buttons would do what you need.

As I keep reiterating... the API should always be your absolute last resort. It's not a friendly place to live.

[chiefouko]

Sweeet!!! - The TM1 tool is working perfect!! Thanks