problems using Filesearch object when files have a password

[Tim W]

Hi All-
I'm having a strange problem when using the Filesearch object in an Excel
macro. I'm using Excel 2003 sp2 on WinXP pro. If there any files in the
array that Filesearch returns that have a password to open or a
write-reservation password, the macro won't open the file & stops with a
run-time error 1004. I've got error-handling in place but it doesn't seem to
work. Here's a code sample:

Sub fstest()
Dim fslist As Object, wbCheck As Workbook, intIndex As Integer
On Error Resume Next
Set fslist = Application.FileSearch
With fslist
.LookIn = "c:\data files\"
.Filename = "*.xls"
.SearchSubFolders = True
If .Execute > 0 Then
For intIndex = 1 To .FoundFiles.Count
Set wbCheck = Workbooks.Open(.FoundFiles(intIndex), _
UpdateLinks:=0, Password:="", WriteResPassword:="")
On Error GoTo 0
If wbCheck Is Nothing Then
Else
Debug.Print wbCheck.Name
wbCheck.Close savechanges:=False
End If
Next intIndex
End If
End With
End Sub

It keeps breaking on the line of code that opens the workbook. It just stops
there & tells me I have an incorrect password. Please note that this code
works fine if I don't use the Filesearch object. If I just feed it a
filename in a string or in a string literal, it works fine when there's a
password involved.

I've tried this with a number of variations in error handling, nothing
works. I've made sure that I have "Break on unhandled errors" selected in
the VBE options. I've been all over the web & the MS Kbase and I can't find
anything about this. There seems to be something buggy going on here but I'm
not sure if it's my code or an Excel "feature."


Anyone else seen this? I've been beating my head against the wall for a few
days with this. I suppose I could use the dir() method but I like the ease
of use of the Filesearch object.

TIA,
Tim W
[email protected]

[Tom Ogilvy]

Its your error Tim. You turn on error handling, then the first time an error
occurs, you turn it off and it remains off.

Sub fstest()
Dim fslist As Object, wbCheck As Workbook, intIndex As Integer
Set fslist = Application.FileSearch
With fslist
.LookIn = "c:\data files\"
.Filename = "*.xls"
.SearchSubFolders = True
If .Execute > 0 Then
For intIndex = 1 To .FoundFiles.Count
On Error Resume Next
Set wbCheck = Workbooks.Open(.FoundFiles(intIndex), _
UpdateLinks:=0, Password:="", WriteResPassword:="")
On Error GoTo 0
If wbCheck Is Nothing Then
Else
Debug.Print wbCheck.Name
wbCheck.Close savechanges:=False
End If
Next intIndex
End If
End With
End Sub

You should always only use On Error Resume Next on the smallest block of
code where you need it. Otherwise you will observe "strange problems"

--
Regards,
Tom Ogilvy

"Tim W" wrote:

> Hi All-
> I'm having a strange problem when using the Filesearch object in an Excel
> macro. I'm using Excel 2003 sp2 on WinXP pro. If there any files in the
> array that Filesearch returns that have a password to open or a
> write-reservation password, the macro won't open the file & stops with a
> run-time error 1004. I've got error-handling in place but it doesn't seem to
> work. Here's a code sample:
>
> Sub fstest()
> Dim fslist As Object, wbCheck As Workbook, intIndex As Integer
> On Error Resume Next
> Set fslist = Application.FileSearch
> With fslist
> .LookIn = "c:\data files\"
> .Filename = "*.xls"
> .SearchSubFolders = True
> If .Execute > 0 Then
> For intIndex = 1 To .FoundFiles.Count
> Set wbCheck = Workbooks.Open(.FoundFiles(intIndex), _
> UpdateLinks:=0, Password:="", WriteResPassword:="")
> On Error GoTo 0
> If wbCheck Is Nothing Then
> Else
> Debug.Print wbCheck.Name
> wbCheck.Close savechanges:=False
> End If
> Next intIndex
> End If
> End With
> End Sub
>
> It keeps breaking on the line of code that opens the workbook. It just stops
> there & tells me I have an incorrect password. Please note that this code
> works fine if I don't use the Filesearch object. If I just feed it a
> filename in a string or in a string literal, it works fine when there's a
> password involved.
>
> I've tried this with a number of variations in error handling, nothing
> works. I've made sure that I have "Break on unhandled errors" selected in
> the VBE options. I've been all over the web & the MS Kbase and I can't find
> anything about this. There seems to be something buggy going on here but I'm
> not sure if it's my code or an Excel "feature."
>
>
> Anyone else seen this? I've been beating my head against the wall for a few
> days with this. I suppose I could use the dir() method but I like the ease
> of use of the Filesearch object.
>
> TIA,
> Tim W
> [email protected]
>
>
>

