TM1 Forum

Posted: **Fri Sep 27, 2013 11:41 am**

Hi all

I am using VBA to DBS and DBRW but was wondering what the commands are for

1) Add User
application.run(AddClient, Tm1server,Name)

2) Delete User
application.run(deleteClient, Tm1server,Name)

3) refresh Security
application.run(refreshSecurity, Tm1server)

these obviously dont work i was expecting something similar I have TI's im linking to my spreadsheet just wondering if there was a VBA alternative

once again thanks all

Posted: **Fri Sep 27, 2013 12:39 pm**

The TM1 documentation lists all the functions that are available to you via the Perspectives add-in. These are the only functions you can call via the Application.Run construct in VBA. There are no functions for what you have listed below. If you want to do any of that via Excel your only option is to add an Action button to the sheet or have your VBA shell out to a command line and execute a TI via RunTI.exe.

Posted: **Fri Sep 27, 2013 1:13 pm**

Hi Tomok

I thought as much, and have checked the system information and like you said couldnt find anything for these actions, but thought would also ask as there have been countless items been missed of documentation that you guys know

thanks anyway

Posted: **Fri Sep 27, 2013 3:16 pm**

Darkhorse wrote:there have been countless items been missed of documentation that you guys know

While the documentation is not perfect, it does not mean that you should doubt about everything in the documentation. Use your common sense.

Posted: **Fri Sep 27, 2013 8:29 pm**

tomok wrote:The TM1 documentation lists all the functions that are available to you via the Perspectives add-in. These are the only functions you can call via the Application.Run construct in VBA. There are no functions for what you have listed below. If you want to do any of that via Excel your only option is to add an Action button to the sheet or have your VBA shell out to a command line and execute a TI via RunTI.exe.

Not the only option; it can be done through VBA API code as well. But I agree that in most cases using the TI approach is the simplest and cleanest.

Posted: **Sat Sep 28, 2013 7:47 pm**

aha so there is a way via apis!

can you divuldge any further information kirk or some pointers on where i can find a sample?

Posted: **Sat Sep 28, 2013 10:08 pm**

Darkhorse wrote:aha so there is a way via apis!

can you divuldge any further information kirk or some pointers on where i can find a sample?

All you need is in the API manual, but I would definitely discourage you from doing it. The action button / TI method is going to take you a lot less time to implement. The only reason for using the API is if it was an integral part of a much larger project which also requires API code. As I've said many times before the API is powerful but given the effort that you have to put into coding it it should always be a last resort, not a first one.

Posted: **Sun Sep 29, 2013 10:14 am**

Hi Alan

Im building a big project that already uses some Api's already

1) Get Server List
2) Find Connections
3) RunTi()

Im trying to do away with some really basic TI's like the Add/Delete/refresh securities i have some complex TI's that I can't use Apis for.

Thanks Alan I will have a look in the manual abit more

Thanks guys, Always amazed at this forum

Posted: **Mon Sep 30, 2013 5:21 am**

Hi Darkhorse,

You may try the code below. You need to use TM1ValStringEncrypt for password.

Code: Select all

Sub addclient()
'Declare Function TM1ClientAdd Lib "tm1api.dll" (ByVal hPool As Long, ByVal
hServer As Long, ByVal sClientName As Long) As Long
Dim SessionHandle As Long, ValPoolHandle As Long
Dim ServerHandle As Long, ProcessHandle As Long

hUser = 0
'Clear Memory
Application&#46;Run ("M_Clear")
hUser = TM1_API2HAN
ServerName = "Tm1_Server1"
NewClient = "Testuser4"
PasswordNew = "1234"

'Get handles
SessionHandle = GetExcelSessionHandle()
If SessionHandle = 0 Then
Exit Sub
End If

'Get handle to server; check if connected
ValPoolHandle = TM1ValPoolCreate(SessionHandle)
ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
If ServerHandle = 0 Then
Exit Sub
End If

Result = TM1ClientAdd(ValPoolHandle, ServerHandle,
TM1ValString(ValPoolHandle, NewClient, 0))
'Check if added
result2 = TM1ValBoolGet(hUser, Result)
If result2 <> 1 Then
MsgBox "Not Possible to create user", vbCritical, "Error"
Exit Sub
End If

'Get handle for user
Result3 = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle,
TM1ServerClients, TM1ValString(ValPoolHandle, NewClient, 0))
Result4 = TM1ClientPasswordAssign(ValPoolHandle, Result3,
TM1ValStringEncrypt(ValPoolHandle, PasswordNew, 0))
'Clear Memory
Application&#46;Run ("M_Clear")
End Sub

