Page 316 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Sub SetupSearchFoldersCollection()
Dim FS As FileSearch
Dim SS As SearchScope
Dim SF As ScopeFolder
Dim sfSubFolder As ScopeFolder
Dim strMessage As String
Chapter 13
Dim i As Long
Set FS = Application.FileSearch
For i = FS.SearchFolders.Count To 1 Step -1
FS.SearchFolders.Remove i
Next i
For Each SS In FS.SearchScopes
Select Case SS.Type
Case msoSearchInMyComputer
For Each SF In SS.ScopeFolder.ScopeFolders
Select Case SF.Path
Case “C:\"
For Each sfSubFolder In SF.ScopeFolders
If UCase(Left(sfSubFolder.Name, 6)) = _
“PRODUCT” Then
sfSubFolder.AddToSearchFolders
End If
Next sfSubFolder
Exit For
End Select
Next SF
Exit For
End Select
Next SS
Search_SearchFolders
End Sub
The example empties the SearchFolders collection and then searches through the SearchScopes
and ScopeFolders collections to locate the C drive. The code then evaluates the Name property
of each ScopeFolder in the root directory of the C drive to determine if the name begins with
Product. Since the comparison of text is case sensitive, the Name property is converted to
uppercase.

The previous example is dependent on the Search_SearchFolders procedure. When the code
finds a matching directory, it uses the AddToSearchFolders method of the ScopeFolder object
to add the object to the SearchFolders collection. The Search_SearchFolders routine is listed here:
Sub Search_SearchFolders()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim strMessage As String
Dim iCount As Long

290
Part 4: Advanced VBA