How to get current TM1 server name in excel?

[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

[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.

[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

[wang_chris]

Get it, thank you for your suggestion.

We may attemp to use this VBA program in the excel file.

Regards,
Chris