Connect with TM1p.xla & VBA

Post Reply
HighKeys
Posts: 2
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: v10
Excel Version: Plus 2013

Connect with TM1p.xla & VBA

Post by HighKeys » Tue Aug 13, 2019 9:31 am

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

User avatar
orlando
Posts: 77
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: Connect with TM1p.xla & VBA

Post by orlando » Tue Aug 13, 2019 1:45 pm

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
Posts: 2
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: v10
Excel Version: Plus 2013

Re: Connect with TM1p.xla & VBA

Post by HighKeys » Fri Aug 16, 2019 8:25 am

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?

Post Reply