Skip to Content
0
Former Member
Apr 24, 2015 at 11:08 AM

Code for scrolling to next line item in item tab for delivery date change in PO using VBA or Vbs?

2304 Views

Hi there,

I am trying to scroll to next line item in PO using ME22N , using VBA (currently), but facing some coding problem. If any of you can help me in this regard?

I have data in excel like PO #, Purchasing line item #, Material #, PO Qty,Delivery date & Statistical date.

Purchasing line item # is something like 20, 30, 40, but sometimes it could be 21, 32, 45.


So thing is that, code should identify Purchasing Line item # according to data in excel, scroll to the line item with specific #, than identify particular material with reference to Qty, and than change the required date from excel.


for reference i am attaching a snapshot of line item & the code on which i am working.



My code:


Sub Update_Delivery_statDate()

Dim App, Connection, session As Object, currentrow As Integer

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.GetScriptingEngine

Set Connection = App.Children(0)

Set session = Connection.Children(0)

If IsObject(WScript) Then

WScript.ConnectObject session, "on"

WScript.ConnectObject Application, "on"

End If

Dim objExcel

Dim objSheet, intRow, i, j

Set objExcel = GetObject(, "Excel.Application")

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 To objSheet.UsedRange.Rows.Count 'Assuming there is a header row

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

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nme22n"

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

Do Until objSheet.Cells(i, 2).Value = ""

If objSheet.Cells(i, 2).Value <> "" Then

COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1

COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2

COL3 = Trim(CStr(objSheet.Cells(i, 3).Value)) 'Column3

COL4 = Trim(CStr(objSheet.Cells(i, 4).Value)) 'Column4

COL5 = Trim(CStr(objSheet.Cells(i, 5).Value)) 'Column5

COL6 = Trim(CStr(objSheet.Cells(i, 6).Value)) 'Column5

COL7 = Trim(CStr(objSheet.Cells(i, 7).Value)) 'Column5

COL8 = Trim(CStr(objSheet.Cells(i, 8).Value)) 'Column5

COL9 = Trim(CStr(objSheet.Cells(i, 9).Value)) 'Column5

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

session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").Text = COL2

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

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/cmbDYN_6000-LIST").SetFocus

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/btn%#AUTOTEXT002").press

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/btn%#AUTOTEXT002").press

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/btn%#AUTOTEXT002").press

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5").Select

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1320/tblSAPLMEGUITC_1320/ctxtMEPO1320-EEIND[2,0]").Text = COL9

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1320/tblSAPLMEGUITC_1320/ctxtMEPO1320-SLFDT[5,0]").Text = COL9

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1320/tblSAPLMEGUITC_1320/ctxtMEPO1320-SLFDT[5,0]").SetFocus

session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1320/tblSAPLMEGUITC_1320/ctxtMEPO1320-SLFDT[5,0]").caretPosition = 10

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

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

session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press

session.findById("wnd[0]/sbar").DoubleClick

session.findById("wnd[0]/shellcont").Close

objSheet.Cells(i, 3).Value = "DONE"

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

On Error Resume Next

End If

i = i + 1

Loop

Set objSheet = Nothing

Set objExcel = Nothing

Set SapGuiAuto = Nothing

Exit For

Next

MsgBox "Process Completed"

End Sub

Attachments

Capture.JPG (208.5 kB)