Support Article: 305342 - Workarounds provided DO NOt work as expe
 

Article: 305342
OS: Windows 2000
Product: Office XP professional (sp-3)

Issue: The workarounds provided do not work when searching a USB hard drive.
In this case the hard drive has been manually changed to €œK:€�. I am unable
to confirm if the workarounds are valid on a mapped network location.

The 2 workarounds provided in the article a

The article provides 2 workarounds
Method 1: Disable Indexing Service
Method 2: Change search string format
To avoid the problem that is mentioned in the "Symptoms" section, you can
change the .FileExtension search string format that was used in the VBA code
to the *.FileExtension format.

Sub count_htm_files()
With Application.FileSearch
..FileName = "*.htm"
..LookIn = "c:\"
..Execute MsgBox .FoundFiles.Count
End With
End Sub

How to recreate the problem:
Using a computer running Windows 2000 and Office XP (sp3) with a USB hard
drive set as €œK:€� and indexing disabled, a new workbook and save it to the
root of drive C: and the root of drive K. Both copies should be given the
same name (ie: €œC:\test.xls€� and €œK:\test.xls€�). After closing the newly
created file, create a new workbook and then create a module in VBE. Next,
Copy the code, as written in method 2, to the module and then change it as
follows:

Sub count_htm_files()
With Application.FileSearch
..FileName = "\test.xls"
..LookIn = "K:\"
..Execute MsgBox .FoundFiles.Count
End With
End Sub

When the code is executed, the file in not found but if you change the code
[€œ.LookIn = "K:\"]€� to [.LookIn = "C:\"], the code returns €œ1€� (as expected).

Other observations:
The FileSearch object fails on drive K: with any variation to the €œ.lookin€�
and .filename parameters.

In some cases, using the code as outlined above will intermittently cause
Excel to stop responding, requiring for the instance to be terminated via the
task manager.