VBA Excel to SAP Program

V

VBA is new to me

New Member
Joined
Dec 10, 2018
Messages
1
Hi,
I am trying to write a VBA code from excel to populate fields within SAP program.
I can get all of them to work except for the field titled Reqmt No.
I have attached the code below and a screen dump of the SAP fields...can anyone she some light/help please?



Code: 
Sub UpdatePO()


    Dim SystemNumber, SapGuiAuto, SAPApp, SAPCon, session
    SystemNumber = 1
    
    Set SapGuiAuto = GetObject("SAPGUI")  'Get the SAP GUI Scripting object
    Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
    Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
    Set session = SAPCon.Children(SystemNumber - 1) 'Get the first session (window) on that connection
    
    Dim i, j, k, statusMessage, rowCount, HeaderNoteMessage, ItemNoteMessage, PO, POLine, POLineDelDate, ReqmtNo, strScreen, strScreen2
        
    i = 1
    Do While Sheets("PO Line Del Date").Cells(i + 1, 1).Value <> ""
        i = i + 1
        
        ' Get the PO number, PO Line item, and Header note message from "PO Line Del Date" tab of the Excel file
        PO = Sheets("PO Line Del Date").Cells(i, 1).Value
        POLine = Sheets("PO Line Del Date").Cells(i, 2).Value
        POLineDelDate = Sheets("PO Line Del Date").Cells(i, 3).Value
        ReqmtNo = Sheets("PO Line Del Date").Cells(i, 4).Value
        HeaderNoteMessage = Sheets("PO Line Del Date").Cells(i, 5).Value
        HeaderNoteMessage = Now() & " - " & session.Info.User & " - Updated Delivery Date for PO line " & POLine & " - " & HeaderNoteMessage & vbCr
        
        'Debug.Print session.Info.User
        session.findById("wnd[0]").resizeWorkingPane 200, 25, False
        session.findById("wnd[0]/tbar[1]/btn[17]").press
        session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").Text = PO
        session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").caretPosition = 10
        session.findById("wnd[1]/tbar[0]/btn[0]").press
                    
                  '****Edit Mode*******
        '***Check for change or display mode *****
        If Not (session.findById("wnd[0]/mbar/menu[0]/menu[6]").Changeable) Then
                session.findById("wnd[0]").SendVKey 7
                session.findById("wnd[0]").SendVKey 0
                
        Else
                session.findById("wnd[0]").SendVKey 0
        End If


        '****If PO is subject to Release, hit Enter*******
        While Not (session.findById("wnd[0]/sbar").Text = "")
            session.findById("wnd[0]").SendVKey 0
        Wend
        
        '****Collapse Header*******
        session.findById("wnd[0]").SendVKey 29
        
        '*** Select Line item to update tax code
        On Error Resume Next
          session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/btnEDITFILTER").press
          session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0600/btnAPP_WL_SING").press
          session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0600/btn600_BUTTON").press
          session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").Text = POLine
          session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").caretPosition = 2
          session.findById("wnd[2]/tbar[0]/btn[0]").press
          session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").Text = POLineDelDate
          session.findById("wnd[0]").SendVKey 28
          session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/cmbDYN_6000-LIST").SetFocus
          session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/cmbDYN_6000-LIST").Key = "   1"
          session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5").Select
        Debug.Print Err.Description
        If Not (Err.Description = "") Then
            For j = 10 To 20
                On Error Resume Next
                strScreen = "00" & CStr(j)
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen & "/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/btnEDITFILTER").press
                session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0600/btnAPP_WL_SING").press
                session.findById("wnd[1]/usr/subSUB_DYN0500:SAPLSKBH:0600/btn600_BUTTON").press
                session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").Text = POLine
                session.findById("wnd[2]/usr/ssub%_SUBSCREEN_FREESEL:SAPLSSEL:1105/ctxt%%DYN001-LOW").caretPosition = 2
                session.findById("wnd[2]/tbar[0]/btn[0]").press
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen & "/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").Text = POLineDelDate
                session.findById("wnd[0]").SendVKey 28
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen & "/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/cmbDYN_6000-LIST").SetFocus
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen & "/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB1:SAPLMEGUI:6000/cmbDYN_6000-LIST").Key = "   1"
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen & "/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5").Select
                If Err.Description = "" Then
                    On Error GoTo 0
                    Exit For
                End If
            Next j
        End If
    
       
        '***Delivery Schedule Tab to update Statistical Delivery Date
