Page 309 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Files

Note Be sure to clear your previous search settings by executing the NewSearch method
Chapter 13
each time the FileSearch method is used. The FileSearch properties are retained in the
Excel application during your current session in Excel.

Sub FindAccountingExcelFiles()
Dim FS As Office.FileSearch
Dim strPath As String
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
.FileType = msoFileTypeExcelWorkbooks
.LastModified = msoLastModifiedAnyTime
iCount = .Execute
strMessage = Format(iCount, “0 “"Files Found""“)

For Each vaFileName In .FoundFiles
strMessage = strMessage & vbCr & vaFileName
Next vaFileName

MsgBox strMessage
End With
End Sub
The FileType property determines which file extensions will be included in the search criteria.
The msoFileTypeExcelWorkbooks constant directs the search to include all the Excel file exten­
sions: .xls, .xlt, .xlm, .xlc, and .xla. Table 13-2 lists the other constants available.
Table 13-2. List of File Types and Their Values
msoFileType Constants Value
msoFileTypeAllFiles 1
msoFileTypeOfficeFiles 2
msoFileTypeWordDocuments 3
msoFileTypeExcelWorkbooks 4
msoFileTypePowerPointPresentations 5
msoFileTypeBinders 6
msoFileTypeDatabases 7
continued

283
Part 4: Advanced VBA