Page 1 of 1
How to get current TM1 server name in excel?
Posted: Fri Nov 28, 2014 3:49 pm
by wang_chris
I have some excel sheets to access TM1 server, in the control sheet, I list TM1 server name.
But once I wish to connect this excel file to another server, I had to change server name manually. How can I let excel to get current connected TM1 server name? I can not find such function in reference book, there is only a function called TM1User() to get current user.
Regards,
Chris
Re: How to get current TM1 server name in excel?
Posted: Fri Nov 28, 2014 10:01 pm
by lotsaram
You can't really do this because the client can be connected to multiple servers. It would be a useful feature to be able to retrieve a LIST of connected servers, this would be a useful enhancement request.
What you can do if you know a list of possible server names is to pass these into an array of TM1User functions. Where TM1User returns a non-empty string then that indicates a server that is connected.
Re: How to get current TM1 server name in excel?
Posted: Sat Nov 29, 2014 2:40 am
by Headon01
Lotsaram is right. We have 14 production servers and I am usually connected to 3 at any time. Having our Excel worksheets changing the server dynamically, based on the first connected server, would be chaos.
That said, if your users are disciplined enough to disconnect and connect to just one server (Lord knows I'm not) you could use the TM1 API. Just import the "Tm1api.bas" file, the one included in the TM1 Install , into your VBAProject and put code like the following into a Module:
Code: Select all
Private colServers As Collection
Sub Main()
GetConnectedServers
' Check to see if any servers were connected
If colServers.Count > 0 Then
Debug.Print colServers(1)
End If
End Sub
Public Sub GetConnectedServers()
Dim nI As Integer
Dim sServerName As String
Set colServers = New Collection
' Grab the user System Handle (Yeah I know it's an undocumented
' function and you should use it at your own risk)
hUser = Application.Run("TM1_API2HAN")
' Loop through all the available servers
For nI = 1 To TM1SystemServerNof(hUser)
sServerName = String(100, " ")
TM1SystemServerName_VB hUser, nI, sServerName, 100
' Get rid of the extra spaces
sServerName = Trim(sServerName)
' See if a Server Handle is returned
If Not TM1SystemServerHandle(hUser, sServerName) = 0 Then
colServers.Add sServerName
End If
Next nI
End Sub
Now that I think of it, I could pop up a pick list if there is more than one server connected. I just might make my Excel workbooks more dynamic.
Micheal
Re: How to get current TM1 server name in excel?
Posted: Mon Dec 01, 2014 7:18 am
by wang_chris
Get it, thank you for your suggestion.
We may attemp to use this VBA program in the excel file.
Regards,
Chris