TM1 Forum

I'm trying to write a value to a string cell in a cube using the VBA API in Excel. I can set everything up for the cube write, but on the actual line, Excel Crashes.
This sheet will only be run as a application through the Perspectives Server Explorer, so I'm trying to piggyback on the existing connection, and I'm guessing that is the issue. Any help would be appreciated.

Yes, the cell combination takes a string. Yes, the cell combination is writable.

Morning Andrew,

I sent you some code a couple of months ago that uses a current connection. Have a look at it. If you need further info give a me a shout,

Jim.

Reached a dead end on this. Can anyone else help shed some light?

aking wrote:I'm trying to write a value to a string cell in a cube using the VBA API in Excel. I can set everything up for the cube write, but on the actual line, Excel Crashes.

Code: Select all

Sub Write_String()
    TM1APIInitialize
    
    Dim hPool As Long
    hPool = TM1ValPoolCreate(TM1_API2HAN)
    
    ' Get the Cube
    Dim hServer As Long
    hServer = TM1SystemServerHandle(TM1_API2HAN, strServerName)
    
    Dim hCube As Long
    hCube = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerCubes, TM1ValString(hPool, strCubeName, 0))
    
    ' Build Element Array
    Dim hDimensions(2) As Long
    hDimensions(1) = TM1ObjectListHandleByNameGet(hPool, hCube, TM1CubeDimensions, TM1ValString(hPool, strDim1Name, 0))
    hDimensions(2) = TM1ObjectListHandleByNameGet(hPool, hCube, TM1CubeDimensions, TM1ValString(hPool, strDim2Name, 0))
    
    Dim arrayElements(2) As Long
    arrayElements(1) = TM1ObjectListHandleByNameGet(hPool, hDimensions(1), TM1DimensionElements, TM1ValString(hPool, strElem1Name, 0))
    arrayElements(2) = TM1ObjectListHandleByNameGet(hPool, hDimensions(2), TM1DimensionElements, TM1ValString(hPool, strElem2Name, 0))
    
    Dim hElements As Long
    hElements = TM1ValArray(hPool, arrayElements, 2)
    
    ' Put value into Cube
    Dim hValue As Long
    hValue = TM1ValString(hPool, "Test", 0)
    
    TM1CubeCellValueSet hPool, hCube, hElements, hValue 'It all goes sideways here
    
    TM1APIFinalize
End Sub

You should never, ever combine the use of TM1_API2HAN with TM1APIInitialize and TM1APIFinalize. Either log in to the server fully via TM1SystemServerConnect and all of the related functions, or obtain the user handle and its associated logins via TM1_API2HAN, but don't mix and match. If you do so you will corrupt your Excel session and a crash will result from it, as I learnt from bitter and repeated experience until I figured out what the problem was.

Also I'd strongly advise against directly using TM1_API2HAN directly in function calls the way you're doing; do the call once and store it in a long integer variable, then pass that variable as the argument where needed.

See my post on this subject in this thread.

Umm, I would question WHY you are trying to use the API to write text to a string cell in the first place when you can use the available old skool TM1 macros to do this piggybacking the existing server connection without any need for heavy duty coding and no API.

Application.Run "DBSS", sValue, sCube, sDim1, sDim2, ..., sDimN

Is there a reason you are taking the hard way as opposed to the easy way?

lotsaram wrote:Umm, I would question WHY you are trying to use the API to write text to a string cell in the first place when you can use the available old skool TM1 macros to do this piggybacking the existing server connection without any need for heavy duty coding and no API.

Application.Run "DBSS", sValue, sCube, sDim1, sDim2, ..., sDimN

Is there a reason you are taking the hard way as opposed to the easy way?

Did you think it might be an exercise in learning VBA? It might also be part of a bigger project. Sometimes the easiest way of doing a small task doesn't fit in with the larger picture.

jim wood wrote:Did you think it might be an exercise in learning VBA? It might also be part of a bigger project. Sometimes the easiest way of doing a small task doesn't fit in with the larger picture.

Possibly, but that isn't explained in the post. And given that from the description given it would seem that using DBSS macro would achieve the same result I'm surprised that no one else had already suggested this obvious (and easier) alternative.

lotsaram wrote:

jim wood wrote:Did you think it might be an exercise in learning VBA? It might also be part of a bigger project. Sometimes the easiest way of doing a small task doesn't fit in with the larger picture.

Possibly, but that isn't explained in the post. And given that from the description given it would seem that using DBSS macro would achieve the same result I'm surprised that no one else had already suggested this obvious (and easier) alternative.

I didn't suggest it because my thoughts were running along the same lines as Jim's. It's a sub not a function, it has no parameters and a no frills name like Write_String. Seemed pretty obvious to me that it was merely a prototype for something more substantial.

While it is right to offer an alterntive and point out it might be a better option, is it right to start questioning the persons reasons for doing something without knowing the full facts??? Where I come from we call that jumping to conclusions. Also where I come from we say "Reel your neck in" in response.

jim wood wrote:While it is right to offer an alterntive and point out it might be a better option, is it right to start questioning the persons reasons for doing something without knowing the full facts??? Where I come from we call that jumping to conclusions. Also where I come from we say "Reel your neck in" in response.

