Page 1 of 1

Connect TM1 Perspectives(IntegratedSecurityMode 5) with CAMPassport in vba

Posted: Mon May 16, 2022 7:31 am
by kavitha2002
Hello Everyone,

I have tried to connect to TM1 Perspectives in IntegratedSecurityMode 5 with CAMPassport in excel vba automatically.

Previously with 'N_CONNECT_CAM' with n_connect_cam_bridge.dll worked well in excel 32 bit. In the current latest vesion of TM1 Pespective will support only 64 bit. Same dll is not supported in 64 bit. So I set the CAMPassport in vba and connect to TM1 Perspectives as a workaround.

The problem is its connected to TM1 Perspectives but ribbon is not activated.
Tm1-serverexplorer.png
Tm1-serverexplorer.png (11.19 KiB) Viewed 2963 times
Tm1-ribbon.png
Tm1-ribbon.png (18.11 KiB) Viewed 2963 times
Kindly provide your input on this.

Thanks

Re: Connect TM1 Perspectives(IntegratedSecurityMode 5) with CAMPassport in vba

Posted: Thu May 02, 2024 5:54 am
by danieljarolim
Just hit the same issue yesterday migrating reports to 64 bit Excel. The n_connect_cam_bridge.dll provided by IBM is 32 bit only and so is the VBA template they provided at the below links. I just updated the code and reimplemented the GetCamPassport function in VBA that was in n_connect_cam_bridge.dll

https://www.ibm.com/support/pages/uid/swg21959177 / https://www.ibm.com/support/pages/tm1-a ... de-4-and-5

If anyone is still using Perspectives in 64 bit Excel and needs this you can download the file from IBM above and do the following to the module:
  • Search Replace "Declare Function" with "Declare PtrSafe Function"
  • Search Replace "Declare Sub" with "Declare PtrSafe Sub"
  • Search Replace "As Long" "As LongLong" It will make a couple of incorrect changes
  • Change back one instance of "As LongLongPtr" to "As LongPrt"
  • Change back "Dim lLenB As LongLong" to "Dim lLenB As Long" or the ReDim in that function will cause type mismatch errors
  • Change back "Dim cSize As LongLong" to "Dim cSize As Long" and
  • Update next line to cast the returned value to Long so it looks like "cSize = CLng(2 * TM1ValStringWMaxSize(hUser, vString))"
  • Comment out the 2 lines containing n_connect_cam_bridge.dll one is a definition and the other loads the library. This is a 32 bit dll and won't work.
  • Replace "If rcApi = 0 Or rcCon = 0 Then" with "If rcApi = 0 Then" since the rcCon assignment from the missing library load has been commented out
  • Add the new GetCamPassport function. Copy it out of the second file in this gist.
  • Replace "sPassportLong = Trim(Left(StrConv(sPassportLong, vbFromUnicode), 255))" with "sPassportLong = Trim(Left(sPassportLong, 255))" The new GetCamPassport functions returns the passport non-unicode encoded.
  • Update TEST_N_CONNECT_CAM() with correct connection details, load perspectives, and run the sub to test everything works.
Or you can grab the full updated module here: https://gist.github.com/danieljarolim/7 ... 656fa3443a

The updated GetCamPassport function:

Code: Select all

Private Function GetCamPassport(ByVal camuri As String, ByVal servername As String, ByRef passport As String, ByVal Size As LongLong) As LongLong
    On Error GoTo ErrorHandler:
    
    Dim sResponseHeaders, sResponseText, sCamPassport, aData, sUrl, oMatch
    sUrl = StrConv(camuri, vbFromUnicode)
    
    With CreateObject("WinHttp.WinHttpRequest.5.1")
         .Open "GET", sUrl, False
         .setRequestHeader "Accept", "application/json"
         .setRequestHeader "Content-Type", "application/json; charset=utf-8"
         .SetAutoLogonPolicy (0) '(AutoLogonPolicy_Always) 'Connect using current user credentials
         .Send
         sResponseHeaders = .getAllResponseHeaders
         sResponseText = .responseText
    End With
    
    aData = Array()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = "^Set-Cookie: cam_passport=(\S*?);[\s\S]*?$"
        For Each oMatch In .Execute(sResponseHeaders)
            If oMatch.SubMatches.Count = 1 Then
                sCamPassport = oMatch.SubMatches(0)
            End If
        Next
    End With
    
    'passport = StrConv(sCamPassport, vbUnicode) ' This works here but results in the function returning a badly padded passport
    passport = sCamPassport
    GetCamPassport = 0

    Exit Function
    
ErrorHandler:
    GetCamPassport = 2
End Function

Re: Connect TM1 Perspectives(IntegratedSecurityMode 5) with CAMPassport in vba

Posted: Thu May 02, 2024 6:22 pm
by Wim Gielis
Very nice first post here 👍
Just a pity that the end of Perspectives was announced yesterday.