TM1 Forum

Posted: **Thu Sep 28, 2017 2:45 am**

I've begun delving into TM1's API and am using the sample code provided by TM1 to get started. So far so good, but the following code continues to crash Excel and I don't know why.

It crashes when trying to use the "TM1ObjectListCountGet" function.

Code: Select all

Sub ListCubes()
    Dim hUser As Long, hPool As Long, hServer As Long, vbOK As Long
    Dim sServerName As String, sClientId As String, sPassword As String
    Dim viCubeCount As Long

    TM1APIInitialize

    hUser = TM1SystemOpen()
    sServerName = "RxSandboxAW"
    sClientId = "admin"
    sPassword = ""

    Call TM1SystemAdminHostSet(hUser, "csidcbipt059.ivdc.kp.org")

    hPool = TM1ValPoolCreate(hUser)
    
    hServer = TM1SystemServerConnect(hPool, TM1ValString(hPool, sServerName, 0), TM1ValString(hPool, sClientId, 0), TM1ValString(hPool, sPassword, 0))
   
    If TM1ValType(hUser, hServer) = TM1ValTypeError() Then
        Debug.Print "Cannot connect to server"
        Exit Sub
    Else
        Debug.Print "Connected to server"
    End If
            
    ' This is the line that crashes. Determine the number of cubes in the database
    viCubeCount = TM1ObjectListCountGet(hPool, hServer, TM1ServerCubes())

  
    vbOK = TM1SystemServerDisconnect(hPool, hServer)
    Call TM1ValPoolDestroy(hPool)
    Call TM1SystemClose(hUser)
    Call TM1APIFinalize
End sub

Posted: **Thu Sep 28, 2017 4:17 am**

ptownbro wrote: ↑Thu Sep 28, 2017 2:45 am I've begun delving into TM1's API and am using the sample code provided by TM1 to get started. So far so good, but the following code continues to crash Excel and I don't know why.

The advice that you will get here from pretty much anyone who knows what they are doing (certainly every admin and MVP without exception as far as I know) is that the API is something to be called only as a last resort. With the overwhelming majority of things you are far better off using macro and worksheet function calls. In addition to which, that API, along with all of the legacy APIs, will be going away. And not in the distant future, but the near future. While it will be around as long as there are users of 10.2.2 and PA 2, now is perhaps not the best time to be developing applications based on the legacy APIs. You can take or leave that advice according to your preference.

You should also be aware that the classic API's standard method of error handling is to dump Excel into a smouldering heap on your desktop. You need to check each and every returned value for errors. If any reference is wrong, it will crash Excel on you.

To help alleviate this somewhat I would recommend that if you are doing something in an Excel VBA environment you avoid logging in with user name and password, and instead piggy-back off the user's login using the undocumented TM1_API2HAN function. This ensures that at least your user handle is valid.

As an aside, I would definitely not ever do this:

Code: Select all

vbOK As Long

vbOK is a Visual Basic constant. Treating any VB constant as a variable is leading with your chin. However it probably isn't the cause of your crash.

I copied the code below and changed nothing but the server name, login name and server host. I could not cause Excel to crash even if I changed the Admin Host to something invalid. Also when I extracted the value from the value capsule, I got the correct result. So it's not the code; it's almost certainly something to do with the user handle or the server handle. I would suggest checking exactly what the valtype of those are.

Also for the record... to get the number of cubes (as but one example)?

Code: Select all

lCubesCnt = Application.Run("DimSiz", "ServerName:}Cubes")

I think that you would agree that this is rather more compact than the API code above which does the same thing (almost; in the API code you still need to validate the contents of the value capsule, then extract it), and much less inclined to turn your Excel session into a pile of ashes.

Posted: **Thu Sep 28, 2017 8:52 am**

Hi,

Don’t take this the wrong way, but given the other topics of yours, I don’t think the API is the way to go now. You are just starting with TM1 (so it seems) and then you first wed to master TM1 before delving into programming it. For one thing (as Alan pointed out): you will find far better and easier solutions,more maintainable, if you know “plain” TM1.

Posted: **Thu Sep 28, 2017 7:00 pm**

Thanks to you both for your responses.

Alan Kirk wrote: ↑Thu Sep 28, 2017 4:17 am[snip]... the API is something to be called only as a last resort. With the overwhelming majority of things you are far better off using macro and worksheet function calls. In addition to which, that API, along with all of the legacy APIs, will be going away.

Hmm. They really are doing away with the APIs. Interesting. How then to they plan to expose their objects to developers? Not only in VBA but in full frameworks likes .NET?

I went the route of APIs because I was interested in learning the basics around automating creating TM1 objects (cubes, dimensions, TI processes, etc...) through VBA and the documentation led me to APIs. I hadn't seen other methods suggested in the documentation yet, so I started there . Plus, using APIs avoids having to have the add-ins loaded to get exposed to the objects, procedures, etc... But I guess you could argue that's not that big of a deal.

Wim Gielis wrote: ↑Thu Sep 28, 2017 8:52 amDon’t take this the wrong way, but given the other topics of yours, I don’t think the API is the way to go now. You are just starting with TM1 (so it seems) and then you first wed to master TM1 before delving into programming it.

Haha. No offense taken. Believe it or not, I've come a long way since my original questions and have come to understand and build quite a bit since then. I won't list my resume, but I'll just say I've had a lot of experience with different tools and can pick up things like this pretty quickly once I get the fundamentals down on how that particular tool handles things.

So if you've ever heard the expression "don't mistake kindness for weakness" what would apply here is "don't mistake questions for stupidity". Not that you're saying that (hopefully =D) but you get the point.

Anyway.. thanks for the help.

Posted: **Thu Sep 28, 2017 7:10 pm**

ptownbro wrote: ↑Thu Sep 28, 2017 7:00 pm How then to they plan to expose their objects to developers? Not only in VBA but in full frameworks likes .NET?

It's called the REST API, which is the way almost all tools are headed for their APIs. It requires absolutely no drivers, files, DLLs etc., on a machine to take advantage of it. You communicate completely with HTTP calls. Since you're so experienced with programming I'm sure you've already heard of REST.

Posted: **Thu Sep 28, 2017 7:32 pm**

ptownbro wrote: ↑Thu Sep 28, 2017 7:00 pm Thanks to you both for your responses.

Alan Kirk wrote: ↑Thu Sep 28, 2017 4:17 am[snip]... the API is something to be called only as a last resort. With the overwhelming majority of things you are far better off using macro and worksheet function calls. In addition to which, that API, along with all of the legacy APIs, will be going away.
Hmm. They really are doing away with the APIs.

They are doing away with those APIs. There is, as Tomok said, a new API based on REST principles, but at present it has limited backward compatibility. There are still a lot of active (if not necessarily supported) versions that can't make use of it.

An API is still the longest and most cumbersome way of getting from A to B in TM1 compared to using the native functionality. Want to create a new cube or dimension? TI is a faster and cleaner way of doing it, especially when combined with a library of wrapper functions like Bedrock.

Posted: **Thu Sep 28, 2017 9:54 pm**

Ok. Thanks everyone.

TM1 Forum

Excel VBA API Crashing on TM1ObjectListCountGet

Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet

Re: Excel VBA API Crashing on TM1ObjectListCountGet