Multiple error handling

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,404
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:

Rich (BB 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:

Rich (BB code):
Resume SomeLine

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

Also does putting:

Rich (BB code):
On Error GoTo 0

reset the error handling status?

Thanks


 
Last edited:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
How about ...

Code:
  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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,368
Messages
5,624,286
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top