Connect with TM1p.xla & VBA

[HighKeys]

Hello,

i'm new with TM1 and i try to get a auto connection in some sheets for TM1.

So i added in the Open Event a check to find out if the user already loaded the plugin, if not then the addon should be loaded.

After that i call the COnnection call from the dll, now you can select a server and press connect.

2 Issues at the moment:

1.) it loads the TM1 Ribbon Addon everytime, even if you already load it.

2.) the Ribbon control is after the connection still disabled like i have never connected to a server



My Open Event:

Code: Select all

Private Sub workbook_open()
    
    Application.Calculation = xlCalculationManual
    
    'load TM1 add-in if the TM1 menu do not exist
     If Not (bCommandBarExists("TM&1")) Then
     Workbooks.Open ("C:\Program Files\ibm\cognos\tm1_64\bin\tm1p.xla")
     End If
    
    'hide TM1 toolbars if not in correct team // add MAD to check users
     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
     Application.Run ("NET_CONN")
    
    'msg = Run("N_CONNECT", "spatpss023.at.dc.eb-grp.net", "Planung")
     If msg <> "" Then
     MsgBox msg
     End If
    
    Application.Run "TM1RECALC"

End Sub

------

Code: Select all

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

Thanks for your Help
BR

[orlando]

Hi,

if i have to connect via VBA (what i try to avoid) i use the VBA API:

https://www.bihints.com/tm1_sdk

and this sub

######################

Sub con
hUser = TM1SystemOpen()
AdminHostName = ThisWorkbook.Worksheets("Adr").Range("_adm_1")
ServerName = ThisWorkbook.Worksheets("Adr").Range("_sr_1")
ServerName = Left(ServerName, Len(ServerName) - 1)

'AdminHostServer angeben
Call TM1SystemAdminHostSet(hUser, AdminHostName)

ServerCount = TM1SystemServerNof(hUser)

'Einen ValuePool-Handle erstellen
hPool = TM1ValPoolCreate(hUser)

MsgBox AdminHostName + " " + ServerName + " " + UserName + " " + Password
vServerName = TM1ValString(hPool, Trim(ServerName), 0)
vClientName = TM1ValString(hPool, Trim(UserName), 0)
vClientPassword = TM1ValString(hPool, Trim(Password), 0)

'Am TM1-Server anmelden

hServer = TM1SystemServerConnect(hPool, vServerName, vClientName, vClientPassword)

End Sub

###########################

maybe this will help

[HighKeys]

Hello,

thanks for your help!

But could you help me also how i can check if the Ribbon is loaded or not?

I Tried this

Code: Select all

Function bCommandBarExists(sCmdBarName As String) As Boolean

Dim bCbExists As Boolean

Dim cb As CommandBar

bCbExists = False
 
 For Each cb In Application.CommandBars
 
 Debug.Print cb.Name
 Debug.Print cb.Parent.Name
 If cb.Name = sCmdBarName Then
 bCbExists = True
 Exit For
 
 
 End If
 Next

bCommandBarExists = bCbExists

End Function

And i Search for "TM&1" but i can't find the ribbon, even when its loaded.

Any idea whats wrong?

[orlando]

Hello,

thanks for your help!

But could you help me also how i can check if the Ribbon is loaded or not?

Hi,

i would check, if the XLA is loaded.
Somethink like that schould help
http://www.office-loesung.de/ftopic30774_0_0_asc.php

Best regards
orlando

[HighKeys]

Hi!

works perfect, thank you so much!!

So one last thing to do, how i could check if TM1 is already connected to a Server?

Thanks!

[orlando]

So one last thing to do, how i could check if TM1 is already connected to a Server?

i would play with the TM1User in Excel

https://www.ibm.com/support/knowledgece ... 1user.html

if empty - not connected. If not empty - connected.

best regards
orlando

[HighKeys]

Hey Orlando!

Thanks for your help! Works great now!


if someone is interested, here is the code:

Code: Select all

Private Sub workbook_open()
     
    Application.Calculation = xlCalculationManual
     
Dim oAddIN As AddIn, vAd As String
 
    On Error GoTo Fehler
    For Each oAddIN In Application.AddIns
    If oAddIN.Name = "tm1p.xla" Then
    If oAddIN.Installed = False Then vAd = oAddIN.FullName
    Exit For
    End If
     
    Next oAddIN
    If Len(vAd) <> 0 Then Application.AddIns.Add(vAd).Installed = True
         
        GoTo Ende
 
Fehler:
    Workbooks.Open ("C:\Program Files\ibm\cognos\tm1_64\bin\tm1p.xla")
      
Ende:
    If Application.Run("TM1User", "SERVERNAME") = "" Then
        Application.Run ("NET_CONN")
    End If
     
    Do While Application.Run("TM1User", "SERVERNAME") = ""
        DoEvents
    Loop
     
    Application.Run "TM1RECALC"
 
End Sub

BR
HighKeys