0

I have created a SAP GUI script to delete PO's in SAP. As long as PO is containing one line item the script is fairly straight forward. But what if I have PO's with more than one line item and I want to delete only specific item/row on the PO?

Attached is a picture of a Purchase Order in SAP with 4 line items. My script is recorded for deleting the second line item (Item 20) at this line: session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211").getAbsoluteRow(1).selected = true.

enter image description here

Now, I need to create a script which fetches information from an Excel sheet regarding what PO to access (column1) and which line item to delete (column2) on that PO then take next PO and do the same etc. until loop is finished.

Code:

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


Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(,"Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 to objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1


session.findById("wnd[0]/tbar[0]/okcd").text = "/nme22n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").text = Col1
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211").getAbsoluteRow(1).selected = true
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/btnMEPO1211-STATUSICON[0,1]").setFocus
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/btnDELETE").press
session.findById("wnd[1]/usr/btnSPOP-OPTION1").press
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
session.findById("wnd[0]/tbar[1]/btn[17]").press



aux=col1
CreateObject("WScript.Shell").run("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
next

msgbox "Process Completed"
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Adde
  • 45
  • 2
  • 10
  • Possible duplicate of [Loop through each row of a range in Excel](https://stackoverflow.com/questions/1463236/loop-through-each-row-of-a-range-in-excel) – Luuklag May 12 '19 at 12:11

1 Answers1

0

You could try the following:

...
For i = 2 to objSheet.UsedRange.Rows.Count
COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2

session.findById("wnd[0]/tbar[0]/okcd").text = "/nme22n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").text = Col1
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211").getAbsoluteRow(" & COL2 & ").selected = true
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/btnDELETE").press
session.findById("wnd[1]/usr/btnSPOP-OPTION1").press
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
session.findById("wnd[0]/tbar[1]/btn[17]").press

aux=COL1 & " / " & COL2
CreateObject("WScript.Shell").run("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
next

msgbox "Process Completed"

Regards, ScriptMan

ScriptMan
  • 1,580
  • 1
  • 9
  • 9
  • I couldn't test script until today as the test module was under maintenance. It didn't work with line "getAbsoluteRow(" & COL2 & ")". Instead I tried with "getAbsoluteRow(COL2)" and aux=COL1 & " " & COL2 and it works now. The strange thing is that I thought I tested "getAbsoluteRow(COL2)" prior to asking the question here but apparently I didn't... – Adde May 21 '19 at 13:24