Microsoft Office Tutorials and References
In Depth Information
The FileName property allows you to narrow your search more than the FileType constant. If
you use both FileType and FileName , the FileName property overrides the FileType property.
You can also search the text contained in the properties of a file or in the body of the file itself
by assigning the text to the Te xtOrProperty property of the FileSearch object.
The Execute method of the FileSearch object carries out the search and adds an object repre­
senting each file to the FoundFiles collection. The Execute method also returns a value that is
the number of files found.
You use the FoundFiles collection to access the names, including the path, of the files found.
The code from the previous example uses a For Each…Next loop to process the list, adding
each name to strMessage , separated by a carriage return.
Limiting the File Search
There are times when you need to reopen a file from several months ago, but you don’t
remember its exact location. We have all run into this problem at one time or another. The
File Search feature in Excel can accomplish this task. However, you can use the PropertyTests
collection in your VBA procedure to automate the search process.
For example, the Garden Supply Company has decided to reorganize their historical files.
All files created in the previous year will be moved to an Archive folder on the company’s file
server. The following procedure was designed to search for files that were modified in the
previous month:
Sub FindLastMonthFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim strMessage As String
Dim i As Long
Dim iCount As Long
Set FS = Application.FileSearch
strPath = “C:\GSC\Accounting"
With FS
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
With .PropertyTests
For i = .Count To 1 Step -1
.Remove i
Next i
.Add Name:="Files of Type", _
Condition:=msoConditionFileTypeExcelWorkbooks
End With
Search JabSto ::




Custom Search