VBA AddUser/Group Refresh security
-
- 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
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
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
-
- 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
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.
-
- 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
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
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
-
- MVP
- Posts: 3103
- 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
While the documentation is not perfect, it does not mean that you should doubt about everything in the documentation. Use your common sense.Darkhorse wrote:there have been countless items been missed of documentation that you guys know
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
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
- 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
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.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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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?
can you divuldge any further information kirk or some pointers on where i can find a sample?
- 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
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.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?
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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
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
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
-
- 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
Hi Darkhorse,
You may try the code below. You need to use TM1ValStringEncrypt for password.
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.Run ("M_Clear")
End Sub
Ricky Marwan
-
- 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
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
Add a Client and add a password
Password Reset
Server Security refresh
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
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
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
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