trying to trap runtime error when opening file without password

[Tim]

Hi All-
I'm using Excel 2003 on Windows XP Pro. I've got some excel code that
loops through a selection of files, opening them one at a time, running
some code, then closing them. I'm running into a snag when I try to
open a file with a password, the code stops with a run-time error 1004.


If the file is password-protected, I don't want to open it, just skip
over it & go on to the next one. The problem is, my error handler isn't
working. Here's what I have:

Sub AAATEst()
Dim wkbk As Workbook, strFile as String
On Error GoTo Error_Handler
'Begin loop
strFile = "Whatever"
Set wkbk = Workbooks.Open(Filename:=strFile, _
Password:="", WriteResPassword:="")
'do some code here
'do some code here
ResumeHere:
'go to next file
Exit Sub
Error_Handler:
If Err.Number = 1004 Then
MsgBox "File has a password"
Err.Clear
GoTo ResumeHere
Else
MsgBox "Run-time error # " & Err.Number & Chr(13) &
Err.Description
Stop
End If
End Sub

the problem is that the error handler is not tripping when I try to
open the password-protected file. It's just coming up telling me that
the password is not correct. Is there any way to trap the incorrect
password error and just skip over that file?

Sorry if that's confusing, I tried to make it as clear as possible.

Thanks,
Tim

 

[Dave Peterson]

Your code worked fine for me.

An alternative approach:

Sub AAATEst()
Dim wkbk As Workbook, strFile As String

strFile = "book4.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=strFile, _
Password:="", WriteResPassword:="")
On Error GoTo 0

If wkbk Is Nothing Then
'do nothing
Else
'do your real code
End If

End Sub

 

[Tim W]

I can't get it to work. It bombs on the line:
Set wkbk = Workbooks.Open(Filename:=strFile, _
Password:="", WriteResPassword:="")
if I try to open a file with a password. I've tried your variation earlier &
got the same results.

Just to be silly, I tried some completely different code to make sure error
trapping was working & it seems to be working fine for other errors, it just
seems like this just ignores any error handling.

Anyone else got any ideas?
Thanks,
Tim

 

[Dave Peterson]

Maybe...

Inside the VBE.
Tools|Options|General Tab

What do you have checked in the "error trapping" section?

I bet you want "Break on unhandled errors"

 

[Tim W]

Brilliant! it worked perfectly! Dave, thank you so much. You've just solved
a major hurdle for me and saved me a boatload of time & hassle. Thanks
again!

Tim