Technical Articles
VB script/SAP GUI script for mass role assignment –
Along with SECATT and LSMW SAP has provided one more tool VB script through which we can perform mass activities, VB script is easy to use(record and playback) add the fixed code part in script and just the change path of excel where it will be pickup details for processing
once the final script is ready you can utilize it as per your requirement no need to recreate it again.
please find below steps to create VB script
Prerequisite –
Scripting parameter needs to be enabled -> We can enable parameter through RZ11 tcode make the Value TRUE current value will be False
parameter name – sapgui/user_scripting
once the parameter is enabled you will find option Script recording and Playback as below
We will do scripting for role assignment through SU10
1. Go to Su10 screen..
2. click the customize layout icon and select the Script Recording and playback you will Record and playback window…
3. Green button is for running script and Red button is for recording the script. More option will allow to you choose the path of file where you want to save script (Which we are going to record)
4. Start recording by clicking red button(It will get yellow). It will start recording all activities you perform once u have done with required action stop the script by clicking same button(Yellow )
Follow the below steps for recording script execute the /nSU10 enter the user id got to edit mode assign the role and save it and again execute /nSu10 do that it will go to initial screen and stop the script (role assignment activity has been recorded)
5. you will find one VBS file in given path (provided path in step 3), open the same in notepad we have to edit below file with fixed code(give the path of excel from where it will take data such as user id, role for performing mass activity and Loop )
Fixed code – paste this code after End if statement
Dim cnnExcel, rstExcel
Dim strExcelFile
strExcelFile = "C:\Users\Documents\My Received Files\VB\Book1.xls"
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic
session.findById("wnd[0]").maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"
============================================================================
Dim cnnExcel, rstExcel
Dim strExcelFile
strExcelFile = “C:\Users\Documents\My Received Files\VB\Book1.xls”
Set cnnExcel = CreateObject(“ADODB.Connection”)
cnnExcel.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & strExcelFile & “;” & _
“Extended Properties=””Excel 8.0;HDR=NO;”””
Set rstExcel = CreateObject(“ADODB.Recordset”)
rstExcel.Open “Select * from [Sheet1$A1:G1000]”, cnnExcel, adOpenStatic
session.findById(“wnd[0]”).maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = “LAST”
============================================================================
After adding first part of code file will look like below (path of excel from where it will take data such as user id, role for performing mass activity)
Fixed code description (First part of code) – It gives path (Excel file must be in 97 – 2003 format) of excel file from where it will take data user id and role, In that excel we will maintained the userid and roles need to be assigned. In first column we will add LAST text so that script will get stop once it reached to LAST
Excel file(97 – 2003 format) should be in below format (First column User id and second column Roles)
Middle section will be our actual recording where we have to make below changes so that it will fetch the data from specific column of excel
rstExcel.Fields(0).Value – Refers to First column of excel(User id)(Replace “TESTUSER” with rstEXCel.Fields(0).Value)
rstExcel.Fields(1).Value – Refers to Second column of excel(Role)(Replace “Z_role_1” with rstEXCel.Fields(1).Value)
Second part of code you will add in end of file as below –
rstExcel.movenext
If rstExcel.EOF Then Exit Do
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
============================================================================
rstExcel.movenext
If rstExcel.EOF Then Exit Do
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
============================================================================
Before editing Script –
If Not IsObject(application) Then
Set SapGuiAuto = GetObject(“SAPGUI”)
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, “on”
WScript.ConnectObject application, “on”
End If
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “SU10”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]”).text = “TESTUSER”
session.findById(“wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]”).caretPosition = 7
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/tbar[1]/btn[18]”).press
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG”).select
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).modifyCell 0,”AGR_NAME”,”Z_role_1″
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).currentCellColumn = “AGR_NAME”
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).pressEnter
session.findById(“wnd[0]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nsu10”
session.findById(“wnd[0]”).sendVKey 0
=========================================================================
After editing Script will be as below (highlighted in bold is additional code we have added in script) you can copy the below entire code paste in notepad save it in VBS format (.VBS) just change the path of excel file from where it will collect data.and you can use script
Please note I have recorded below script in ECC 6 so if you are having same version you can use the below script as it is but if you are having other version of ECC system you will have to record it and then edit as per below fixed code (Fixed code will remain the same whatever the version of your system will be)
If Not IsObject(application) Then
Set SapGuiAuto = GetObject(“SAPGUI”)
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, “on”
WScript.ConnectObject application, “on”
End If
Dim cnnExcel, rstExcel
Dim strExcelFile
strExcelFile = “C:\Users\Documents\VB Scripts\Role.xls”
Set cnnExcel = CreateObject(“ADODB.Connection”)
cnnExcel.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & strExcelFile & “;” & _
“Extended Properties=””Excel 8.0;HDR=NO;”””
Set rstExcel = CreateObject(“ADODB.Recordset”)
rstExcel.Open “Select * from [Sheet1$A1:G1000]”, cnnExcel, adOpenStatic
session.findById(“wnd[0]”).maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = “LAST”
session.findById(“wnd[0]”).maximize
session.findById(“wnd[0]/tbar[0]/okcd”).text = “SU10”
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]”).text = rstExcel.Fields(0).Value
session.findById(“wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]”).caretPosition = 7
session.findById(“wnd[0]”).sendVKey 0
session.findById(“wnd[0]/tbar[1]/btn[18]”).press
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG”).select
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).modifyCell 0,”AGR_NAME”,rstExcel.Fields(1).Value
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).currentCellColumn = “AGR_NAME”
session.findById(“wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell”).pressEnter
session.findById(“wnd[0]/tbar[0]/btn[11]”).press
session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nsu10”
session.findById(“wnd[0]”).sendVKey 0
rstExcel.movenext
If rstExcel.EOF Then Exit Do
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
===========================================================================
Script is ready.. follow the below steps for running the VB script. As script has been recorded through SU10.
Login to SAP system click on customize layout select Script recording and Playback and click on Play button
Click on green button it will ask for file path of VBS file that u want to run select it .
It will ask you popup to allow it to run, once you click ok script will get executed in foreground., you can see roles are getting assigned to users.
you can use VB script for other mass activities as well such user creation/ updating email id id/ Last name/ First name, just record the activity and apply the code.
Hope this helps, Looking forward to your feedback/comments.
Regards,
Rakesh Kirve
Thanks for sharing this!
Hi Rakesh,
I tried your way to grant mass role assignent using SAP script but nothing happen.
Please advise if you have updated sap script.
Note: i'm using CUA and not directly change in ECC.
Thanks,
Nizar
Hi,
As you are using CUA system please record a script through SU10 tcode, after recording you will have three fields one is user id second will system and another for role accordingly in excel you have to maintain three columns
rstExcel.Fields(0).Value - userid
rstExcel.Fields(1).Value - system
rstExcel.Fields(2).Value - Role
also make sure that you have execute the script on the same screen where you have done recording.
For example: if you recorded script after executing SU10 tcode then while executing script first you go to SU10 screen first and then select the script you have to execute.(All it depends on how you have recorded the script)
I will check script for CUA system and will let you know.
Regards,
Rakesh Kirve
Hi,
Please find below script for CUA system role assignment
First execute the SU10 tcode and then execute the script, please change the path of excel file and excel file(97-2003 format) should have data in below format
rstExcel.Fields(0).Value – userid (1st cloum)
rstExcel.Fields(1).Value – system (2nd cloum)
rstExcel.Fields(2).Value – Role (3rd Colum)
============================================================================
copy paste the below code in notepad and save file in .VBS
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
strExcelFile = "C:\Users\Documents\VB Scripts\test_3.xls"
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic
session.findById("wnd[0]").maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").text = rstExcel.Fields(0).Value
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").caretPosition = 9
session.findById("wnd[0]/tbar[1]/btn[18]").press
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"SUBSYSTEM", rstExcel.Fields(1).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"AGR_NAME", rstExcel.Fields(2).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").currentCellColumn = "AGR_NAME"
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/okcd").text = "/nSu10"
session.findById("wnd[0]").sendVKey 0
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
Hi,
I recorded new script in CUA and replace with script below but still nothing happen.
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
strExcelFile = “H:\MoveToCognizant\Nizar Backup\My Work Tools\02. Profit Center Assignment\SAP Script - Mass Role Assignment.xls”
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic
session.findById("wnd[0]").maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").text = rstExcel.Fields(0).Value
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").caretPosition = 9
session.findById("wnd[0]/tbar[1]/btn[18]").press
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"SUBSYSTEM", rstExcel.Fields(1).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"AGR_NAME", rstExcel.Fields(2).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").currentCellColumn = "AGR_NAME"
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/okcd").text = "/nSu10"
session.findById("wnd[0]").sendVKey 0
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
Hi,
please do new recording(Fixed code will remain the same) and make sure that you run VB script on the same screen where you have done recording, in your case if you have recorded VB script through SU10 then first execute the SU10 and then try to run the script else it will not run.
If you try to run script just after login to system it will not work.
Hi Rakesh,
I'm done new recording and the script run but only take 1st row on excel file and keep repeating the role assignment without stopping the task.
Below is the script.
===========================================
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
strExcelFile = "H:\MoveToCognizant\Nizar Backup\My Work Tools\02. Profit Center Assignment\Script for Mass Role Assignment\SAP Script - Mass Role Assignment.xls"
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic
session.findById("wnd[0]").maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"
session.findById("wnd[0]").maximize
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").text = rstExcel.Fields(0).Value
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").caretPosition = 9
session.findById("wnd[0]/tbar[1]/btn[18]").press
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"SUBSYSTEM", rstExcel.Fields(1).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"AGR_NAME", rstExcel.Fields(2).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").currentCellColumn = "AGR_NAME"
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/okcd").text = "/nSu10"
session.findById("wnd[0]").sendVKey 0
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
hi ,
i am getting below error , can you please help me.
i am using ecc 6 only
Error screen
Hi,
I recorded in SU10 & prepared below script, but it is showing below error: "
"External table is not in the expected format."
Is it due to excel format as you have created the script using excel 2007 & I am using office 365.
Below is my script for reference, kindly help.
If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
Dim cnnExcel, rstExcel
Dim strExcelFile
strExcelFile = "C:\Users\kchakrabor041\Documents\Mass User Creation\Test_Mass Role.xls"
Set cnnExcel = CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO;"""
Set rstExcel = CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic
session.findById("wnd[0]").maximize
rstExcel.movenext
do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "su10"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").text = rstExcel.Fields(0).Value
session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").caretPosition = 5
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[18]").press
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0,"AGR_NAME",rstExcel.Fields(1).Value
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").currentCellColumn = "AGR_NAME"
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").pressEnter
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/chkGS_NODE_REFUSER_X-REFUSER").selected = true
session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/chkGS_NODE_REFUSER_X-REFUSER").setFocus
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
rstExcel.movenext
If rstExcel.EOF Then Exit Do
LOOP
rstExcel.Close
Set rstExcel = Nothing
cnnExcel.Close
Set cnnExcel = Nothing
Hi,
Have you resolved this issue, Getting same error ?
Did you try saving the Excel file in an older format?