Using VBA to connect to TM1 application

[LubosR]

Hi, sorry if this is already described somewhere, but I couldn't find it.

I was able to identify how to refresh/refresh all sheets using TM1 cognos, but so far didn't find the way how to automatize the connection to server when using TM1.

I know how to connect using Essbase software, but now switching to TM1 and have problems identifying/writing the VBA macro to automatize the connection.

Would you please help on that problem or advise where I can find the solution and any other helpful macros/tricks for TM1?

P.s.: using Office 2003 and latest TM1 release (=IBM employee so have very latest one )

Thank you

[LubosR]

.... I understand that the connection macro is somewhere inside the VBA modules that I have already inside excel VBA explorer, just can't find it.....

[Andy Key]

To log on using VBA rather than manually doing it in Server Explorer, you can use

Code: Select all

Application.Run "N_CONNECT", "<Server>", "<Client>", "<Password>"

with

Code: Select all

Application.Run "N_DISCONNECT"

to disconnect. Note that this disconnects you from all remote servers.

[LubosR]

Hi Andy,

thank you, tried your connection code that is calling N_CONNECT macro.
It's not doing anything for myself even if I leave server, user, Pass empty it's doesn't bring up login window (which it probably should do right?)

Can it be that my TM1 functions/macros that are doing connection (functions hidden behind the buttons) are changed/edit for internal company usage so it's not "N_CONNECT" anymore?

If yes, would you have please any idea in which section the 'connection command' would be?

I'm attaching the VBA explorer to show what is opening automatically when I open empty Ssheet (connect.xls)

Probably my problem here is that I'm not calling the correct function behind 'connection button'

Thank you

[LubosR]

...or maybe, do I need to copy declare functions into blank sheet all the time?

...probably not yes, as those functions are preloaded by default when I open any ssheet... ?

[Mike Cowie]

Hi Andy,

thank you, tried your connection code that is calling N_CONNECT macro.
It's not doing anything for myself even if I leave server, user, Pass empty it's doesn't bring up login window (which it probably should do right?)

N_CONNECT will never show a form/user window for login - if you want to use a login form you'll need to create one yourself. N_CONNECT is just a function you can call to try and make a connection to a TM1 Server for a particular server name, user name and password (which you would specify).

The only TM1 VBA functions that bring up forms are ones like E_PICK. I don't know of any way, supported or not, to bring up the TM1 client's own login window.

Regards,
Mike

[LubosR]

OK understand, thank you

One more question.
Now I see that inside company VBA is first prompting me to pickup server/application, but after doing that it's redirecting me to another window (looking as flash window) where I insert my ID/password (probably from website where company stores employees ID's/Pass)

am I able somehow to have VBA managing this flash window, or I can forget that straight and save my time searching? (I'm guessing that answer is that I can forget about it, but wanted to try my luck with experts... )

[Wim Gielis]

Do you mean a box like that in attachment?

[Alan Kirk]

Hi Andy,

thank you, tried your connection code that is calling N_CONNECT macro.
It's not doing anything for myself even if I leave server, user, Pass empty it's doesn't bring up login window (which it probably should do right?)

N_CONNECT will never show a form/user window for login - if you want to use a login form you'll need to create one yourself. N_CONNECT is just a function you can call to try and make a connection to a TM1 Server for a particular server name, user name and password (which you would specify).

The only TM1 VBA functions that bring up forms are ones like E_PICK. I don't know of any way, supported or not, to bring up the TM1 client's own login window.

There's a way of doing it if (a) you're on an Excel version prior to 2007 (there's probably a way of doing it there too, but I haven't explored the Ribbon Factory sufficiently to be sure) and (b) you have the standard TM1 menu items available and haven't screwed around with them.

It ain't pretty, but it seems to work. Basically you just iterate through the menus until you find the right one, then execute it. (Tested on Excel 2003, TM1 versions 9.0 SP3 and 9.4 MR1.)

Code: Select all

Sub DisplayTM1Login()
Dim cbr As CommandBar
Dim cbrctl_TM1 As CommandBarControl
Dim cbrctl_Network  As CommandBarControl
Dim cbrctl_NetworkConnect As CommandBarControl
Dim b_ControlFound As Boolean

On Error Resume Next
Set cbr = Application.CommandBars("Worksheet Menu Bar")
On Error GoTo ErrorHandler

If cbr Is Nothing Then
    Err.Raise vbObjectError + 1000, , "Cannot find the Worksheet command bar."
End If


For Each cbrctl_TM1 In cbr.Controls
    If cbrctl_TM1.Caption = "TM&1" Then
        
        For Each cbrctl_Network In cbrctl_TM1.Controls
        
            If cbrctl_Network.Caption = "&Network" Then
            
                For Each cbrctl_NetworkConnect In cbrctl_Network.Controls
                
                    If cbrctl_NetworkConnect.Caption = "&Connect..." Then
                        b_ControlFound = True
                        cbrctl_NetworkConnect.Execute
                    End If
                
                Next
            
            End If
            
        Next
    
    End If
    
Next

If Not b_ControlFound Then
    Err.Raise vbObjectError + 1000, , "Cannot find the TM1 menu item."
End If

ExitPoint:
Set cbrctl_NetworkConnect = Nothing
Set cbrctl_Network = Nothing
Set cbrctl_TM1 = Nothing
Set cbr = Nothing

Exit Sub

[image2x]

Curious if Alan or others may have since come up with a solution for reliably displaying the tm1 connect box in Excel 2007 with the tm1 ribbon?

Thanks,
-- John

[paulsimon]

Hi

The N_CONNECT macro should have worked for you. A few things to check

1) Were you trying it from Excel with TM1 loaded
2) Were your TM1 Options set with the Admin Host on which the server is located

