TM1 API - TM1DimensionElementInsert crashes Excel
Posted: Tue Jun 12, 2012 11:35 pm
I'm working with the TM1 VB API for the first time. My VBA script is ultimately going to loop through certain cells in a worksheet and add them to a dimension. From what I understand, TM1DimensionElementInsert should be able to help me accomplish this.
I've pasted my first attempt at using the function call with the planning sample database. In the below example, I'm trying to add Jan-2005 to the plan_time dimension after Dec-2004. All of the handles come back with a non-zero value. However, Excel crashes when it tries to do the element insert.
I'm assuming one of my handles is not correct. Any direction would be be greatly appreciated.
I've pasted my first attempt at using the function call with the planning sample database. In the below example, I'm trying to add Jan-2005 to the plan_time dimension after Dec-2004. All of the handles come back with a non-zero value. However, Excel crashes when it tries to do the element insert.
I'm assuming one of my handles is not correct. Any direction would be be greatly appreciated.
Code: Select all
Declare Function TM1_API2HAN Lib "tm1.xll" () As Long
Declare Function TM1ValPoolCreate Lib "tm1api.dll" (ByVal hUser As Long) As Long
Declare Function TM1SystemServerHandle Lib "tm1api.dll" (ByVal hUser As Long, ByVal name As String) As Long
Declare Function TM1TypeElementSimple Lib "tm1api.dll" () As Long
Declare Function TM1ServerDimensions Lib "tm1api.dll" () As Long
Declare Function TM1DimensionElements Lib "tm1api.dll" () As Long
Declare Function TM1ValString Lib "tm1api.dll" (ByVal hPool As Long, ByVal InitString As String, ByVal MaxSize 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 TM1DimensionElementInsert Lib "tm1api.dll" (ByVal hPool As Long, ByVal hDimension As Long, ByVal hElementAfter As Long, _
ByVal sName As Long, ByVal iType As Long) As Long
Public Function MyDimFunction()
Dim SessionHandle As Long
Dim ValPoolHandle As Long
Dim DimHandle As Long
Dim ElHandle As Long
Dim hElement As String
Dim ServerName As String
ServerName = "planning sample"
SessionHandle = Application.Run("TM1_API2HAN")
MsgBox "SessionHandle: " & SessionHandle
ValPoolHandle = TM1ValPoolCreate(SessionHandle)
MsgBox "ValPoolHandle: " & ValPoolHandle
ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
MsgBox "ServerHandle: " & ServerHandle
DimHandle = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle, TM1ServerDimensions, TM1ValString(ValPoolHandle, "plan_time", 0))
MsgBox "DimHandle: " & DimHandle
ElHandle = TM1ObjectListHandleByNameGet(ValPoolHandle, DimHandle, TM1DimensionElements, TM1ValString(ValPoolHandle, "Dec-2004", 0))
MsgBox "ElHandle: " & ElHandle
hElement = TM1DimensionElementInsert(ValPoolHandle, DimHandle, ElHandle, TM1ValString(hPool, "Jan-2005", 0), TM1TypeElementSimple())
End Function