How to connect TM1 from Excel

[LP_user]

Dear all,

The former developer created one excel file, which uses VBA to draw one button in the excel sheet. Click this button will call some TI process in our TM1 server, and show the result in the excel sheet. I got the excel file. But when I click the button, there is error message:
Please check if you have connected to TM1 server

I have connected to the TM1 server. But it still can't solve the problem. Could you please tell me how to do?


With best regards,

LP_user

[Alan Kirk]

The former developer created one excel file, which uses VBA to draw one button in the excel sheet. Click this button will call some TI process in our TM1 server, and show the result in the excel sheet. I got the excel file. But when I click the button, there is error message:
Please check if you have connected to TM1 server

I have connected to the TM1 server. But it still can't solve the problem. Could you please tell me how to do?

That does not sound like a standard TM1 message. The developer is almost certainly using the N_Connect macro function to connect but if it isn't working and you don't know why, then...

Request for assistance guidelines (PLEASE READ) says:

...remember that including the actual code in your post will be a thousand times more useful than an attempted description of it.

[LP_user]

Hi Alan,

Thanks for your reply. I checked the VBA code again, and found it assigned the wrong path to TM1 add-in. That is why not connecting to TM1 server. And the error message is not standard message. Instead, it is our own VBA code.

Best,

LP_user

[LP_user]

Hi Alan,

As I said in my last post, the VBA code sets the fixed path to access TM1 Excel Add-in. But different user has his own TM1 install path. I just changed the path to my TM1 install path. And the excel file worked for me at the moment. But if other user wants to use this excel file to get information for TM1 server, he might meet the same problem.

How can I set the relative path for TM1 Excel Add-in. It would be nice if you could show me the VBA example code.


With best regards,

LP_user

The former developer created one excel file, which uses VBA to draw one button in the excel sheet. Click this button will call some TI process in our TM1 server, and show the result in the excel sheet. I got the excel file. But when I click the button, there is error message:
Please check if you have connected to TM1 server

I have connected to the TM1 server. But it still can't solve the problem. Could you please tell me how to do?

That does not sound like a standard TM1 message. The developer is almost certainly using the N_Connect macro function to connect but if it isn't working and you don't know why, then...

Request for assistance guidelines (PLEASE READ) says:

...remember that including the actual code in your post will be a thousand times more useful than an attempted description of it.

[tomok]

Under what circumstances would you need to know the path to the add-in? If the add-in is loaded, any call to a TM1 function will work without having to know the path to the add-in. If you are loading the add-in as part of the VBA I would recommend changing that. I have built tons of VBA infused spreadsheets over the years and have never felt the need to make loading the add-in itself as part of the VBA.

[Plunge pool]

Hi there, I would LOVE to include this code in my macros.

Could you post it for me to use? Instead of me trying to figure all this out?

PPPLLLLLLLEASSSEEE

[Alan Kirk]

Hi there, I would LOVE to include this code in my macros.

Could you post it for me to use? Instead of me trying to figure all this out?

PPPLLLLLLLEASSSEEE

I'm not sure which part of Tomok's reply was unclear.

The part where he implied that such a design is a bad idea?

The bit where he implied that it is a bad idea?

Or was it just the bit that implied that It. Is. A. BAD. IDEA?

First of all there is no "relative path". There is a DEFAULT install path which, it might be noted, has changed not less than THREE times between versions 9.0 and 10.1. The latest change (10.1) was to insert a folder named "ibm" (all lower case, just to be passive anti-Windows aggressive) directly under the Program Files folder. The benefit of this? Probably to ensure that IBM's Presence As An Internationally Recognised Brand (™, ®, © etc) goes undiminished, and that there be "consistency across all platforms". Doubtless there is a mouthpiece somewhere in Armonk who could chime in with corporate lie #18: "Our customers have told us that this is what they want". (When in fact what customers want is for the software to work reliably and well and for upgrades to involve as little pain as possible.)

