Problem with File.Search

[Ken Loomis]

This is driving me crazy.

I use the following to search for files that are ".xls" files and contain a
sub name in the VBA:

MyFilePath is the path to the My Documents folder on whatever system this
runs on

StartTime = Time
With Application.FileSearch
.NewSearch
.FileName = "*.xls"
.LookIn = MyFilePath
.SearchSubFolders = True
.TextOrProperty = "BuildStreetsReports"
.MatchTextExactly = True
.Execute
EndTime = Time
MsgBox ("Done searching. It took " & (EndTime - StartTime) * 24 * 60
& " minutes")


On my Windows XP system that search takes les than 20 seconds.

But when I run it on my Windows 98 system, it can take up to 10 minutes and
sometimes just hangs and won't come back at all, even if I let it run for an
hour.

I am beginning to think there is something wrong with Windows or my Office
installation.

Has anyone else ever had this kind of problem with FileSearch, or have any
ideas or suggestions?

 

[William Benson]

Just a guess Ken but could have something to do with the formatting of the
respective hard drives ... Win98 is Fat32, and your "faster" drive may be
NTFS?

I have very little experience with this.

 

[Jim Cone]

Ken,

I've seen comments in the programming group to the
effect that FileSearch is not always reliable.
See Myrna Larson's comments here as one example...
http://makeashorterlink.com/?C3145206B

The FileSystemObject code, part of the Windows Script Host
included with the Windows operating system (after Windows 95)
seems to be rock solid dependable and can be freely intermixed
with VBA code. For what it's worth, the following is code that
searches for files in the top folder and all sub-folders.
The results are listed on the active sheet.


'------------------------
'Microsoft Windows Script 5.6 Documentation
'http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp

Option Explicit
Option Compare Text

Sub ListFoldersAndSubFolderAndFiles()
Jim Cone - San Francisco, USA - May 24, 2005/July,02, 2005
'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll) '***
'List all files and folders in the specified folder.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim lngNum As Long

'Specify the folder...
strPath = "C:\Documents and Settings"
'Specify the file to look for...
strName = "*.xls"
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngNum = 2

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
Cells(lngNum, 2) = objFile.Path
lngNum = lngNum + 1
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
DoTheSubFolders objFolder.SubFolders, lngNum, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'------------------------

Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef lngN As Long, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
Cells(lngN, 2).Value = scrFile.Path
lngN = lngN + 1
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count > 0 Then
DoTheSubFolders scrFolder.SubFolders, lngN, strTitle
End If
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'-------------------------------------


This is driving me crazy.
I use the following to search for files that are ".xls" files and contain a
sub name in the VBA:
MyFilePath is the path to the My Documents folder on whatever system this
runs on

StartTime = Time
With Application.FileSearch
.NewSearch
.FileName = "*.xls"
.LookIn = MyFilePath
.SearchSubFolders = True
.TextOrProperty = "BuildStreetsReports"
.MatchTextExactly = True
.Execute
EndTime = Time
MsgBox ("Done searching. It took " & (EndTime - StartTime) * 24 * 60
& " minutes")


On my Windows XP system that search takes les than 20 seconds.
But when I run it on my Windows 98 system, it can take up to 10 minutes and
sometimes just hangs and won't come back at all, even if I let it run for an
hour.
I am beginning to think there is something wrong with Windows or my Office
installation.
Has anyone else ever had this kind of problem with FileSearch, or have any
ideas or suggestions?

 

[William Benson]

Jim, can filesystemobject search in the file for text, I thought Ken was
needing to do that ...?

 

[Jim Cone]

William,

It has a "TextStream Object" with which you can read or write text files.
I don't know of any way for it to read an Excel file.

Regards,
Jim Cone
San Francisco, USA


Jim, can filesystemobject search in the file for text, I thought Ken was
needing to do that ...?

Ken,
I've seen comments in the programming group to the
effect that FileSearch is not always reliable.
See Myrna Larson's comments here as one example...
http://makeashorterlink.com/?C3145206B

The FileSystemObject code, part of the Windows Script Host
included with the Windows operating system (after Windows 95)
seems to be rock solid dependable and can be freely intermixed
with VBA code. For what it's worth, the following is code that
searches for files in the top folder and all sub-folders.
The results are listed on the active sheet.

-snip-

 

[Ken Loomis]

Jim,

I'm not an expert at this stuff, but I'd be glad to go thru that code to
make it work in my project.

But, at first glance, it seems to be searching thru the file names, not at
text in the files.

In my project, I have to look inside the files for a subroutine name in the
VBA.

Will this routine you suggested do that?

Ken

 

[Jim Cone]

Ken,

Sorry, wild goose chase - the code will not search inside xl files.

Regards,
Jim Cone
San Francisco, USA


Jim,
I'm not an expert at this stuff, but I'd be glad to go thru that code to
make it work in my project.
But, at first glance, it seems to be searching thru the file names, not at
text in the files.
In my project, I have to look inside the files for a subroutine name in the
VBA.
Will this routine you suggested do that?
Ken

 

[Ken Loomis]

Thanks for checking.

I just ran a test with a batch of 50 test files scattered throughout my "My
Documents" folder.

The first time I ran it, it took 45 minutes to search all the files and find
the ones that contain the target subroutine name.

It takes less than 2 minutes to open all 50 files, delete the VBA code and
resave them

Then, I deleted all the modified files. And reloaded the same test files
from CD. I restarted the computer and ran the routine again. This time, it
took less than 5 minutes to search thru the entire My Documents folder and
find all those same files. And, again, less than 2 minutes to delete the VBA
code from all 50 of them

I sure wish I could figure out the inconsistency in the times it takes. Same
computer, same programs running (only Excel), same everything, but I keep
getting these disparaging run times.

Go figure.

Thanks to all for all the help.

Ken

 

[William Benson]

Going out on a limb, I wonder: If FileSearch caches results during prior
searches to make future searches faster.

I have noted the same behavior in the Search feature (Start Menu).

 

[Ken Loomis]

That could be, but I would have thought that if that were the case, the
slowest search would be right after I restart the system.

And, I am not making that many changes to the file structure in My Documents
between tests.

So far, I can't tell if the last search before a restart or the first search
after, is the fastest. It really seems random as to how long it takes.

I just ran another test without restarting and this time it took just over 4
minutes.

At this point, I just think I need to tell the users to start this before
they go to lunch.

Thanks.
Ken

 

[Tom Ogilvy]

As I recall, the cache is written to disk. In Windows 9.x, there used to be
a utility that indexed files in the background (at least there was an option
to have it do that, but the default was that it was turned on).

 

[DM Unseen]

I always was under the impression Office filesearch could use windows
indexing service. This is standard service for Win2K/WinXP but i'm not
sure about Win98(I suspect not). I think indexing service really shines
when looking up words inside documents.

Dm Unseen