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
ResumeHe
'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

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

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
ResumeHe
'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

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" wrote in message
...
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 wrote:

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
ResumeHe
'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

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

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" wrote in message
...
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 wrote:

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
ResumeHe
'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

--

Dave Peterson

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


"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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
ResumeHe
'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

--

Dave Peterson