TM1 Forum

Hi! Everyone,

Is it possible in MACRO to create a TI and write codes in it by only the macro itself? I am just curious.

Thanks!

bunchukokoy wrote: Is it possible in MACRO to create a TI and write codes in it by only the macro itself? I am just curious.

It can be done via the API.

It is not, however, for the inexperienced or the timid, and not a task to be undertaken unless there is absolutely no alternative.

Expect to crash Excel and / or your server many times before you get it right.

Thanks Allan!

Hi! Guys,

I'm kinda interested at writing codes in TI (in prolog, epilog, matadata, data tabs) via MAcro. But I don't know how to start that, Sir Allan has told it's in API, but does someone know how to start a basic, using macro?

Thanks!

bunchukokoy

{Admin Note: Topics merged in response to user report.}

There's no way to do this via Macro. API is the only way to do it from Excel.

As Alan says it's a last resort option. I'd strongly recommend against it.

Martin

Hi. See below - this will start you off - it will create an empty process. As you can see it's not that straight forward and I'd say there is little value trying to create a TI process through VBA. Just copy and paste what's below into a VBA module and run the sub createEmptyProcess. Good luck.

Option Explicit

Declare Sub TM1APIInitialize Lib "tm1api.dll" ()
Declare Sub TM1APIFinalize Lib "tm1api.dll" ()

Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Sub TM1SystemClose Lib "tm1api.dll" (ByVal hUser As Long)

Declare Sub TM1SystemAdminHostSet Lib "tm1api.dll" (ByVal hUser As Long, ByVal AdminHosts As String)
Declare Function TM1SystemServerConnect Lib "tm1api.dll" (ByVal hPool As Long, ByVal sServer As Long, ByVal sClient As Long, ByVal sPassword As Long) As Long

Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Sub TM1ValPoolDestroy Lib "tm1api.dll" (ByVal hPool As Long)

Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long

Declare Function TM1ProcessCreateEmpty Lib "tm1api.dll" (ByVal hPool As Long, ByVal hServer As Long) As Long
Declare Function TM1ObjectRegister Lib "tm1api.dll" (ByVal hPool As Long, ByVal hParent As Long, ByVal hObject As Long, ByVal sName As Long) As Long


Public Function addProcess(ByVal sAdminHost As String, ByVal sServerName As String, _
ByVal sClientName As String, ByVal sClientPassword As String, ByVal sProcessName As String) As Boolean

Dim hUser As Long
Dim hServerName As Long, hClientName As Long, hClientPassword As Long
Dim hServer As Long
Dim hProcessName As Long
Dim hPool As Long
Dim lReturn1, lReturn2 As Long

hUser = TM1SystemOpen()

hPool = TM1ValPoolCreate(hUser)

TM1SystemAdminHostSet hUser, sAdminHost
hServerName = TM1ValString(hPool, Trim(sServerName), 0)
hClientName = TM1ValString(hPool, Trim(sClientName), 0)
hClientPassword = TM1ValString(hPool, Trim(sClientPassword), 0)

hProcessName = TM1ValString(hPool, Trim(sProcessName), 0)

hServer = TM1SystemServerConnect(hPool, hServerName, hClientName, hClientPassword)

lReturn1 = TM1ProcessCreateEmpty(hPool, hServer)
lReturn2 = TM1ObjectRegister(hPool, hServer, lReturn1, hProcessName)

TM1ValPoolDestroy (hPool)


TM1SystemClose (hUser)
TM1APIFinalize

End Function


Sub createEmptyProcess()

Call addProcess("yourHost", "youServer", "YourClientName", "YourClientPassword", "yourProcessName")

End Sub

Well, the empty process has been created. But...
Would you put some code to add some code to this empty process?

hi!

Thanks very much for that codes!

Thanks!

Ok for those of you interested - here is ans example of some code that puts some code into the TI process. Again much easier to use to TI inteface directly in Tm1
Copy and paste what's below into a VBA module and run the sub createEmptyProcess cheers

'Written by John Rizk

Option Explicit

Declare Sub TM1APIInitialize Lib "tm1api.dll" ()
Declare Sub TM1APIFinalize Lib "tm1api.dll" ()

Declare Function TM1SystemOpen Lib "tm1api.dll" () As Long
Declare Sub TM1SystemClose Lib "tm1api.dll" (ByVal hUser As Long)

Declare Sub TM1SystemAdminHostSet Lib "tm1api.dll" (ByVal hUser As Long, ByVal AdminHosts As String)
Declare Function TM1SystemServerConnect Lib "tm1api.dll" (ByVal hPool As Long, ByVal sServer As Long, ByVal sClient As Long, ByVal sPassword As Long) As Long

Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Sub TM1ValPoolDestroy Lib "tm1api.dll" (ByVal hPool As Long)

Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize As Long) As Long

Declare Function TM1ValArray Lib "tm1api.dll" (ByVal hPool As Long, ByRef sArray() As Long, ByVal MaxSize As Long) As Long
Declare Function TM1ValArrayGet Lib "tm1api.dll" (ByVal hUser As Long, ByVal vArray As Long, ByVal index As Long) As Long
Declare Function TM1ValArrayMaxSize Lib "tm1api.dll" (ByVal hUser As Long, ByVal vArray As Long) As Long
Declare Sub TM1ValArraySet Lib "tm1api.dll" (ByVal vArray As Long, ByVal val As Long, ByVal index As Long)
Declare Sub TM1ValArraySetSize Lib "tm1api.dll" (ByVal vArray As Long, ByVal Size As Long)

