TM1 Tools VBA update

Post Reply
Jayme1
Posts: 14
Joined: Thu Mar 27, 2014 5:30 pm
OLAP Product: turbo intergrator
Version: cognos express
Excel Version: Excel 2009

TM1 Tools VBA update

Post by Jayme1 »

Hello Everyone

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









lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Tools VBA update

Post by lotsaram »

In VBA there are "macros" and "functions". They are basically the same and do the same thing except that a function is declared as having a variable type and usually at the end of the function a value is assigned to the function. In the TM1Tools Add-In RunTIProcessNew is declared as a function with value type string and a value is assigned indicating whether the process ran successfully or not.
..
MsgBox RunTIProcessNew(tm1ServerName, procName, p1, p2)
This just displays the value assigned to RunTIProcessNew in a message box.

You could just as easily have
Dim sMsg As String
sMsg = RunTIProcessNew(tm1ServerName, procName, p1, p2)

You then don't actually have to do anything with the value that is returned and no message box will appear, but you could do a test of the value of sMsg before deciding in the code what else to do.

What did you actually TRY already?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Jayme1
Posts: 14
Joined: Thu Mar 27, 2014 5:30 pm
OLAP Product: turbo intergrator
Version: cognos express
Excel Version: Excel 2009

Re: TM1 Tools VBA update

Post by Jayme1 »

Thanks you very much lotsaram

This make perfect sense now that someone has spelt if out to me.

Thank you for your help
Trasmuss
Posts: 1
Joined: Mon Dec 18, 2017 9:44 am
OLAP Product: IBM Cognos TM1
Version: 9.5
Excel Version: 2016

Re: TM1 Tools VBA update

Post by Trasmuss »

Thank’s for the TM1 Tools, which is very useful.

However, when I run the code shown below I can’t get around having to answer the question in the “View error log?” message box “There was an error when running your process…..”

Usually the process will run with minor errors and then return the error message box which requires a user response. I can’t have that in my unsupervised program.

Sub RunTIProcessTest()
Dim sResult As String
sResult = RunTIProcess("synoptik", "finance gl data")
End Sub

How can I avoid having to answer the error message?
Post Reply