CODEWITHSUNDEEP
excelvbasap-gui
Thijsk
Thijsk

Reputation: 103

On Error GoTo in loop with 2x next (1x on error, 1x on normal procedure)

Currently trying to build a VBA where data from Excel gets migrated into SAP GUI screens. What I want is the following:

If no error occurs, save the order number (generated by SAP system) and fill the cell next to it with "UPDATED", then go to the next line in SAP GUI. If an error occurs, go to the err_handler, save the error status (obtained from SAP GUI) and fill the cell next to it with "ERROR", then go to the next line in SAP GUI.

However, for both codes, I need to use "next", and I only have 1 "For". The problem is, that the err_handling lines are being picked up (end of the code), even though the order went fine. What am I doing wrong here? Should I put the err_handling somewhere else?

Besides, one more question, the pop-up windows is now called wnd[1], but is there a way to find the real name of the pop-up window, so that the macro not just automatically clicks away every popup?

Sub CREATE_Sales_Order()

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(0) 'Get the first session (window) on that connection
Set aSheet = ActiveSheet

On Error GoTo err_handling

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
session.findById("wnd[0]").sendVKey 0

For i = 2 To aSheet.UsedRange.Rows.Count
If aSheet.Cells(i, 12).Value <> "UPDATED" Then
col1 = Trim(CStr(aSheet.Cells(i, 1).Value))   'Column1 Order type
col2 = Trim(CStr(aSheet.Cells(i, 2).Value))   'Column2 Sales Org
col3 = Trim(CStr(aSheet.Cells(i, 3).Value))   'Column3 Distribution Channel
col4 = Trim(CStr(aSheet.Cells(i, 4).Value))   'Column4 Division
col5 = Trim(CStr(aSheet.Cells(i, 5).Value))   'Column5 Customer order number
col6 = Trim(CStr(aSheet.Cells(i, 6).Value))   'Column6 Sold-to
col7 = Trim(CStr(aSheet.Cells(i, 7).Value))   'Column7 Ship-to
col8 = Trim(CStr(aSheet.Cells(i, 8).Value))   'Column8 Requested Delivery Date
col9 = Trim(CStr(aSheet.Cells(i, 9).Value))   'Column9 Material Number
col10 = Trim(CStr(aSheet.Cells(i, 10).Value)) 'Column10 Material Quantity
col11 = Trim(CStr(aSheet.Cells(i, 11).Value))  'Column11 Price
col12 = Trim(CStr(aSheet.Cells(i, 12).Value)) 'Column12 KG

On Error GoTo err_handling

'First entry screen
session.findById("wnd[0]/usr/ctxtVBAK-AUART").Text = col1 
session.findById("wnd[0]/usr/ctxtVBAK-VKORG").Text = col2 
session.findById("wnd[0]/usr/ctxtVBAK-VTWEG").Text = col3 
session.findById("wnd[0]/usr/ctxtVBAK-SPART").Text = col4 
'Session.findbyid("wnd[0]/usr/ctxtVBAK-SPART").SetFocus
'Session.findbyid("wnd[0]/usr/ctxtVBAK-SPART").caretPosition = 2
session.findById("wnd[0]").sendVKey 0

