Page 312 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

.LastModified = msoLastModifiedLastMonth
iCount = .Execute
strMessage = Format(iCount, “0 “"Files Found""“)

For Each vaFileName In .FoundFiles
strMessage = strMessage & vbCr & vaFileName
Chapter 13
Next vaFileName
MsgBox strMessage
End With
End Sub

The PropertyTests collection operates independently of any settings in the File Search task
pane. This collection doesn’t recognize any conditions in the task pane and it doesn’t change
the settings. If you add tests to the PropertyTests collection, they are retained until a NewSearch
is executed.
The Add method of the PropertyTests collection adds the new tests, which are specified by
assigning a string to the Name parameter that is identical to the string that appears in the
Property combo box in the File Search task pane.

The FileType property for FileSearch can be used to limit your search to a specific file type
or multiple file types. The FileTypes collection allows you to specify multiple file types. The
following example will return all Microsoft Word and Excel files in the specified folders:

Sub FindWordandExcelFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim stMessage As String
Dim i As Long
Dim iCount As Long
Set FS = Application.FileSearch
strPath = “C:\GSC\Accounting"
With FS
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.FileTypes.Add msoFileTypeWordDocuments
.LookIn = strPath
.SearchSubFolders = True
.LastModified = msoLastModifiedAnyTime
iCount = .Execute
stMessage = Format(iCount, “0 “"Files Found""“)
For Each vaFileName In .FoundFiles
stMessage = stMessage & vbCr & vaFileName
Next vaFileName

MsgBox stMessage
End With
End Sub

286
Part 4: Advanced VBA