I have been using the TM1 tools http://www.tm1forum.com/viewtopic.php?f=21&t=2515
that someone with far more skill than me has written. I have then copied part of the code and put them into my VBA. This has allowed me to run numerous TM1 processes from inside VBA.
This has been great and whomever wrote this code i owe you as i have been putting it to good use. now though i need to run one process, do some VBA and then run a second process. This can be done with the stolen code, however i need to click on the message box that appears after the fist process has run, before i can then run the rest. This is because the TIProcess is run from within a message box
Code: Select all
MsgBox RunTIProcessNew(tm1ServerName, procName, p1, p2)
for the life of me i cannot work out how to run the TI process from outside a messagebox. Does anyone know of an easy way?
Below is the full code i am using (copied from TM1 tools add in)
Code: Select all
Option Explicit
' IMPORT TXT DATA for Speed ''''
Sub ImportTextFileProccess()
Dim tm1ServerName As String, procName As String
Dim p1, p2, p3, p4, p5, p6, p7 As String
'--- Update this section as required
tm1ServerName = "CXL_MFG_DEV" ' the name of the TM1 server
procName = "x_Run_Input_Template_Load_and_ascii_report" ' the name of the process being called
p1 = Sheets("Update").Range("D12").Value ' parameter CC
p2 = Sheets("Update").Range("D11").Value ' parameter BU
MsgBox RunTIProcessNew(tm1ServerName, procName, p1, p2)
End Sub
' #### END SAMPLE CALL ####
'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 RunTIProcessNew(ByVal ServerName As String, ByVal ProcessName As String, ParamArray ProcessParameters()) As String
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, numResults As Integer, k As Integer, myMsg As String
Dim iType As Integer, errStr As String * 75, tempVal As Long, returnStr As String * 75
'Get handles
SessionHandle = GetExcelSessionHandle()
If SessionHandle = 0 Then
RunTIProcessNew = "ERROR: Cannot communicate with TM1 API."
Exit Function
End If
ValPoolHandle = TM1ValPoolCreate(SessionHandle)
If ValPoolHandle = 0 Then
RunTIProcessNew = "ERROR: Cannot create a TM1 API value pool."
Exit Function
End If
On Error GoTo Cleanup_API
'Get handle to server; check if connected
ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
If ServerHandle = 0 Then
RunTIProcessNew = "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
RunTIProcessNew = "ERROR: Unable to access process " & ProcessName & "."
GoTo Cleanup_API
End If
If TM1ValObjectCanRead(SessionHandle, ProcessHandle) = 0 Then
RunTIProcessNew = "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)
'ExecuteResult is an array containing information about the error type (minor, ProcessQuit, etc) and a reference to the error log location
iType = TM1ValType(SessionHandle, ExecuteResult)
If iType = CInt(TM1ValTypeIndex) Then
RunTIProcessNew = "Process executed successfully"
ElseIf iType = CInt(TM1ValTypeArray) Then
'Retrieve the error
TM1ValErrorString_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(1)), errStr, 75
'Retrieve the error log file
TM1ValStringGet_VB SessionHandle, TM1ValArrayGet(SessionHandle, ExecuteResult, CDbl(2)), returnStr, 75
showProcessError errStr, returnStr
RunTIProcessNew = "Errors occurred during process execution"
ElseIf iType = CInt(TM1ValTypeError) Then
RunTIProcessNew = "Returned an error value. The process was not run for some reason"
ElseIf iType = CInt(TM1ValTypeBool) Then
RunTIProcessNew = "Returned a boolean value. This should not happen."
ElseIf iType = CInt(TM1ValTypeObject) Then
RunTIProcessNew = "Returned an object. This should not happen."
ElseIf iType = CInt(TM1ValTypeReal) Then
RunTIProcessNew = "Returned a real number. This should not happen."
ElseIf iType = CInt(TM1ValTypeString) Then
RunTIProcessNew = "Returned a string. This should not happen."
Else
RunTIProcessNew = "Unknown return value: " & iType
End If
Cleanup_API:
TM1ValPoolDestroy ValPoolHandle
End Function
Sub showProcessError(reason As String, FileName As String)
Dim myMsg As String, FilePath As String, logPath As String, fso As Object
On Error GoTo ErrorHandler
Set fso = CreateObject("Scripting.FileSystemObject")
' Retrieve the log path that the user set in the options dialog box.
logPath = GetSetting(gSC_APP_NAME, gSC_REG_SECT_OPTS, gSC_REGKEY_LOGDIR, "") & "\"
FilePath = logPath & FileName
' Check they've specified a log path
If logPath = "\" Then
myMsg = "There was an error when running your process. Specify a logging directory " & _
"in the TM1 Tools options dialog box to be able to view the error log from this workbook. The error message was " & reason
MsgBox myMsg, vbOKOnly, "An error occurred"
' Check they've specified a valid log path
ElseIf Not fso.FolderExists(logPath) Then
myMsg = "There was an error when running your process, but I can't find the error directory. Check you have specified it " & _
"correctly in the TM1 Tools options dialog box and that you have access privileges. The error message was " & reason
MsgBox myMsg, vbOKOnly, "An error occurred"
' If we can find the error file then give them the option of opening it
ElseIf fso.FileExists(FilePath) Then
myMsg = "There was an error when running your process. Do you wish to view the error log? The error message was " & reason
If MsgBox(myMsg, vbYesNo, "View error log?") = vbYes Then
If Dir(FilePath) = "" Then
MsgBox "I was unable to open the file. The path is " & FilePath
Else
Workbooks.Open FilePath
End If
End If
Else
' Incorrect parameters (either how many of them, or incorrect numeric/string type) sticks a '$' on the end of the error
' log and is locked by TM1 so we can't open it. There may be other reasons too.
myMsg = "An error occurred but I cannot find the error log. This may something is wrong with " & _
"the parameters, or that the error log folder you have specified is incorrect. " & _
Chr(13) & Chr(13) & "The error message was: " & reason
MsgBox myMsg, vbOKOnly, "An error occurred"
End If
ExitPoint:
On Error Resume Next
Set fso = Nothing
On Error GoTo 0
Exit Sub 'avoid error handler
ErrorHandler:
MsgBox "An unknown error occured in the showProcessError sub of the bas_TurboIntegrator module in the TM1_Tools addin"
Resume ExitPoint
End Sub