Multiple error handling

[tiredofit]

The problem I have is to open password protected files.

I know of 2 possible passwords, so if either works, then fine, otherwise I want to display a message informing the user that neither passwords are correct.

This is my code:

On Error GoTo Err1
 
    Workbooks.Open Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, PASSWORD:="FirstPW"


    ' Do something


SomeLine:
 
On Error GoTo 0

    ' do something else

Exitpoint:

    On Error GoTo 0


Exit Sub

Err1:

    Resume NextLine ' Looks weird to resume to the line immediately following this line but I think it is necessary.

NextLine:

    On Error GoTo 0 ' Is this necessary to reset the error handling status?
 
    On Error GoTo Err2
  
    Workbooks.Open Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, PASSWORD:="SecondPW"
  
    GoTo SomeLine ' Putting Resume SomeLine instead does NOT work

Err2:

    MsgBox Prompt:="Both passwords don't work."
                                                   
    Resume Exitpoint

Is the code OK or does it need tidying?

As far as I am aware, every time you handle an error, you must use the Resume word instead of GoTo. However in my example, I tried:

Resume SomeLine

but it didn't go to the line SomeLine. Instead it went to the next line in the code.

Also does putting:

On Error GoTo 0

reset the error handling status?

Thanks

 

[baitmaster]

See code recommendations (untested). Essentially I'm using error handling only on specific tasks, then seeing if each task completed. I'm doing this by creating objects that I can use elsewhere, and I don't need to start jumping around and having to track where I am

Dim wb As Workbook                                                                                          ' declare workbook object


' attempt to assign the workbook object
On Error Resume Next                                                                                        ' in case password fails
    Set wb = Workbooks.Open(Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, Password:="FirstPW")         ' attempt to open workbook and assign it to the object variable
On Error GoTo 0                                                                                             ' end error handling


' test if file is not open
If wb Is Nothing Then                                                                                       ' workbook was not opened - attempt second password
    On Error Resume Next
        Set wb = Workbooks.Open(Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, Password:="SecondPW")
    On Error GoTo 0
End If
    
' test if file is still not open
If wb Is Nothing Then
    MsgBox "ERROR: unable to open file", vbCritical                                                         ' inform User
    Exit Sub                                                                                                ' stop processing
End If


' if you get here then the file was opened and can be referred to from anywhere using the object variable "wb"
' your code here...

On Error GoTo 0 does reset your error handling. I do this as soon as possible even if I then have to switch it back on again, as I am looking to handle only one type of error, not others by accident - if others may exist I want to understand them and not mask them (same reason I all but refuse to use IFERROR)

 

[shg]

How about ...

  On Error Resume Next
  Workbooks.Open Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, Password:="FirstPW"

  If Err.Number Then
    Err.Clear
    Workbooks.Open Filename:="C:\MyFile\MyFile.xlsx", ReadOnly:=True, Password:="SecondPW"
    If Err.Number Then
      MsgBox "You're toast!"
      Exit Sub
    End If
  End If

  On Error GoTo 0

  ' carry on

 

[tiredofit]

Thanks for the suggestions.