Jim, it's kind of late in my timezone and I may be a little bleary eyed here but with all due respect I think you are over reaching. I don't see that there is anything wrong with offering an alternative (especially if it's an easier one). I'm not jumping to any conclusions, my point exactly is that full information hasn't been provided and therefore it's also a fair question (in terms of seeking more information) to ask why go the API path when it's not necessary for the problem given in the example. You may be in the possession of some inside information in this particular case but the rest of us aren't.

There are many developers who come to TM1 from other products or from a more traditional coding background who are not familiar with the OOTB toolset and often also not at all familiar with Excel and what it can do in the right hands and to whom diving into the API as a way to solve a problem would seem the most natural approach. The simpler and to many of us more obvious approach may not occur to people from such a background. Doesn't mean it's not worth suggesting though.

NB I'm not saying the OP is from such a background. The point is I don't know.

I actually said above you were right to offer an alternative (I'll put that down to the blurry eyes) I was just pointing out that you seemed a little harsh. As Alan said it was pretty obvious from the code that it wasn't a final objective. Also it doesn't say in the guide for asking for assitance that everybody has to explain why they are doing something a certain way. (I'll put that one down to the reason for the blurry eyes??)

aking wrote:Reached a dead end on this. Can anyone else help shed some light?

Firstly, let me re-iterate what Alan says - don't use TM1_API2HAN with the other connection functions as you are just asking for trouble. Secondly, the issue lies with your usage of hElements. From the documentation:

hArrayOfElements is a long. This TM1 value capsule contains an array of element handles, one from each of the dimensions of the cube, in the same dimension order as that with which the cube is defined.

Your mistake is that you haven't actually set the values in the array, you have just defined the bounds of the array. So when you pass in hElements it is causing an error - one of those errors that unfortunately isn't handled gracefully and causes Excel to crash by the sounds of it. To be on the safe side, when developing with this API, you should use TM1ValType on your values and handles in order to check that they are of the expected type before you pass them to other functions. This will save you a lot of headaches.

Regards the other discussion, I infer that the OP isn't the most experienced API programmer in the world and that the code may be part of an exercise in teaching himself or herself in how to get it working. If not and especially if this is part of a larger system then the DBSS TM1 macro would be much more suitable (less complexity, more intuitive, much less coding).

I haven't seen any issue with using TM1_API2HAN and TM1Initialize as of yet, but I'll be wary of that in the future.

We did eventually figure out that the values in the array had not been set. Which is funny, because you have to pass an array into TM1ValArray. Specifically set the values and it all worked.

I didn't use the Application.Run because I haven't done a lot of Excel VBA yet and had forgotten about it.

aking wrote:I haven't seen any issue with using TM1_API2HAN and TM1Initialize as of yet, but I'll be wary of that in the future.

It's more with TM1APIFinalize. Consider what that function is doing; it essentially terminates and cleans up all references to the TM1 connection. If you use that, then any references to the session that the user is logged in on, as represented by the user handle that you obtain via TM1_API2HAN, will also be lost.

As you've already discovered, the API's preferred and indeed only method of error handling when it comes across an invalid handle is to crash Excel.

aking wrote:We did eventually figure out that the values in the array had not been set. Which is funny, because you have to pass an array into TM1ValArray. Specifically set the values and it all worked.

That was a good catch by Robin; I have to admit that I didn't go through the code line by line. As soon as I saw the finalise function in there, that triggered the "unpleasant memories" response.

You may also want to download TM1 Tools. In there is a module called bas_TM1APIUtilities, which are some standard functions that I wrote to support my API code. One of them is CheckValueCapsuleType, which I alsways call after I get a new value capsule (as can be seen from the GetClientsCollection function). Some may call it overkill, but as I said, the API's only method of error handling is an Excel crash, so...

aking wrote:I didn't use the Application.Run because I haven't done a lot of Excel VBA yet and had forgotten about it.

It's a better way of doing things if you have the option. Unfortunately the macros don't cover everything, but what they do cover makes life a lot easier than API code. (Generally speaking, anyway.)

@cefPyncdefs

Couldn't have said it better myself.

aking wrote:@cefPyncdefs

Couldn't have said it better myself.

You know that that post is going to have a lot of people scratching their heads now.

Yes, the spammers are running rampant again; fortunately you (the broader membership) only see a handful that sneak through. The vast majority are obliterated before they have a chance to spew forth their rubbish, and the rest don't live long though the admins can't be everywhere at once. All of their posts are deleted at the same time as we zap their account (and most are never actually viewed by anyone, including the admins), so the post (and poster's account) that you're referring to has long since vanished into the electronic compost heap that it so richly deserves to be in.

(Now if there was only some way we could arrange for the same fate for the posters...)

Alan Kirk wrote:Yes, the spammers are running rampant again; fortunately you (the broader membership) only see a handful that sneak through. The vast majority are obliterated before they have a chance to spew forth their rubbish, and the rest don't live long though the admins can't be everywhere at once. All of their posts are deleted at the same time as we zap their account (and most are never actually viewed by anyone, including the admins), so the post (and poster's account) that you're referring to has long since vanished into the electronic compost heap that it so richly deserves to be in.

(Now if there was only some way we could arrange for the same fate for the posters...)

Although for whatever reason the spam posts do seem to be preserved is RSS feeds so some of us still get to enjoy the spam ... that one did contain this gem "Then raised his head and waited, with potatoes, sour milk takaz number or pink borscht. Servant has brought just the mattress generously proceeded to roast lunch tutoring ..." I wonder if it made as much sense in the original polish?