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:
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.