Error Handler occasionally activates with no error

[ducktapeonmydesk]

I am having an issue with a bit of script where an error handler will be called when there is no error.

This script is pulling data from SAP. The way I want it to work is when there is no data to be found, the error handler is called. While that is working, occasionally, the error handler will be called even when there is data in SAP.

Does anyone have any insight to what may be going on?

         'Go to Print Preview         
         On Error GoTo ErrHandler
         session.findById("wnd[0]").sendVKey 46
                   
        'Resize 'ship-to' column
         session.findById("wnd[0]/mbar/menu[3]/menu[2]/menu[0]").Select
         session.findById("wnd[1]/usr/tabsTS_LINES/tabpLI01/ssubSUB810:SAPLSKBH:0810/tblSAPLSKBHTC_WRITE_LIST/txtGT_WRITE_LIST-OUTPUTLEN[2,1]").Text = "10"
         session.findById("wnd[1]/tbar[0]/btn[0]").press
          
        'Extract data as a text file
         session.findById("wnd[0]/tbar[1]/btn[45]").press
         session.findById("wnd[1]/tbar[0]/btn[0]").press
         session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = cl.Value & ".txt"
         session.findById("wnd[1]/usr/ctxtDY_PATH").Text = NewFolder
         session.findById("wnd[1]/tbar[0]/btn[0]").press
         
         GoTo NextVendor
         
'If there is no data available, make note to broaden criteria
ErrHandler:
         session.findById("wnd[1]/tbar[0]/btn[0]").press
         cl.ClearContents
         End If
         Err.Clear
         Resume NextVendor

The error number is 619 and is highlighting the line

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

When no data is found in SAP, a message box comes up stating so, and this line hits the check mark box, the intent to be moving on to the next cell in the spreadsheet. However, this box doesn't appear when there actually is data.

 

[NdNoviceHlp]

Trial changing this...

Err.Clear

To...

On error goto 0

HTH. Dave

 

[ducktapeonmydesk]

That did not fix it, unfortunately. Here is the entire script. Maybe something higher up is causing it?

Sub Customer_History_Extraction()

Application.DisplayAlerts = False


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
    
   Dim cl As Range
   Dim usrnm As Range
   Dim NewFolder As String
   Dim FSO As Object
    
    Set FSO = CreateObject("scripting.filesystemobject")
    Set usrnm = Worksheets("1-Main").Range("N5")
    
    'Set folder path - if it doesn't exist, create it.  If it does exist, delete it and all of its contents, then create it again
     NewFolder = "C:\Users\" & usrnm & "\Auto ZZCUSREP"
        If Dir(NewFolder, vbDirectory) = "" Then
            MkDir NewFolder
        Else
            FSO.deletefolder NewFolder
            MkDir NewFolder
        End If
        
        
    Columns("R:R").Sort key1:=Range("R1:R1"), order1:=xlAscending, Header:=xlYes
    Columns("T:T").Sort key1:=Range("T1:T1"), order1:=xlAscending, Header:=xlYes
    Columns("J:J").Sort key1:=Range("J1:J1"), order1:=xlAscending, Header:=xlYes
    Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
    For Each cl In ActiveSheet.Range("J2:J501")
        
       If Len(cl) <> 0 Then
  
        'Start the transaction to view a table
        session.StartTransaction "ZZCUSREP"


        'Select our customer
         session.findbyId("wnd[0]/usr/ctxtS_KUNNR-LOW").Text = cl.Value
            
        'Select our branches
         If Worksheets("1-Main").Range("L2") <> 0 Then
            If Worksheets("1-Main").Range("L3") <> 0 Then
                Range("L2").Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.Copy
                session.findbyId("wnd[0]/usr/btn%_S_WERKS_%_APP_%-VALU_PUSH").press
                session.findbyId("wnd[1]").sendVKey 16
                session.findbyId("wnd[1]/tbar[0]/btn[24]").press
                session.findbyId("wnd[1]/tbar[0]/btn[8]").press
            Else
                session.findbyId("wnd[0]/usr/btn%_S_WERKS_%_APP_%-VALU_PUSH").press
                session.findbyId("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL-SLOW_I[1,0]").Text = Range("L2")
                session.findbyId("wnd[1]").sendVKey 8
            End If
         Else
            session.findbyId("wnd[0]/usr/btn%_S_WERKS_%_APP_%-VALU_PUSH").press
            session.findbyId("wnd[1]").sendVKey 16
            session.findbyId("wnd[1]/tbar[0]/btn[8]").press
         End If
        
        'Select materials
         If Worksheets("1-Main").Range("R2") <> 0 Then
            Range("R2").Select
                If Worksheets("1-Main").Range("R3") <> 0 Then
                    Range(Selection, Selection.End(xlDown)).Select
                End If
            Selection.Copy
            session.findbyId("wnd[0]/usr/btn%_S_MATNR_%_APP_%-VALU_PUSH").press
            session.findbyId("wnd[1]/tbar[0]/btn[24]").press
            session.findbyId("wnd[1]/tbar[0]/btn[8]").press
        End If
        
        'Set date range
         If Worksheets("1-Main").Range("N2") <> 0 And Worksheets("1-Main").Range("P2") <> 0 Then
            session.findbyId("wnd[0]/usr/ctxtS_FKDAT-LOW").Text = Range("N2")
            session.findbyId("wnd[0]/usr/ctxtS_FKDAT-HIGH").Text = Range("P2")
         End If
         
         'Select individual vendors
          If Worksheets("1-Main").Range("T2") <> 0 Then
            Range("T2").Select
                If Worksheets("1-Main").Range("T3") <> 0 Then
                    Range(Selection, Selection.End(xlDown)).Select
                End If
            Selection.Copy
            session.findbyId("wnd[0]/usr/btn%_S_EMNFR_%_APP_%-VALU_PUSH").press
            session.findbyId("wnd[1]/tbar[0]/btn[24]").press
            session.findbyId("wnd[1]/tbar[0]/btn[8]").press
         End If
         
        'Execute Transaction
         session.findbyId("wnd[0]/tbar[1]/btn[8]").press
         
        'Go to Print Preview
         On Error GoTo ErrHandler
         session.findbyId("wnd[0]").sendVKey 46
                   
        'Resize 'ship-to' column
         session.findbyId("wnd[0]/mbar/menu[3]/menu[2]/menu[0]").Select
         session.findbyId("wnd[1]/usr/tabsTS_LINES/tabpLI01/ssubSUB810:SAPLSKBH:0810/tblSAPLSKBHTC_WRITE_LIST/txtGT_WRITE_LIST-OUTPUTLEN[2,1]").Text = "10"
         session.findbyId("wnd[1]/tbar[0]/btn[0]").press
          
        'Extract data as a text file
         session.findbyId("wnd[0]/tbar[1]/btn[45]").press
         session.findbyId("wnd[1]/tbar[0]/btn[0]").press
         session.findbyId("wnd[1]/usr/ctxtDY_FILENAME").Text = cl.Value & ".txt"
         session.findbyId("wnd[1]/usr/ctxtDY_PATH").Text = NewFolder
         session.findbyId("wnd[1]/tbar[0]/btn[0]").press
         
         GoTo NextVendor
         
