VBA AddUser/Group Refresh security

Post Reply
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

VBA AddUser/Group Refresh security

Post by Darkhorse »

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
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: VBA AddUser/Group Refresh security

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: VBA AddUser/Group Refresh security

Post by Darkhorse »

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 :D

thanks anyway
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: VBA AddUser/Group Refresh security

Post by Wim Gielis »

Darkhorse wrote:there have been countless items been missed of documentation that you guys know :D
While the documentation is not perfect, it does not mean that you should doubt about everything in the documentation. Use your common sense.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: VBA AddUser/Group Refresh security

Post by Alan Kirk »

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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: VBA AddUser/Group Refresh security

Post by Darkhorse »

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?
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: VBA AddUser/Group Refresh security

Post by Alan Kirk »

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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: VBA AddUser/Group Refresh security

Post by Darkhorse »

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
Ricky Marwan
Posts: 32
Joined: Tue Aug 14, 2012 7:47 am
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: VBA AddUser/Group Refresh security

Post by Ricky Marwan »

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.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
Ricky Marwan
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: VBA AddUser/Group Refresh security

Post by Darkhorse »

For those that might want these Api calls

thought worth while sharing and to also get someones bug fixing if needed :D
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
Post Reply