Handling incorrect password on file open

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
Hi,

I have a macro that prompts the user for an Excel workbook and displays the file open dialog as follows...
Code:
  NewFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*", Title:="Please select file for import")
  If NewFileName = False Then
   MsgBox ("No file selected" & vbCrLf & vbCrLf & "No data has been imported"), vbCritical, "Import Abandoned"
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   Exit Sub
  End If

' Code to handle input file is here

How do I detect an incorrect password so I can pop up a message and get them to try again?

Thanks
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Andrew,

This is the code I am using...
Code:
  NewFileName = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*", Title:="Please select file for import")
  If NewFileName = False Then
   MsgBox ("No file selected" & vbCrLf & vbCrLf & "No data has been imported"), vbCritical, "Import Abandoned"
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   Exit Sub
  End If
  Workbooks.Open FileName:=NewFileName, UpdateLinks:=False

' Check Invalid password here?  

' Check if a valid file
  If Left(ActiveWorkbook.Name, 23) <> "New Business Calculator" Then
' Not a valid New Business Calculator sheet, so stop import
   ActiveWindow.Close
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
   MsgBox "You have selected the following file..." & vbCrLf & vbCrLf & NewFileName & vbCrLf & vbCrLf & "...this file is not valid for use with this process", vbCritical, "Invalid Data File"
   Exit Sub
  End If
...and I am assuming that I can check for a valid poassword where indicated.
Regards
Steve
 
Upvote 0
Not well tested, but maybe something like:
Rich (BB code):
Option Explicit
    
Sub exa()
Dim wb As Workbook
Dim sFileName As String
Dim lErrCounter As Long
    
    sFileName = Application.GetOpenFilename("Excel Pre-2007 Workbooks (*.xls), *.xls", , "", , False)
    If Not sFileName = "False" Then
        On Error GoTo WrongPWD
        Set wb = Workbooks.Open(Filename:=sFileName, UpdateLinks:=False)
        On Error GoTo 0
    End If
Exit Sub
WrongPWD:
    
    If Err.Number = 1004 Then
        lErrCounter = lErrCounter + 1
        If Not lErrCounter >= 3 Then
            If MsgBox("You entered an incorrect password or left it blank.  Do you want to" & vbCrLf & _
                      "try again?", vbQuestion Or vbYesNo, vbNullString) = vbYes Then
                
                Resume
            Else
                'any cleanup
                Exit Sub
            End If
        Else
            MsgBox "Yeh, okay, three shots is enough!", 0, vbNullString
            Exit Sub
        End If
    Else
        MsgBox "An unspecified error occurred.", vbCritical, vbNullString
        'any cleanup
        Exit Sub
    End If
End Sub
Hope that helps,

Mark
 
Upvote 0
Mark,

Thanks very much, that works nicely

Just one general question...

Does error handling have to be triggered by "On Error ..." or can you do a specific test without using On Error, such as
Code:
If Err.Code = 1004 then
 MsgBox etc etc
Else
 Continue processing
Endif

Thanks anyway

Steve
 
Upvote 0
Mark,

Thanks very much, that works nicely

Just one general question...

Does error handling have to be triggered by "On Error ..." or can you do a specific test without using On Error, such as
Code:
If Err.Code = 1004 then
 MsgBox etc etc
Else
 Continue processing
Endif

Glad to help Steve. At least thru 2003, there's no such thing as .Code for Err. You can use Err.Number, but you still would need to change On Error to On Error Resume Next, or the error is fatal (code stops).

A simple example to try:
Rich (BB code):
Sub exa()
Dim i As Integer
    
    'On Error Resume Next
    
    i = 100000 '< overflow
    If Err.Number = 6 Then
        MsgBox "We made it here"
    End If
    
End Sub

I would strongly caution against using On Error Resume Next for any more lines of code than aboslutely necessary. It masks errors and will cause you to spend much unnecessary time tracking down some tiny glitch...

Mark
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,234
Members
452,765
Latest member
Erka Gizli

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
Back
Top