'If there is no data available, make note to broaden criteria
ErrHandler:
         session.findbyId("wnd[1]/tbar[0]/btn[0]").press
         cl.ClearContents
         End If
         On Error GoTo 0
         Resume NextVendor
         
NextVendor:
    Next cl
    
End Sub

 

[ducktapeonmydesk]

I noticed that when this issue occurs, the date range has not yet been copied into SAP, which tells me the error occurs before

[COLOR=#333333] On Error GoTo ErrHandler[/COLOR]

yet VBA still procedes to the ErrHandler line.

 

[ducktapeonmydesk]

In the event that anyone ever comes across this, here is how I fixed this.

First off, I found out that you don't "end" an error handler, all future errors will revert to it. Future errors of the same type (that can be resolved by error handler) are perfectly okay, however, if an error occurs that can't be handled by the error handler, you will get another run time error. In this case, there was an issue with SAP creating a random error when entering the date. When this occurred, the error went to error handler because the error handler was still "enabled." To disable the error handler, I added

On Error GoTo 0

after the section that the error handler was designed to handle.

'Go to Print Preview
On Error GoTo ErrHandler
session.findbyID("wnd[0]").sendVkey 46
On Error GoTo 0

In simplified terms, this line:

On Error GoTo ErrHandler

states "for any future code, if an error occurs, go to ErrHandler"

And this line here:

On Error GoTo 0

states "for any future code, stop using ErrHandler for any errors that occur."

They are kind of like bookends\parentheses\etc for any code between them. Any errors as a result of the code between them should be handled by ErrHandler, but any code outside of that should not be.

 

[NdNoviceHlp]

Glad U got that sorted out and thanks for posting your outcome. On your first code, U had Error.Clear within the err.handler which clears the error but then returns U to the On error GoTo ErrHandler command. Following that within the errhandler, the "Resume Next Vendor" took U to the top of the loop for the next vendor without completion of the code beneath the error. Placing the On Error Goto 0 code following the error clears/cancels the error and allows U to proceed executing your remaining code after the error. However, for the remaining code the ErrHandler no longer works because On Error Goto 0 is still in effect. Usually before a line that may create, an error "On Error Resume Next" is used before the possible error and then "On Error Goto 0" is used following. If U want to again use your error handler U need to again provide that instruction following the "On Error Goto 0". HTH. Dave

 

[Fluff]

This is not entirely correct

On Error Goto 0 code following the error clears/cancels the error

There's a very good article here about error handling
http://excelmatters.com/2015/03/17/on-error-wtf/

 

[NdNoviceHlp]

Thanks so much Fluff. That was very informative. I thought that U were able to provide further error instruction following On Error Goto 0. I was wrong. Dave

 

[ducktapeonmydesk]

Thank you, fluff! Very informative.

 

[Fluff]

Thank RoryA, he wrote it.