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