[Tim W]

Thanks, Tom, that cleared it up. I tried some other error handling, where I
wrote an actual error handler into the code, "On Error goto Err_Handler"
etc.. That didn't seem to trap the errors and I can't figure out why but
this works so I'm not going to argue. Thanks again!
Tim

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Its your error Tim. You turn on error handling, then the first time an
> error
> occurs, you turn it off and it remains off.
>
> Sub fstest()
> Dim fslist As Object, wbCheck As Workbook, intIndex As Integer
> Set fslist = Application.FileSearch
> With fslist
> .LookIn = "c:\data files\"
> .Filename = "*.xls"
> .SearchSubFolders = True
> If .Execute > 0 Then
> For intIndex = 1 To .FoundFiles.Count
> On Error Resume Next
> Set wbCheck = Workbooks.Open(.FoundFiles(intIndex), _
> UpdateLinks:=0, Password:="", WriteResPassword:="")
> On Error GoTo 0
> If wbCheck Is Nothing Then
> Else
> Debug.Print wbCheck.Name
> wbCheck.Close savechanges:=False
> End If
> Next intIndex
> End If
> End With
> End Sub
>
> You should always only use On Error Resume Next on the smallest block of
> code where you need it. Otherwise you will observe "strange problems"
>
> --
> Regards,
> Tom Ogilvy
>
> "Tim W" wrote:
>
>> Hi All-
>> I'm having a strange problem when using the Filesearch object in an Excel
>> macro. I'm using Excel 2003 sp2 on WinXP pro. If there any files in the
>> array that Filesearch returns that have a password to open or a
>> write-reservation password, the macro won't open the file & stops with a
>> run-time error 1004. I've got error-handling in place but it doesn't seem
>> to
>> work. Here's a code sample:
>>
>> Sub fstest()
>> Dim fslist As Object, wbCheck As Workbook, intIndex As Integer
>> On Error Resume Next
>> Set fslist = Application.FileSearch
>> With fslist
>> .LookIn = "c:\data files\"
>> .Filename = "*.xls"
>> .SearchSubFolders = True
>> If .Execute > 0 Then
>> For intIndex = 1 To .FoundFiles.Count
>> Set wbCheck = Workbooks.Open(.FoundFiles(intIndex), _
>> UpdateLinks:=0, Password:="", WriteResPassword:="")
>> On Error GoTo 0
>> If wbCheck Is Nothing Then
>> Else
>> Debug.Print wbCheck.Name
>> wbCheck.Close savechanges:=False
>> End If
>> Next intIndex
>> End If
>> End With
>> End Sub
>>
>> It keeps breaking on the line of code that opens the workbook. It just
>> stops
>> there & tells me I have an incorrect password. Please note that this code
>> works fine if I don't use the Filesearch object. If I just feed it a
>> filename in a string or in a string literal, it works fine when there's a
>> password involved.
>>
>> I've tried this with a number of variations in error handling, nothing
>> works. I've made sure that I have "Break on unhandled errors" selected in
>> the VBE options. I've been all over the web & the MS Kbase and I can't
>> find
>> anything about this. There seems to be something buggy going on here but
>> I'm
>> not sure if it's my code or an Excel "feature."
>>
>>
>> Anyone else seen this? I've been beating my head against the wall for a
>> few
>> days with this. I suppose I could use the dir() method but I like the
>> ease
>> of use of the Filesearch object.
>>
>> TIA,
>> Tim W
>> [email protected]
>>
>>
>>