Searching for files that contain ???

[Ken Loomis]

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")

The problem is it can take about 45 minutes to run on my Windows 98 system
and I was just wondering if there is a faster way to do this or a way to
speed up this search. I have no control over where the the user saves the
files I need to find, except that they are supposed to be in the My
Documents folder. If they aren't I am not as concerned about them. I can
handle that manually on a user by user basis. I surely do not want to slow
things down any further by searching the entire hard drive, unless, of
course, that would actually make this search go faster.

Just a note that the time it takes this search to complete is never very
consistent. Sometimes it takes 20 minutes and others it take 45 when run on
the same system with no changes to the files or file structure.

Thanks for any ideas.

Ken

 

[Sean Connolly]

Hi Ken,

Just quickly and for (my) clarity, you are searching for the string
"BuildStreetsReports" anywhere in the the body of a file or the file's
properties. If most peoples' "My Documents" are anything like mine, that
*will* take a long time as my understanding is that it will search through
every cell of every worksheet of every workbook in the folders you have
supplied.

Do you really have to do that or is it possible that the workbook(s) that
you're looking for are actually *named* with some sort of combination that
includes "BuildStreetsReports"? (e.g. "John's BuildStreetsReports July
2005.xls"). If that is the case you might consider using .FileName and
..FileType in preference to .TextOrProperty - for example ...

..FileType = msoFileTypeExcelWorkbooks
..FileName = "*BuildStreetsReports*"

One other suggestion: For purposes of comparison, have you manually run this
same search, say from Windows Explorer Search, using the same parameters? How
long did that take to fully complete?

Not sure if it does, but HTH, Sean.

 

[Ken Loomis]

There is no guarantee that any of the users will have followed any sort of
consistent naming structure for the file names, so I have to look for text
in the files.

And there are two versions of workbooks that this project creates. One is
the final report with all the code left intact. The other is a distribution
copy that has all the code removed.

"BuildStreetsReports" is the name of the main subroutine in the main VBA
module. When a distribution copy is saved, that module name (and all the
rest of the VBA code) is removed before the workbook is saved.

So when I use that Application.FileSearch code, it finds all the files in
the "My Documents" folder that still have the VBA code. And, since we now
realize that all past reports can actually be distribution copies, I strip
out all the code from those files and save them.

In the latest iteration of this project, I solve this problem from happening
in the future by trapping the Save event and only allowing the user to save
a distribution copy. And I suggest a standard naming convention so I can
more easily find of similar files if needed. But at least each user will
only have one file with VBA code.

Thanks for your comments.

Ken