CAM authentication and Excel

[bagovino]

I'm running on 9.5.1 and according to the documentation N_CONNECT doesn't support CAM authentication. I know how to, and can, connect to TM1 using CAM authentication through the API.

Does anyone know if there is any way I can point the Excel Add-In to use this handle, or if there is an undocumented connection function I can call that supports CAM authentication?

[Mike Cowie]

Hi:

Bit of a rushed response here, but hopefully it will help... I don't know of an undocumented CAM connection VBA function, unfortunately.

There's no way to tell the TM1 Client to use a specific TM1 API session (or any other) handle. However, you can go in the other direction. From VBA you can get the TM1 Client's session handle and use it in place of the normal TM1SystemOpen functions, etc that you'd use in a standalone app.

Here's the somewhat undocumented call (TM1_API2HAN) to get that session handle in VBA:

Code: Select all

'TM1 helper function to get Excel TM1 API session handle
Public Function GetExcelSessionHandle() As Long
    GetExcelSessionHandle = Application.Run("TM1_API2HAN")
End Function

So, in theory you could get this handle, present a form to the user to prompt for user name & password, and use that with the CAM login TM1 API functions you already know how to use to make the connection. If you're successful, the next time you refresh Server Explorer you should show as being connected, since you're sharing its handle and it will see what you see from the functions you called in VBA (that you're connected to a server now).

Now, one big word of caution with this: you are just borrowing the TM1 client's session handle, which means you have to be careful with it and not "break" it. You can cause all kinds of problems if you do things like call TM1SystemClose from VBA with the TM1 Client's session handle or start messing with things like its value pools, etc. The TM1 client won't know you've done this and you are highly likely to see Excel crashes - that may have been obvious, but it's worth noting.

Hope that helps.

Regards,
Mike

[bagovino]

It worked perfectly, thanks!