SAP and Excel: On Error GoTo Within On Error GoTo?

Question

I'm trying to go back and forth between my SAP GUI and my Excel spreadsheet. I have a list of tables that I want to view in SAP, pull the data from SAP, paste into Excel, and go to the next table. If that table does not exist in SAP, I want it to go to the next table (the table may not currently exist, but it might exist in the future, and I want this to be dynamic I do not want to hard code the table names).

I already have one sequence of an On Error GoTo working, but say that the next table we want to reference also doesn't exist; that error would have to be handled.

Sub SAPEverything()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ans = MsgBox("Are you currently logged into SAP?", vbYesNoCancel)

If ans = vbNo Then
    MsgBox ("Please log into SAP, then come back to this macro.")
    Exit Sub
ElseIf ans = vbCancel Then
    Exit Sub
ElseIf ans = vbYes Then
    frmSAP.Show
    frmSAP.Hide

    LastRow = Sheets("Sheet2").Cells(Rows.Count, 19).End(xlUp).Row
    CurrRow = 2

    For i = 2 To LastRow
        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
        'Start the transaction to view a table
        session.StartTransaction "Transaction"

        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[1]/btn[16]").press
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        On Error GoTo HandlingIt
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItemByText "Copy Text"

        Workbooks("WorkbookName").Activate
        Sheets("Sheet2").Select
        Cells(CurrRow, 2).Select
        ActiveSheet.Paste
        NewLastRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
        For k = CurrRow To NewLastRow
            Sheets("Sheet2").Cells(k, 1).Value = Sheets("Sheet2").Cells(i, 19).Value
        Next k
        CurrRow = NewLastRow + 1

    Next i
HandlingIt:
    currErr = i
    For i = currErr + 1 To LastRow
        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
        'Start the transaction to view a table
        session.StartTransaction "Transaction"

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

        session.findById("wnd[0]/tbar[1]/btn[8]").press
        On Error GoTo HandlingIt
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItemByText "Copy Text"

        Workbooks("WorkbookName").Activate
        Sheets("Sheet2").Select
        Cells(CurrRow, 2).Select
        ActiveSheet.Paste
        NewLastRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
        For k = CurrRow To NewLastRow
            Sheets("Sheet2").Cells(k, 1).Value = Sheets("Sheet2").Cells(i, 19).Value
        Next k
        CurrRow = NewLastRow + 1

    Next i
End If

Is there any possible way to refer to another On Error GoTo section of the code once I'm already in a On Error GoTo section? Or even go back to the beginning of the current On Error GoTo section?

Answer 1

Keep the error-handling code completely separate from the "happy path". You want the code in an error-handling subroutine to only ever execute when there's a runtime error, and most importantly you want to handle that error - use a Resume [label] instruction for that.

Public Sub DoSomething()
    'do stuff...
    On Error GoTo CleanFail
    For i = a To b
       'do more stuff...
Skip:
    Next
    Exit Sub ' end of happy path

CleanFail: ' begin error handling code
    Debug.Print Err.Description; ". Skipping iteration #" & i
    Resume Skip ' clears error state and jumps to Skip label
End Sub

If you find yourself needing more than a single error-handling subroutine, then your procedure is doing too many things. Break it down into smaller procedures that do fewer things, and therefore have fewer reasons to fail.

Answer 2

You can just skip to the next loop iteration in case of error, and reset the error Handler before proceeding to the next iteration. See comments in code.

Sub SAPEverything()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ans = MsgBox("Are you currently logged into SAP?", vbYesNoCancel)

    If ans = vbNo Then
        MsgBox ("Please log into SAP, then come back to this macro.")
        Exit Sub
    ElseIf ans = vbCancel Then
        Exit Sub
    ElseIf ans = vbYes Then
        frmSAP.Show
        frmSAP.Hide

        LastRow = Sheets("Sheet2").Cells(Rows.Count, 19).End(xlUp).Row
        CurrRow = 2

        For i = 2 To LastRow
            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
            'Start the transaction to view a table
            session.StartTransaction "Transaction"

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

            ' Enable error handler, in case of any error, execution will go to SkipIt.
            On Error GoTo SkipIt

            session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll
            session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
            session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItemByText "Copy Text"

            Workbooks("WorkbookName").Activate
            Sheets("Sheet2").Select
            Cells(CurrRow, 2).Select
            ActiveSheet.Paste
            NewLastRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
            For k = CurrRow To NewLastRow
                Sheets("Sheet2").Cells(k, 1).Value = Sheets("Sheet2").Cells(i, 19).Value
            Next k

            CurrRow = NewLastRow + 1

SkipIt:
            On Error GoTo 0 ' Reset error handler.

        Next i

    End If
End Sub

Answer 3

There's no reason to duplicate code. If you just want to skip the remainder of the loop when you hit an error, you can do exactly that.

On a side note, you should always reset the OnError behavior (by running OnError GoTo 0) when you're done with your error handling. You don't want bugs further down in your code mysteriously sending you back to your loop. Debugging that is the stuff of nightmares.

    For i = 2 To LastRow
        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
        'Start the transaction to view a table
        session.StartTransaction "Transaction"

        session.findById("wnd[0]").maximize
        session.findById("wnd[0]/tbar[1]/btn[16]").press
        session.findById("wnd[0]/tbar[1]/btn[8]").press
        On Error GoTo NextLoopIteration
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").SelectAll
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").contextMenu
        session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell").selectContextMenuItemByText "Copy Text"

        Workbooks("WorkbookName").Activate
        Sheets("Sheet2").Select
        Cells(CurrRow, 2).Select
        ActiveSheet.Paste
        NewLastRow = Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
        For k = CurrRow To NewLastRow
            Sheets("Sheet2").Cells(k, 1).Value = Sheets("Sheet2").Cells(i, 19).Value
        Next k
        CurrRow = NewLastRow + 1

NextLoopIteration:
        On Error GoTo 0
    Next i