'        session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0019/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-SLFDT[5,0]").Text = POLineDelDate
    
        '***Text tab to enter note in Item Note
        ' Removed this section because we want to put the message in the header note instead
        'session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT15").Select
        'session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT15/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1329/subTEXTS:SAPLMMTE:0200/cntlTEXT_TYPES_0200/shell").SelectedNode = "F09"
        'ItemNoteMessage = ItemNoteMessage & session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT15/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1329/subTEXTS:SAPLMMTE:0200/subEDITOR:SAPLMMTE:0201/cntlTEXT_EDITOR_0201/shellcont/shell").Text
        'session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT15/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1329/subTEXTS:SAPLMMTE:0200/subEDITOR:SAPLMMTE:0201/cntlTEXT_EDITOR_0201/shellcont/shell").Text = ItemNoteMessage
        'session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT15/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1329/subTEXTS:SAPLMMTE:0200/subEDITOR:SAPLMMTE:0201/cntlTEXT_EDITOR_0201/shellcont/shell").SetSelectionIndexes 25, 25
    
        
        '****Press Header details button *****
        session.findById("wnd[0]").SendVKey 26


        '****Update Headernote with message*****
        On Error Resume Next
        session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3").Select
        session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/cntlTEXT_TYPES_0100/shell").SelectedNode = "F02"
        HeaderNoteMessage = HeaderNoteMessage & vbCr & session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/subEDITOR:SAPLMMTE:0101/cntlTEXT_EDITOR_0101/shellcont/shell").Text
        session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0013/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/subEDITOR:SAPLMMTE:0101/cntlTEXT_EDITOR_0101/shellcont/shell").Text = HeaderNoteMessage
        If Not (Err.Description = "") Then
            For j = 10 To 20
                Debug.Print Err.Description
                On Error Resume Next
                strScreen2 = "00" & CStr(j)
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen2 & "/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3").Select
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen2 & "/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/cntlTEXT_TYPES_0100/shell").SelectedNode = "F02"
                HeaderNoteMessage = HeaderNoteMessage & vbCr & session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen2 & "/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/subEDITOR:SAPLMMTE:0101/cntlTEXT_EDITOR_0101/shellcont/shell").Text
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:" & strScreen2 & "/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT3/ssubTABSTRIPCONTROL2SUB:SAPLMEGUI:1230/subTEXTS:SAPLMMTE:0100/subEDITOR:SAPLMMTE:0101/cntlTEXT_EDITOR_0101/shellcont/shell").Text = HeaderNoteMessage
                If Err.Description = "" Then
                    On Error GoTo 0
                    Exit For
                End If
            Next
        End If
        
        '***Message Output button******
'        session.findById("wnd[0]/tbar[1]/btn[21]").press
'        On Error Resume Next
'        While Not (session.findById("wnd[0]/sbar").Text = "")
'            session.findById("wnd[0]").SendVKey 0
'        Wend
'        On Error GoTo 0
'        Debug.Print session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").rowCount
'        Row = 0
'        Outtext = session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/ctxtDNAST-KSCHL[1," & Row & "]").Text
'        If Outtext <> "ZZEU" Then
'          Do While Outtext <> "ZZEU"
'            Row = Row + 1
'            Outtext = session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/ctxtDNAST-KSCHL[1," & Row & "]").Text
'          Loop
'        End If
'        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(Row).selected = True
'        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").SetFocus
'        session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").caretPosition = 0
'        '***Trash button
'        If Not (session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").Text = "") Then
'            session.findById("wnd[0]/tbar[1]/btn[18]").press
'            session.findById("wnd[1]/tbar[0]/btn[0]").press
'        End If
        
        '***Back to main screen
 '       session.findById("wnd[0]/tbar[0]/btn[3]").press
 '       On Error Resume Next
 '       While Not (session.findById("wnd[0]/sbar").Text = "")
 '           session.findById("wnd[0]").SendVKey 0
 '       Wend
 '       On Error GoTo 0
        
        '***Save PO
        On Error Resume Next
        session.findById("wnd[0]/tbar[0]/btn[11]").press
        session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
        On Error GoTo 0
        '*****Bypass random message output dialog box ********
        On Error Resume Next
        session.findById("wnd[1]/tbar[0]/btn[0]").press
        session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
        On Error GoTo 0




        '***Log Message
        statusMessage = session.findById("wnd[0]/sbar").Text
        Debug.Print statusMessage
        If Not (statusMessage = "") Then
          Sheets("PO Line Del Date").Cells(i, 6).Value = statusMessage
        Else
          Sheets("PO Line Del Date").Cells(i, 6).Value = "No status Message"
        End If
        
        Sheets("PO Line Del Date").Cells(1, 7).Value = (i - 1)
        
    Loop
      
    MsgBox "Congratulations!  You've updated delivery dates for " & (i - 1) & " purchase order line items."
    
End Sub
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Similar threads

C
  • Question Question
SAP Scripting Help needed!
Replies
0
Views
239
Cime14
C
J
  • Question Question
SAP Script
Replies
0
Views
1K
jimbogarner
J
M
  • Solved
VBA - how to add a copy and paste values on current module
Replies
2
Views
110
Minx_Minxy
M
T
  • Question Question
VBA On error isn't working
Replies
2
Views
181
Tashkkaa
T
M
  • Solved
Macro stops after message box appear
Replies
3
Views
470
bobsan42
bobsan42

Forum statistics

Threads
1,223,723
Messages
6,174,123
Members
452,546
Latest member
Rafafa

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top