A tip to avoid disconnecting from TM1 using excel

[macsir]

I think most people like me has some situation to disconnect from TM1 using excel by accident. You have lots of excel opened and sliced/snapshot workbooks. It is very hard to tell which one is the real excel that is connecting to TM1. What I do is to put a piece macro code in one excel file, such as TM1 connection.xlsm. Each time I would open this file to connect TM1. Therefore, the following code would remind me if I really want to disconnect from TM1. Hope it can help others.

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Msg = "Do you want to disconnect from TM1 servers?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNo)
        Select Case Ans
            Case vbYes
                Application.Quit
            Case vbNo
                Cancel = True
                Exit Sub
          End Select
End Sub

[macsir]

BTW, you need to put this code into "ThisWorkbook" object to make it work.

[macsir]

BTW2, to make it work, you need to turn off tm1p.xla auto loading in excel and use this VBA code to load it manually

Code: Select all

Private Sub workbook_open()

Workbooks.Open ("C:\Program Files (x86)\Cognos\TM1\bin\tm1p.xla")

End Sub

[macsir]

Another interesting code from internet

Code: Select all

'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