Handling incorrect password on file open

[ElvisSteel]

Hi,

I have a macro that prompts the user for an Excel workbook and displays the file open dialog as follows...

  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

 

[Andrew Poulsom]

Please post the code that opens the file.

 

[ElvisSteel]

Hi Andrew,

This is the code I am using...

  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

 

[GTO]

Not well tested, but maybe something like:

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

 

[ElvisSteel]

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

If Err.Code = 1004 then
 MsgBox etc etc
Else
 Continue processing
Endif

Thanks anyway

Steve

 

[GTO]

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

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:

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