VBS Script to enter data by scrolling the label.

[khagendra_padhy]

Hi Experts,

A newbie here.

I am writing a script to enter multiple roles in the user's roles tab through script. I am able to achieve it partially but it stops when it comes to scroll to the next page & take the roles from excel sheet.

Below is my code, please take a look & advise to achieve this.

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 ExcelApp, ExcelWorkbook, ExcelSheet, Row ,Col

Set ExcelApp=CreateObject("Excel.Application")

Set ExcelWorkbook=ExcelApp.Workbooks.Open("C:\Users\KH269140\Desktop\ie_data.xls")

Set ExcelSheet=ExcelWorkbook.WorkSheets(2)

Row=1

Col=0

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").text = "/nsu01"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtUSR02-BNAME").text = "test"

session.findById("wnd[0]/usr/ctxtUSR02-BNAME").caretPosition = 4

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[1]/btn[18]").press

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select

Do Until ExcelSheet.Cells(Row,1).Value=""

MsgBox "Value:" &Col

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR/ctxtUSAGR-AGR_NAME[1,"& Col &"]").text = ExcelSheet.Cells(Row,1).value

session.findById("wnd[0]").sendVKey 0

Col=Col+1

if Col>12 Then

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = 12

Col=1

End if

Row=Row+1

Loop

session.findById("wnd[0]").sendVKey 11

ExcelApp.Quit

Set ExcelApp=Nothing

Set ExcelWorkbook=Nothing

Set ExcelSheet=Nothing

Set ExcelSheet1=Nothing

Any help will be great.

Thanks,

Khagendra

[script_man]

Hi Khagendra,

You can try the following:

e.g.

. . .

Do Until ExcelSheet.Cells(Row,1).Value=""

MsgBox "Value:" &Col

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR/ctxtUSAGR-AGR_NAME[1,"& Col &"]").text = ExcelSheet.Cells(Row,1).value

session.findById("wnd[0]").sendVKey 0

Col=Col+1

'if Col>12 Then

'session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = 12

'Col=1

'End if

Row=Row+1

on error resume next

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = Col

if err.number <> 0 then exit do

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").getAbsoluteRow(Col).selected = true

on error goto 0

Loop

on error goto 0

. . .

Regards,

ScriptMan

[khagendra_padhy]

Hi Script Man,

Thanks for your reply. I inserted your code but still not able to complete the activity. It's ending with an error  "The method got an invalid argument".

But one thing i need to update on the above script when i am putting the exact number of rows available in one screen than it's working fine & it's inserting the roles from the excel sheet.

Thanks,

Khagendra

[script_man]

Hi Khagendra,

If I don't know how the script looks after changing and which command is causing the error, I can not help you.

Regards,

ScriptMan

[khagendra_padhy]

Hi Script Man,

Thanks for the reply.

After changing the script looks like as below

....

Do Until ExcelSheet.Cells(Row,1).Value=""

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR/ctxtUSAGR-AGR_NAME[1,"& Col &"]").text = ExcelSheet.Cells(Row,1).value

session.findById("wnd[0]").sendVKey 0

Col=Col+1

'if Col>12 Then

'session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = 12

'Col=1

'End if

Row=Row+1

on error resume next

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = Col

if err.number <> 0 then exit do

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").getAbsoluteRow(Col).selected = true

on error goto 0

Loop

on error goto 0

session.findById("wnd[0]").sendVKey 11

ExcelApp.Quit

Set ExcelApp=Nothing

Set ExcelWorkbook=Nothing

Set ExcelSheet=Nothing

Set ExcelSheet1=Nothing

On executing this script i am now getting another error like "The control could not be found by id."

Please help on this.

Thanks,

Khagendra

[script_man]

OK, Let us look at. About the reported line number you have to find out which command caused the error.

Then enter this command known here.

Regards,

ScriptMan

[khagendra_padhy]

Hi Script Man,

I guess the error is due to , when scroll bar scrolls to next page the variable SAP starts assuming as ..ctxtUSAGR-AGR_NAME[1,1]  but the value of variable Col is 13. For this reason it might be through error like "The control could not be found by id.".

I think one solution for this is to count number of rows there in one page & reset it to 1 when it scrolls down to next page.

Please suggest whether i am right or wrong.

Thanks,

Khagendra

[script_man]

Hi Khagendra,

unfortunately, I have no access to ta = su01. But you can try the following:

. . .

Do Until ExcelSheet.Cells(Row,1).Value=""

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR/ctxtUSAGR-AGR_NAME[1,0]").text = ExcelSheet.Cells(Row,1).value

session.findById("wnd[0]").sendVKey 0

on error resume next

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = Row

if err.number <> 0 then exit do

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").getAbsoluteRow(Row).selected = true

on error goto 0

Row=Row+1

Loop

on error goto 0

session.findById("wnd[0]").sendVKey 11

ExcelApp.Quit

Set ExcelApp=Nothing

Set ExcelWorkbook=Nothing

Set ExcelSheet=Nothing

Set ExcelSheet1=Nothing

Regards,

ScriptMan

[khagendra_padhy]

Hi Script Man,

Thanks for your continuous reply but i am not able to execute he above code as it's ending with an invalid argument like "The method got an invalid argument".

Let me clarify on above

.......ctxtUSAGR-AGR_NAME[1,"& Col &"] 

Here 1 -> indicates the first column &  "& Col &" indicates the ROW in the su01 roles tab , please don't get confuse with the naming (sorry for mistake on naming rule ) .

Can you please help me to count the number of rows available in the screen for entry , i think that will resolve the problem.

Thanks,

Khagendra

[script_man]

Hi Khagendra,

the latest script contains only the following:

.....ctxtUSAGR-AGR_NAME[1,0]....

The parameter Col ist a constant = 0.

Regards,

ScriptMan

[khagendra_padhy]

Hi Script Man,

Finally the below code works fine & able to insert the roles.

session.findById("wnd[0]").maximize

session.findById("wnd[0]/tbar[0]/okcd").text = "/nsu01"

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/usr/ctxtUSR02-BNAME").text = "test"

session.findById("wnd[0]/usr/ctxtUSR02-BNAME").caretPosition = 4

session.findById("wnd[0]").sendVKey 0

session.findById("wnd[0]/tbar[1]/btn[18]").press

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").select

'The below line counts the number of rows in the screen.

i=session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").RowCount

'MsgBox "i:" &i

Do Until ExcelSheet.Cells(Row,1).Value=""

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR/ctxtUSAGR-AGR_NAME[1,"& Col &"]").text = ExcelSheet.Cells(Row,1).value

session.findById("wnd[0]").sendVKey 0

'MsgBox "Col:" &Col

Col=Col+1

if Col>i Then

session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = 12

Col=1

End if

Row=Row+1

'on error resume next

'session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").verticalScrollbar.position = Row

'if err.number <> 0 then exit do

'session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUU5:0106/tblSAPLSUU5TC_AGR").getAbsoluteRow(Row).selected = true

'on error goto 0

'Row=Row+1

Loop

'on error goto 0

session.findById("wnd[0]").sendVKey 11

The below link explains bit on it.

Initially i am counting the number of rows in the screen & then resetting the variable Col to the first line after scroll which works fantastic.

By the way thanks for your reply.

Regards,

Khagendra

[khagendra_padhy]

Just an off topic question , can you please refer me any book to go for an understanding to VB script for SAP.

Regards,

Khagendra