TM1 and Excel in 1 click

the following code will:
- load TM1 add-in
- hide the TM1 toolbars (most people do not need spreading, developer.. tools)
- log you on TM1
- open the Server Explorer
- expand the applications and cubes
so you are just 1 click away from accessing your TM1 data :)

Replace the "\\path\to\tm1p.xla","server","user" and "password" strings to your own settings.

----THIS WORKBOOK------------------
Private Sub workbook_open()

'load TM1 add-in if the TM1 menu do not exist
If Not (bCommandBarExists("TM&1")) Then
Workbooks.Open ("\\path\to\tm1p.xla")
End If

'hide TM1 toolbars
On Error Resume Next
With Application
.CommandBars("TM1 Servers").Visible = False
.CommandBars("TM1 Developer").Visible = False
.CommandBars("TM1 Spreading").Visible = False
.CommandBars("TM1 Standard").Visible = False
End With
On Error GoTo 0

msg = Run("n_connect", "server", "user", "password")
If msg <> "" Then
MsgBox msg
End If

Application.Run "TM1RECALC"
End Sub

-----MODULE 1----------------------
Function bCommandBarExists(sCmdBarName As String) As Boolean

Dim bCbExists As Boolean

Dim cb As CommandBar

bCbExists = False
For Each cb In Application.CommandBars
If cb.name = sCmdBarName Then
bCbExists = True
Exit For
End If
Next

bCommandBarExists = bCbExists

End Function

Sub Open_SE()

Application.Run "TM1StartOrionWithAutomation"

'wait for Server Explorer to open
Application.Wait Now + TimeValue("00:00:05")
'expand Applications
SendKeys "{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}"
'jump to cubes and expand
SendKeys "{C}{RIGHT}"
End Sub

Bookmark/Search this post with:

| | | | | | | |