If so, then it should work.

Regards

Paul Simon

[image2x]

The N_CONNECT macro should have worked for you. A few things to check

N_CONNECT isn't an option in my case as we're using CAM security. I'm able to trigger the connect box via sendkeys but would like a better solution.

[jameswebber]

Try Application.Run ("NET_CONN")

[image2x]

Try Application.Run ("NET_CONN")

Sweet! That works -- even with CAM security.

Thanks James. I was honestly a day away from trying to replicate Alan's recursive button find approach but with the ribbon xml method.

[smorgan36]

I have to generate 200+ workbooks from a master template. The VBA code to do this opens the template, then populates it with a specific id number from a list of 200+ numbers. The id number matches an id number in a dimension in a TM1 cube. The VBA code executes a TM1 Calculate which populates the workbook with data from the cube using the DBRW function. Several other things happen next in the VBA code. The workbook is eventually saved using the id number in the filename so as not to overwrite the master template then closed and the VBA loops to the next id number. The master template is opened again and the process starts over with the new id number. This works well except that it takes a long time to generate the 200+ workbooks. Opening, saving and closing the workbook takes up about 90% of the time it takes to cycle through this process for one id number. If I open the workbook with TM1 disconnected, it opens much quicker.

After the DBRW is performed and the cells are populated, I can disconnect from TM1 using "N_DISCONNECT". However, the VBA statement: Application.Run "N_CONNECT", "<Server>", "<Client>", "<Password>" doesn't work for me. "NET_CONN" works, but I have to intervene and click the 'OK' button on two popup alerts in order to connect. So, can anybody suggest a way around this? Either a different Application.Run statement or a way to automatically click the two popups alerts or a way to prevent the alerts from opening?

I'm using Excel 2010 SP1, TM1 10.1, Win 7 64bit.

Thanks in advance for your help.

Steve

[lotsaram]

I don't have an answer for your issue connecting to the server (it would help if you specified the security mode being used, I assume you are using CAM so mode 4 or 5). But in terms of cutting down on the overall processing time you should consider using Sheets.Copy to create a NEW workbook from the relevant worksheets of the master template and then saving and closing the new file leaving the master template open. Not having to continually open the master file over 200 iterations will save you considerable time, especially if the file is a decent size. You can also then easily avoid including macros and unnecessary sheets in the bursted report files.

[smorgan36]

@ lotsaram - Thanks for your response. Our seculity is CAM 5.
I will consider the sheet.copy approach. However, I do have to include a lot of macros with the workbook.

[tomok]

Is is taking a long time to open because you have auto-recalc on? There's no sense calcing the workbook until you have all the appropriate selections made.

[smorgan36]

Calculation is set to manual. Workbook is calculated inorder to populate with data from the cubes.

Thanks for your response.

[TJMurphy]

What form of Manual Calc do you have in your model? In Excel 2007, if you go to Excel options there is a tick box under manual calc that tells Excel to calc before saving. This triggers a really slow calc (I think it's doing all DBRWs as if they are DBRs. If you turn this box off on your master template does that help the speed?

When we're generating large numbers of templates we tend to make temporary folders on the local hard drive and run everything from there then copy the end result back to the server.