Skip to Content
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

 

1 Comment
You must be Logged on to comment or reply to a post.