In VBA code, open an Excel workbook of the applications tree

[ffp]

Hi all

Is there any possibility, from VBA code or from any third party, to call and open a workbook that has been uploaded into the "Applications" tree of TM1 Perspective ?
I would like that my VBA macro do this :
1. Log in to TM1 (that's possible through API)
2. Go to the Applications folder
3. Open a specific Workbook, as if it were a real user that had double-clicked on it (so that would open an instance of the workbook with a name like "TM15.xls")

Any suggestion would be appreciated !

[ellissj3]

Hello,

I have not used the below code to open the applications, but you might be able to modify to suit your needs. I have used the Log in and open the Server explorer part (application. run "N_Connect" and Application.run "TM1StartOrionWithAutomation". It's your call to keep/delete the toolbars.

http://www.bihints.com/tm1_and_excel_in_1_click

Let me know if this helps,
Steve

[lotsaram]

What is the benefit of doing this versus just letting the user open the application file themselves?

If it is an issue of not wanting to give users access to the server explorer UI while still giving them TM1 in Excel you can still let then navigate between application folder files pretty easily in Excel via simple "menu" type workbooks with action buttons/hyperlinks.

Or have you tried this? http://www.tm1forum.com/viewtopic.php?f=21&t=4993

If you trust the user to make the selection and click it might achieve the same or better result and be a whole lot easier to implement ....

[ffp]

Hello,

I have not used the below code to open the applications, but you might be able to modify to suit your needs. I have used the Log in and open the Server explorer part (application. run "N_Connect" and Application.run "TM1StartOrionWithAutomation". It's your call to keep/delete the toolbars.

http://www.bihints.com/tm1_and_excel_in_1_click

Let me know if this helps,
Steve

Thank you Steve, that seems to be a way of doing what I need, although it won't be easy to maintain. I'll test it and give a feedback.

What is the benefit of doing this versus just letting the user open the application file themselves?

The thing is that I would like to create an automatic testing script of the applications published in TM1 : the aim is to have a set of non-regression tests.
The idea is to approach as near as possible the "live" behaviour and so open workbooks, run VBA macro... automatically.
I'll check your file as soon as I will be able to read it.

[afshin]

It would be great if you could let us know how you got on ? I am working on something like this as well. Some of our users are complaining that perspectives is slow ( templates open in approx 10 seconds ).

[ffp]

It would be great if you could let us know how you got on ? I am working on something like this as well. Some of our users are complaining that perspectives is slow ( templates open in approx 10 seconds ).

Well, base on the hints of ellissj3, it is technically possible to open an Excel workbook from a VBA macro.
In fact, the trick is the following : after log in and start of "TM1 Perspective" (TM1StartOrionWithAutomation), it is possible to use in VBA the "SendKeys" functions that send instructions to the keyboard while Perspective tree is active, such as "right arrow", "down arrow"... just as if it was a real user that was using the keyboard to access the workbooks. With a correct combination of right, down and enter, you'll be able to get to the application folder and then go until your workbook and finally open it.
But I would not recommend to use this "trick" because it is very unstable :
- the Perspective application window has to be active during the execution of the macro : if any other window activate itself during this time, the macro will continue executing on the new active window leading to unexepected behaviour (and it's not possible to know what happened afterwards)
- the user that launches the macro cannot do anything on his desktop (that could be resolved by executing the macro on the TM1 server)
- the macro execution is much faster than the window response of Perspective : this implies to use the macro "Application.Wait Now + TimeValue("00:00:XX")" where XX is the number of seconds to wait before going to next instruction. This means that the development of the macro is quite long, the debugging probably worse and the maintenance a nightmare !

So, on my side, I will focus more on opening the workbooks from }Externals directory of the server. I'll have to play with the date/time name extensions of the file but that seems to be more realistic than the solution above.

[lotsaram]

It would be great if you could let us know how you got on ? I am working on something like this as well. Some of our users are complaining that perspectives is slow ( templates open in approx 10 seconds ).

Make sure that the TM1RebuildOption parameter is set to false. This will stop workbooks recalculating on open and could considerably speed up the time to open.
(TM1RebuildOption=0 in the Excel Name Manager)