Execute Process from VBA

[Rashed]

Hi Guys,
I am trying to execute a process from VBA using an command button. But, keep getting an error type of 6. Have pasted the code below. Any ideas where I am going wrong? Thanks in advance.

Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long
Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Function TM1ProcessExecuteEx Lib "tm1api.dll" (ByVal hPool As Long, ByVal hProcess As Long, ByVal hParametersArray As Long) As Long
Declare Function TM1ObjectListHandleByNameGet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal iPropertyList As Long, ByVal sName As Long) As Long
'Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Function TM1ValType Lib "tm1api.dll" (ByVal hUser As Long, ByVal Value As Long) As Integer

Sub Button1_Click()
Dim x As Long
Dim hPool As Long
Dim hProcess As Long
Dim hServer As Long
Dim hParametersArray As Long
Dim vClientName As String
Dim hUser As Long
Dim iResult As Long
Dim iType As Long

On Error GoTo err_para:

vClientName = "tm1serv"
hPool = TM1ValPoolCreate(TM1SystemOpen())
hUser = TM1SystemOpen()
hServer = TM1ValString(hPool, Trim(vClientName), 0) 'TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerClients, vClientName)

hProcess = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerProcesses, _
TM1ValString(hPool, "Cube_AGR_GL_Populate_Facts_From_Drivers", 0))


iResult = TM1ProcessExecuteEx(hPool, hProcess, hParametersArray)

iType = TM1ValType(hUser, iResult)
MsgBox "The result Type is " & iType
Exit Sub
err_para:
MsgBox "Unkown error"

End Sub

[Mike Cowie]

Hi Rashed,

I'm not sure if you left out some lines of code, but both your hServer and hParametersArray variables don't contain what you need. hServer needs to be a handle to the TM1 Server *object*, not the *name* like what you've used - to get the object handle you either need to leverage the TM1 Excel Client's TM1 API Session (more on this below) or connect to the TM1 Server. And hParametersArray needs to be an array value capsule (you didn't do anything with it) even if your process has no parameters. Your call to get a handle to the TI process is probably the step that's failing, and that problem cascades down to your call to execute the process. To get more information about an error you need to use TM1ValErrorCode and/or TM1ValErrorString_VB, but given the omissions noted above I don't think it will tell you anything more useful.

I think someone on the forum at some stage posted some sample VBA code to run a TI process. I can't seem to locate it, but here is some code I'm ripping out of something else I did - I didn't put in all TM1 API function declarations and some other snipping I did means it might be incomplete/not compile, but hopefully you'll get the general idea:

Code: Select all

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

'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())
  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
  
  '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)
  
  'Check process result
  'NOTE: TM1ProcessExecuteEx returns an array, so you may need to use those TM1 API array functions to get the result
  ' or consider using the TM1ProcessExecute if all you want to know is whether the process succeeded or had some kind of error

Cleanup_API:
  TM1ValPoolDestroy ValPoolHandle
End Function

Hope that helps - sorry again if the code doesn't compile, but I thought you'd get some general ideas from this. Let me know if you have any questions/problems.

Regards,
Mike

[Rashed]

apologies for a late reply. Thanks very much for your help. It worked like a charm!!!!!

[Kingsley]

Hi All,

I need a little help on this topic. I have a command button (ActiveX) control on a spreadsheet and am trying to figure out the VBA code on how to get it to run a TI Process.

I need a little clarification on where/how to declare the function and connection to my TM1 server.

As a startup, I've taken the code above but was not able to get any further.

Code: Select all

Declare Function TM1_API2HAN Lib "C:\Program Files (x86)\ibm\cognos\tm1\bin\tm1.xll" () As Long
__________________________________________________________________________________________

'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())
  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
  
  '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)
  
  'Check process result
  'NOTE: TM1ProcessExecuteEx returns an array, so you may need to use those TM1 API array functions to get the result
  ' or consider using the TM1ProcessExecute if all you want to know is whether the process succeeded or had some kind of error

Cleanup_API:
  TM1ValPoolDestroy ValPoolHandle
End Function
_____________________________________________________________________________
Private Sub CommandButton1_Click()
   RunTIProcess "tm1server", "mytm1tiprocess"
End Sub

[Alan Kirk]

Hi All,

I need a little help on this topic. I have a command button (ActiveX) control on a spreadsheet and am trying to figure out the VBA code on how to get it to run a TI Process.

I need a little clarification on where/how to declare the function and connection to my TM1 server.

As a startup, I've taken the code above but was not able to get any further.

"Not able to get any further" meaning... what, exactly?

• 4) Similarly if you're getting unexpected results, specifics of what you're running, how, and what results you're getting will yield a more valuable response than "I'm running a T.I. but my code doesn't work properly". If you're getting an error, please be specific about what the error is (full details, not just "a runtime error" or "process terminated with errors"), and the circumstances under which it's occurring.

If I had to hazard a guess, I note that you've declared TM1_API2HAN by itself which to my mind raises the questions:
(a) Have you included the tm1api.bas file that contains all of the other declarations that you need into your project?
(b) Have you set your environment Path variable so that your code can find the relevant libraries?
(c) You do of course realise that TM1_API2HAN requires that the user be logged in via the Excel interface; is this in fact the case?

[rmackenzie]

I have a command button (ActiveX) control on a spreadsheet and am trying to figure out the VBA code on how to get it to run a TI Process.

Did you look at the alternative of using an action button? Using an action button would eliminate the need to write any VBA to run a TI process.

[Kingsley]

I have a command button (ActiveX) control on a spreadsheet and am trying to figure out the VBA code on how to get it to run a TI Process.

Did you look at the alternative of using an action button? Using an action button would eliminate the need to write any VBA to run a TI process.

I am actually trying to avoid Perspective. Making the transition to CAFE.

If I had to hazard a guess, I note that you've declared TM1_API2HAN by itself which to my mind raises the questions:
(a) Have you included the tm1api.bas file that contains all of the other declarations that you need into your project?
(b) Have you set your environment Path variable so that your code can find the relevant libraries?
(c) You do of course realise that TM1_API2HAN requires that the user be logged in via the Excel interface; is this in fact the case?

I am referring to the the Cognos Analaysis for Excel 10.2 Guide.pdf but have not found it very informative as to how TM1 can be setup within VBA scripts.
I shall start off with the hints you've provided me. Thanks! http://publib.boulder.ibm.com/infocente ... 70F57.html

[tomok]

Wow! Where do I start? You realize that the link you provided is for TM1 9.4.1, an almost 10 year old version of the product. CAFE is not going to work with that, not even close. Secondly, the instructions are for Perspectives, not CAFE. Also, did you see this nugget from Alan's post?

(c) You do of course realise that TM1_API2HAN requires that the user be logged in via the Excel interface; is this in fact the case?

He's talking about Perspectives when he says "logged in via the Excel interface".

Bottom line is that none of the examples are going to help you with CAFE.