Double error handling - continue after error without going back

Question

I have bunch of excel files of which some contain P3 sheets, some P2, and very few neither. I tried to use error handling, but if two files in a row do not have P3. I had an error within an error block. So I tried to end the error block with resume next or something. But then I would go back to just after the error. Whilst I actually wanted to continue after the error handling block (without ending the code). Any suggestions? Or would it be better to use a different method to identify the existence of the sheet?

Do While myFile <> ""
      Set wbCase = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=0)
On Error GoTo Line2
Set wsCaseinfo = wbC.Worksheets("P3")
GoTo Line3:
Line2:
On Error GoTo Line0
Set wsCaseinfo = wbC.Worksheets("P2")
Resume Next
Line3:
On Error GoTo 0

...
some code doing stuff
...

On Error Resume Next
k = k + 1
wbNew.Sheets("Sheet1").Cells(k, 1) = wbC.Name
wbNew.Sheets("Sheet1").Cells(k, 2) = wsCinfo.Name

GoTo Line1

Line0:
wbNew.Sheets("Sheet1").Cells(k, 3) = "Fail"
Resume Next

Line1:

wbC.Close SaveChanges:=False     

'Get next file name

     myFile = Dir

  Loop

You should check whether the sheet you are looking for exists, instead of relying on error, and then process it. See this [post](https://stackoverflow.com/questions/6040164/excel-vba-if-worksheetwsname-exists) for how to check whether a sheet exists. — RobertBaron, May 19 '19 at 22:37

score 1 · Accepted Answer · answered May 19 '19 at 22:42

You can do something like this:

Option Explicit

Sub Tester()

    Dim myFile, myPath, ws As Worksheet, wbCase As Workbook, sheetName

    myPath = "path/to/files/"

    myFile = Dir(myPath & "*.xls*")

    Do While myFile <> ""

        Set wbCase = Workbooks.Open(Filename:=myPath & myFile, UpdateLinks:=0)
        For Each ws In wbCase.Worksheets
            Select Case ws.Name
                Case "P3", "P2":
                    'got a match, so do something here with ws
                    Exit For 'umless you want to look for other sheets

            End Select
        Next ws

        wbCase.Close SaveChanges:=False
        myFile = Dir
    Loop

End Sub

Double error handling - continue after error without going back

1 Answers1