'Second entry screen (actual order)
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/txtVBKD-BSTKD").Text = col5 '
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUAGV-KUNNR").Text = col6 
session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/subPART-SUB:SAPMV45A:4701/ctxtKUWEV-KUNNR").Text = col7 
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/ssubHEADER_FRAME:SAPMV45A:4440/ctxtRV45A-KETDAT").Text = col8 
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtRV45A-MABNR[1,0]").Text = col9 
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[2,0]").Text = col10 '"20000"
'Session.findbyid("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[2,0]").SetFocus
'Session.findbyid("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[2,0]").caretPosition = 19
session.findById("wnd[0]").sendVKey 0
'If the bill-to pop-up exists, automatically click it away
If session.ActiveWindow.Name = "wnd[1]" Then
'session.findById("wnd[1]/usr/btnSPOP-OPTION1").press
session.findById("wnd[1]").sendVKey 0
End If
'Continue the scheduling window
session.findById("wnd[0]/shellcont/shell/shellcont[0]/shell").pressButton "CONT"
'If the price needs to be changed, do this
If aSheet.Cells(i, 11).Value <> "" Then
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/subSUBSCREEN_BUTTONS:SAPMV45A:4050/btnBT_PKON").press
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_ITEM/tabpT\05/ssubSUBSCREEN_BODY:SAPLV69A:6201/tblSAPLV69ATCTRL_KONDITIONEN/ctxtKOMV-KSCHL[1,20]").Text = "YMCP"
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_ITEM/tabpT\05/ssubSUBSCREEN_BODY:SAPLV69A:6201/tblSAPLV69ATCTRL_KONDITIONEN/txtKOMV-KBETR[3,20]").Text = col11 
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_ITEM/tabpT\05/ssubSUBSCREEN_BODY:SAPLV69A:6201/tblSAPLV69ATCTRL_KONDITIONEN/ctxtRV61A-KOEIN[4,20]").Text = "USD"
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_ITEM/tabpT\05/ssubSUBSCREEN_BODY:SAPLV69A:6201/tblSAPLV69ATCTRL_KONDITIONEN/txtKOMV-KPEIN[5,20]").Text = col12 
session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_ITEM/tabpT\05/ssubSUBSCREEN_BODY:SAPLV69A:6201/tblSAPLV69ATCTRL_KONDITIONEN/ctxtKOMV-KMEIN[6,20]").Text = "KG"
session.findById("wnd[0]").sendVKey 0
End If
session.findById("wnd[0]/tbar[0]/btn[11]").press
'If the Dynamic credit check pop-up exists, automatically click it away
If session.ActiveWindow.Name = "wnd[1]" Then
session.findById("wnd[1]").sendVKey 0
End If

'report the orderstatus back to excel
m_status = session.findById("wnd[0]/sbar").Text
If m_status <> "" Then
    aSheet.Cells(i, 13) = m_status
    
aSheet.Cells(i, 14) = "UPDATED"
End If

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
session.findById("wnd[0]").sendVKey 0
End If


'If an error occurs, show that in the sheet, macro now crashes when there is a second error, still needs fix
err_handling: 'report the orderstatus back to excel
m_status = session.findById("wnd[0]/sbar").Text
aSheet.Cells(i, 13) = m_status
aSheet.Cells(i, 14) = "Error"
'open new order
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
session.findById("wnd[0]").sendVKey 0

Next

MsgBox "All orders have been entered into SAP"

End Sub

Upvotes: 0

Views: 1697

Answers (2)

user3598756
user3598756

Reputation: 29421

I think you may be after the following "architecture":

Sub CREATE_Sales_Order()

    Set SapGuiAuto = GetObject("SAPGUI")  'Get the SAP GUI Scripting object
    ' ...
    ' your "Setting" code section
    ' ...

    On Error GoTo err_handling

    session.findById("wnd[0]").maximize
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
    session.findById("wnd[0]").sendVKey 0

begin_Loop: '<--|place this label to be referenced by 'err_handling' code should this latter be reached before entering the loop

    For i = 2 To aSheet.UsedRange.Rows.Count
        If aSheet.Cells(i, 12).Value <> "UPDATED" Then
            '...
            '... your code inside If-Then-EndIf 
            '    you can take 'On Error GoTo err_handling' statement off it
            '...
        End If

next_SAP_Line: '<--|place this label to be referenced by 'err_handling' code should this latter be reached while inside the loop

        'open new order
        session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
        session.findById("wnd[0]").sendVKey 0
    Next

    MsgBox "All orders have been entered into SAP"
    Exit Sub '<--| exit sub not to reach the subsequent 'err_handling' code 


    'If an error occurs, show that in the sheet, macro now crashes when there is a second error, still needs fix
err_handling:    'report the orderstatus back to excel
    m_status = session.findById("wnd[0]/sbar").Text
    aSheet.Cells(i, 13) = m_status
    aSheet.Cells(i, 14) = "Error"

    If i = 0 Then Resume begin_Loop '<--| if error happend before entering loop, the resume at loop start
    Resume next_SAP_Line '<--| if error happend inside loop, then resume at loop next itaration

End Sub

Upvotes: 1

Comintern
Comintern

Reputation: 22195

Just test to see if you reached that code due to an error:

        'If an error occurs, show that in the sheet, macro now crashes when there is a second error, still needs fix
err_handling:                                    'report the orderstatus back to excel
        If Err.Number <> 0 Then  '<-- Test for error.
            m_status = session.findById("wnd[0]/sbar").Text
            aSheet.Cells(i, 13) = m_status
            aSheet.Cells(i, 14) = "Error"
            'open new order
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/nva01"
            session.findById("wnd[0]").sendVKey 0
            Err.Clear
        End If
    Next

Upvotes: 2

Related Questions