Of course the small, minor, trivial price of this change is that it broke any Excel applications which used the TM1 API because IBM couldn't be @r$ed to update the Path environment variable in Windows to show the new path to the API libraries. They felt it was sufficient to just put a note in the manuals (well, the classic API manual anyway, they forgot to include the .Net one in the 10.1 install and still haven't remedied that, nor have they done a damn thing about posting it for download as far as I can see) telling the users to manually update it themselves. (After the users get through manually uninstalling their previous version, which the installer also can't handle.) For after all, what do we pay oceans of money for maintenance for each year if not to have to manually fix the things that IBM breaks in pursuit of the Great God Of Branding?

Such changes would ALSO break any Excel code that relied on loading the TM1 add-in based on the default install path. There is no guarantee that in 10.2 IBM won't get in a Feng Shui consultant who will tell them that the path has the wrong number of letters for harmonic balance and they then change it again.

Not that it matters because there is no guarantee that users or IT departments will have installed the thing on the default path anyway. They can install it anywhere they please which would again break an application which is dependent on knowing the path to the add-in.

Again, It. Is. A. Bad. Idea.

If you desperately wanted to pursue this Bad Idea, there is PROBABLY an entry somewhere in the Registry which will tell you which path the TM1 software is installed to, and if you write enough Win32 API code (for the native VBA functions only allow you access to one small sub-area of the Registry) then you may be able to find it. I decline to take up this challenge, though someone else may.

Assuming, of course, that the registry entries ALSO don't change between versions.

You are certainly not going to be getting the information from the TM1 software itself since you don't have it loaded.

And let's not go into the fact that if you're loading the add-in .xla file from an untrusted location via VBA code rather than letting Excel load it as an installed add-in, then the user will get a macro enable prompt (assuming that their security is set higher than "Take Me, I'm Yours") which, if they fail to enable macros, will ALSO break the application.

In short, loading the add-in immediately before using it is unreliable, as well as being inefficient. Or, to put it more bluntly, A. Bad. Idea.

If there is any prospect that the user is going to be using TM1 with Excel there is only one single reason for not loading TM1 as an installed add-in, and that is if you have another add-in that doesn't play well with it. My preferred way of handling that would be to have two shortcuts, each of which launches Excel with only the relevant add-in. But aside from that pretty rare instance there is no reason at all, NONE (aside from masochism, perhaps), to write code to launch the add-in "on demand".

[Plunge pool]

Hi there, I would LOVE to include this code in my macros.

Could you post it for me to use? Instead of me trying to figure all this out?

PPPLLLLLLLEASSSEEE

I'm not sure which part of Tomok's reply was unclear.

The part where he implied that such a design is a bad idea?

The bit where he implied that it is a bad idea?

Or was it just the bit that implied that It. Is. A. BAD. IDEA?

First of all there is no "relative path". There is a DEFAULT install path which, it might be noted, has changed not less than THREE times between versions 9.0 and 10.1. The latest change (10.1) was to insert a folder named "ibm" (all lower case, just to be passive anti-Windows aggressive) directly under the Program Files folder. The benefit of this? Probably to ensure that IBM's Presence As An Internationally Recognised Brand (™, ®, © etc) goes undiminished, and that there be "consistency across all platforms". Doubtless there is a mouthpiece somewhere in Armonk who could chime in with corporate lie #18: "Our customers have told us that this is what they want". (When in fact what customers want is for the software to work reliably and well and for upgrades to involve as little pain as possible.)

Of course the small, minor, trivial price of this change is that it broke any Excel applications which used the TM1 API because IBM couldn't be @r$ed to update the Path environment variable in Windows to show the new path to the API libraries. They felt it was sufficient to just put a note in the manuals (well, the classic API manual anyway, they forgot to include the .Net one in the 10.1 install and still haven't remedied that, nor have they done a damn thing about posting it for download as far as I can see) telling the users to manually update it themselves. (After the users get through manually uninstalling their previous version, which the installer also can't handle.) For after all, what do we pay oceans of money for maintenance for each year if not to have to manually fix the things that IBM breaks in pursuit of the Great God Of Branding?

Such changes would ALSO break any Excel code that relied on loading the TM1 add-in based on the default install path. There is no guarantee that in 10.2 IBM won't get in a Feng Shui consultant who will tell them that the path has the wrong number of letters for harmonic balance and they then change it again.

Not that it matters because there is no guarantee that users or IT departments will have installed the thing on the default path anyway. They can install it anywhere they please which would again break an application which is dependent on knowing the path to the add-in.

Again, It. Is. A. Bad. Idea.

If you desperately wanted to pursue this Bad Idea, there is PROBABLY an entry somewhere in the Registry which will tell you which path the TM1 software is installed to, and if you write enough Win32 API code (for the native VBA functions only allow you access to one small sub-area of the Registry) then you may be able to find it. I decline to take up this challenge, though someone else may.

Assuming, of course, that the registry entries ALSO don't change between versions.

You are certainly not going to be getting the information from the TM1 software itself since you don't have it loaded.

And let's not go into the fact that if you're loading the add-in .xla file from an untrusted location via VBA code rather than letting Excel load it as an installed add-in, then the user will get a macro enable prompt (assuming that their security is set higher than "Take Me, I'm Yours") which, if they fail to enable macros, will ALSO break the application.

In short, loading the add-in immediately before using it is unreliable, as well as being inefficient. Or, to put it more bluntly, A. Bad. Idea.

If there is any prospect that the user is going to be using TM1 with Excel there is only one single reason for not loading TM1 as an installed add-in, and that is if you have another add-in that doesn't play well with it. My preferred way of handling that would be to have two shortcuts, each of which launches Excel with only the relevant add-in. But aside from that pretty rare instance there is no reason at all, NONE (aside from masochism, perhaps), to write code to launch the add-in "on demand".

Hi ALan, Ok point taken.

Yes, the user is going to be using excel without TM1 alot of times. For one thing, macros already developed pre-TM1 instalation were working fine, and then they don't work anymore when the TM1 addin starts. Hence why I wanted an on- demand way to start. LOL Fengshui Consultant

[upali]

Hi Plunge pool,

I use this file for the exact reason you mentioned. It loads the TM1 plugin, and unloads it when you exit Excel. So when you want to use Excel without TM1, simply open Excel. When you want TM1, just double click this file.

Does that help?

[Harvey]

I wrote code to load the add-in once.

The reason was I was writing my own Excel formula Add-in that relied on TM1 and thought it would be better for it to load its own dependencies. It was also important (for some reason I can't recall) that the add-ins loaded in the correct order, so I wrote it explicitly into the VBA.

I felt it was justified at the time, but I agree that generally there is no need or desire to do it.