Posted: **Mon Sep 30, 2013 9:14 am**

For those that might want these Api calls

thought worth while sharing and to also get someones bug fixing if needed

sorry this a little dirty (no error handling)

Declare these functions and add this sub

Code: Select all

Declare Function TM1ClientAdd Lib "tm1api.dll" (ByVal hPool As Long, ByVal hServer As Long, ByVal sClientName As Long) As Long
Declare Function TM1ServerSecurityRefresh Lib "tm1api.dll" (ByVal hPool As Long, ByVal hServer As Long) As Long
Declare Function TM1ClientPasswordAssign Lib "tm1api.dll" (ByVal hPool As Long, ByVal hClient As Long, ByVal sPassword As Long) As Long

Public Function GetExcelSessionHandle() As Long
    GetExcelSessionHandle = Application.Run("TM1_API2HAN")
End Function

Add a Client and add a password

Code: Select all

 
Sub AddClientPasswAPi()
Dim SessionHandle As Long, ValPoolHandle As Long, ServerHandle As Long
hUser = 0
    'Clear Memory
    Application.Run ("M_Clear")
    hUser = TM1_API2HAN
    ServerName = "Tm1_Server1"
    NewClient = "Testuser4"
    PasswordNew = "1234"

    'Get handles
    SessionHandle = GetExcelSessionHandle()
    If SessionHandle = 0 Then
        Exit Sub
    End If

    'Get handle to server; check if connected
    ValPoolHandle = TM1ValPoolCreate(SessionHandle)
    ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
    If ServerHandle = 0 Then
        Exit Sub
    End If

    Result = TM1ClientAdd(ValPoolHandle, ServerHandle, TM1ValString(ValPoolHandle, NewClient, 0))
'Check if added
    result2 = TM1ValBoolGet(hUser, Result)
    If result2 <> 1 Then
        MsgBox "Not Possible to create user", vbCritical, "Error"
        Exit Sub
    End If

    'Get handle for user
    Result3 = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle, TM1ServerClients, TM1ValString(ValPoolHandle, NewClient, 0))
    Result4 = TM1ClientPasswordAssign(ValPoolHandle, Result3, TM1ValString(ValPoolHandle, PasswordNew, 0))
    'Clear Memory
    Application.Run ("M_Clear")
End Sub

Password Reset

Code: Select all

 
Sub PasswordResetAPi()
Dim SessionHandle As Long, ValPoolHandle As Long, ServerHandle As Long,
hUser = 0
    'Clear Memory
    Application.Run ("M_Clear")
    hUser = TM1_API2HAN
    ServerName = "Tm1_Server1"
    NewClient = "Testuser4"
    PasswordNew = "1234"

    'Get handles
    SessionHandle = GetExcelSessionHandle()
    If SessionHandle = 0 Then
        Exit Sub
    End If

    'Get handle to server; check if connected
    ValPoolHandle = TM1ValPoolCreate(SessionHandle)
    ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
    If ServerHandle = 0 Then
        Exit Sub
    End If


    'Get handle for user
    Result = TM1ObjectListHandleByNameGet(ValPoolHandle, ServerHandle, TM1ServerClients, TM1ValString(ValPoolHandle, NewClient, 0))
    Result4 = TM1ClientPasswordAssign(ValPoolHandle, Result, TM1ValString(ValPoolHandle, PasswordNew, 0))
    'Clear Memory
    Application.Run ("M_Clear")
End Sub

Server Security refresh

Code: Select all

 
Sub RefreshsecurAPi()
Dim SessionHandle As Long, ValPoolHandle As Long, ServerHandle As Long, 

    'Clear Memory
    Application.Run ("M_Clear")
 
    ServerName = "Tm1_Server1"

    'Get handles
    SessionHandle = GetExcelSessionHandle()
    If SessionHandle = 0 Then
        Exit Sub
    End If

    'Get handle to server; check if connected
    ValPoolHandle = TM1ValPoolCreate(SessionHandle)
    ServerHandle = TM1SystemServerHandle(SessionHandle, ServerName)
    If ServerHandle = 0 Then
        Exit Sub
    End If


    'Get handle for user
     Result = TM1ServerSecurityRefresh(ValPoolHandle, ServerHandle)
       'Clear Memory
    Application.Run ("M_Clear")
End Sub

TM1 Forum

VBA AddUser/Group Refresh security

VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security

Re: VBA AddUser/Group Refresh security