VBA Ignore Password Prompts when reading in excel files

**DaveNUFC** · 07-14-2011, 02:28 PM

Hi all,

I get a problem opening a password-protected file in a folder with VBA. I want some code which basically just ignores the prompt.
I have tried application.screenupdating = false but this did not work. I have also tried some code which inputs a dummy password then 'on error it goes to 0'....

Please help..?

**Dom83** · 07-14-2011, 02:44 PM

Hi Dave,

There's a haspassword property of the workbook class. Try something like:

    Dim wb As Excel.Workbook
    Set wb = Workbooks.Open("D:\Book1.xls")
    If Not wb.HasPassword Then
        ' ... your code
    End If

Cheers,

Dom

**DaveNUFC** · 07-16-2011, 06:42 AM

Thanks Dom.

The VBA still asked me for a password when it tried to open a password protected excel file.
Basically I output a list of all excel file URLs between certain folders into a master list on excel then the VBA reads all of those excel files and if it has a background sheet called output it will copy that data and paste it onto a master excel document. Problem is some of those sheets do not have a background sheet called master list hence how sometimes I come up against a password protected file.

Is there any kind of code that skips past these prompts and just ignores those files? The 'if not workbook.haspassword' function did not work.

thanks
David

**JBeaucaire** · 07-16-2011, 12:59 PM

I think it's a combination of:

1) Suppress display messages
2) Pass a fake password to every wb opened. If no password protection exists, no problem, if password protection exists, you will get an error if the password is wrong. This is good.
3) Trap the error and "skip" all the other action code that won't work, closing the wb and moving on to the next wb

Like so:

Option Explicit

Sub Test()
Dim wb As Workbook
Dim fname As String
Dim fpath As String

Application.DisplayAlerts = False
On Error GoTo ErrorSkip
fpath = "C:\Temp\"          'remember the final \ in this string
fname = Dir("C:\Temp\" & "*.xls")

Do While Len(fname) > 0
    Set wb = Workbooks.Open(fpath & fname)
    wb.Unprotect "cat"
    MsgBox "No password!"
    
    'other action code here

ErrorSkip:
    wb.Close False
    fname = Dir
Loop

End Sub

I imagine you could also collect the names of the failed files to display later for reference. Something like:

Option Explicit

Sub Test()
Dim wb As Workbook
Dim fname As String
Dim fpath As String
Dim Oops  As String

Application.DisplayAlerts = False
On Error GoTo ErrorSkip
fpath = "C:\Temp\"          'remember the final \ in this string
fname = Dir("C:\Temp\" & "*.xls")

Do While Len(fname) > 0
    Set wb = Workbooks.Open(fpath & fname)
    wb.Unprotect "cat"
    
    'other action code here

ErrorResume:
    wb.Close False
    fname = Dir
Loop

If Len(Oops) > 0 Then MsgBox "The following files were protected:" & vbLf & Oops

Exit Sub

ErrorSkip:
    Oops = Oops & "   " & fname
    GoTo ErrorResume
End Sub