Declare Function TM1ServerProcesses Lib "tm1api.dll" () As Long
Declare Function TM1ProcessCreateEmpty Lib "tm1api.dll" (ByVal hPool As Long, ByVal hServer As Long) As Long
Declare Function TM1ProcessPrologProcedure Lib "tm1api.dll" () As Long
Declare Function TM1ProcessEpilogProcedure Lib "tm1api.dll" () As Long
Declare Function TM1ProcessCheck Lib "tm1api.dll" (ByVal hPool As Long, ByVal hProcess As Long) As Long
Declare Function TM1ProcessUpdate Lib "tm1api.dll" (ByVal hPool As Long, ByVal hOldProcess As Long, ByVal hNewProcess As Long) As Long

Declare Function TM1ObjectListHandleByIndexGet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal iPropertyList As Long, ByVal iIndex 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 TM1ObjectPropertySet Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long, ByVal Property_P As Long, ByVal ValRec_V As Long) As Long

Declare Function TM1ObjectDuplicate Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long) As Long
Declare Function TM1ObjectDestroy Lib "tm1api.dll" (ByVal hPool As Long, ByVal hObject As Long) As Long
Declare Function TM1ObjectRegister Lib "tm1api.dll" (ByVal hPool As Long, ByVal hParent As Long, ByVal hObject As Long, ByVal sName As Long) As Long



Public Function addProcess(ByVal sAdminHost As String, ByVal sServerName As String, _
ByVal sClientName As String, ByVal sClientPassword As String, ByVal sProcessName As String) As Boolean

Dim hUser As Long
Dim hServerName As Long, hClientName As Long, hClientPassword As Long, hProcessName As Long
Dim hServer As Long
Dim hPool As Long, hsPool As Long, hpPool As Long, hppPool As Long, hArrayPool As Long
Dim hArray As Long
Dim lArray() As Long
Dim NoParameters As Integer
Dim lReturn1, lReturn2, lReturn3, lReturn4, lReturn5, lReturn6, lReturn7, lReturn8, lReturn9 As Long

hUser = TM1SystemOpen()

hPool = TM1ValPoolCreate(hUser)
hsPool = TM1ValPoolCreate(hUser)
hpPool = TM1ValPoolCreate(hUser)
hppPool = TM1ValPoolCreate(hUser)
hArrayPool = TM1ValPoolCreate(hUser)


hServerName = TM1ValString(hsPool, Trim(sServerName), 0)
hClientName = TM1ValString(hsPool, Trim(sClientName), 0)
hClientPassword = TM1ValString(hsPool, Trim(sClientPassword), 0)
hProcessName = TM1ValString(hsPool, Trim(sProcessName), 0)


TM1SystemAdminHostSet hUser, sAdminHost

hServer = TM1SystemServerConnect(hPool, hServerName, hClientName, hClientPassword)

lReturn1 = TM1ProcessCreateEmpty(hpPool, hServer)
lReturn2 = TM1ObjectRegister(hpPool, hServer, lReturn1, hProcessName)

lReturn3 = TM1ObjectListHandleByNameGet(hpPool, hServer, TM1ServerProcesses, hProcessName)
lReturn4 = TM1ObjectDuplicate(hpPool, lReturn3)


NoParameters = 7
ReDim lArray(NoParameters)
hArray = TM1ValArray(hArrayPool, lArray(), NoParameters)

TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 1
TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 2
TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 3
TM1ValArraySet hArray, TM1ValString(hsPool, "vPrologTest1 = 'This';", 0), 4
TM1ValArraySet hArray, TM1ValString(hsPool, "vPrologTest2 = 'is';", 0), 5
TM1ValArraySet hArray, TM1ValString(hsPool, "vPrologTest3 = 'in';", 0), 6
TM1ValArraySet hArray, TM1ValString(hsPool, "vPrologTest4 = 'Prolog';", 0), 7

lReturn5 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessPrologProcedure, hArray)


NoParameters = 7
ReDim lArray(NoParameters)
hArray = TM1ValArray(hArrayPool, lArray(), NoParameters)

TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 1
TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 2
TM1ValArraySet hArray, TM1ValString(hsPool, "", 0), 3
TM1ValArraySet hArray, TM1ValString(hsPool, "vEpilogTest1 = 'This';", 0), 4
TM1ValArraySet hArray, TM1ValString(hsPool, "vEpilogTest2 = 'is';", 0), 5
TM1ValArraySet hArray, TM1ValString(hsPool, "vEpilogTest3 = 'in';", 0), 6
TM1ValArraySet hArray, TM1ValString(hsPool, "vEpilogTest4 = 'Epilog';", 0), 7

lReturn6 = TM1ObjectPropertySet(hppPool, lReturn4, TM1ProcessEpilogProcedure, hArray)


lReturn7 = TM1ProcessCheck(hpPool, lReturn4)
lReturn8 = TM1ProcessUpdate(hpPool, lReturn3, lReturn4)

lReturn9 = TM1ObjectDestroy(hpPool, lReturn4)

TM1ValPoolDestroy (hPool)
TM1ValPoolDestroy (hsPool)
TM1ValPoolDestroy (hpPool)
TM1ValPoolDestroy (hppPool)

TM1SystemClose (hUser)
TM1APIFinalize

End Function


Sub createEmptyProcess()
Call addProcess("yourHost", "yourServerName", "yourClientName", "yourClientPassword", "yourProcessName")
End Sub