The FileSearch Object | Programming Microsoft Access 2000 (Microsoft Programming Series)

You use the FileSearch object model (shown in Figure 8-1) to integrate file searches into your applications. You can search the hard drives of a computer and any drives on LAN-linked computers. This object exposes the functionality of the Open and the Find dialog boxes. As Figure 8-1 shows, the FoundFiles object and PropertyTests collection are hierarchically dependent on FileSearch.

Figure 8-1. The FileSearch object is one of the shared Office objects.

There are two basic ways to specify a file search, and each approach corresponds to options in the Open dialog box:

You can designate a single criterion (a filename or pattern, a file type, or a path).

You can designate multiple search criteria programmatically using the PropertyTests collection.

With the second approach, you specify arguments that serve as input to the Open dialog box. You can use this dialog box to specify multiple search criteria and rules for concatenating them, such as And and Or operators. Use FoundFiles to enumerate the return set from either approach.

The FileSearch object has two methods: NewSearch and Execute. The NewSearch method resets all FileSearch properties to their default values. You can then edit the properties that require special values for a particular file search. If you do not invoke NewSearch at the beginning of a search specification, your new search inherits its property settings from the previous search.

You invoke the Execute method to launch a file search after you specify it. This method can take several arguments that control the arrangement of filenames in the FoundFiles object and that control whether to update the file index before conducting a new search. The return value from this method is the number of filenames that match the search specification.

Conducting a Basic File Search

Many FileSearch properties permit flexible search specifications. The simple code sample below specifies a search and retrieves its return set. It creates an instance of the FileSearch object by using the FileSearch property of the Application object. Then it restores all FileSearch property settings to their default values by invoking the NewSearch method. Next, it assigns the LookIn and FileName properties, which specify where to look and what to look for. The test machine for this search includes a series of .mdb files with names such as Chapter1 and Chapter2. The SearchSubFolders property accepts a Boolean value that indicates whether to restrict the search to the current folder or extend it to subfolders of the LookIn property setting.

 Sub FileSearch1() 'Search in My Documents folder and its subfolders 'for Chapter*.mdb. With Application.FileSearch 'Start a new search.     .NewSearch 'Set search criteria.     .LookIn = "C:\My Documents"     .FileName = "Chapter*.mdb"     .SearchSubFolders = True End With With Application.FileSearch 'Execute the Search.     If .Execute() > 0 Then         MsgBox "There were " & .FoundFiles.Count & _             " file(s) found." 'Display names of all found files.         For i = 1 To .FoundFiles.Count             MsgBox .FoundFiles(i)         Next i    Else 'If no files found, say so.         MsgBox "There were no files found."    End If End With End Sub

After creating the specification for the search, the procedure invokes the Execute method for the FileSearch object. This method has a return value that indicates the number of files that meet the search criteria. If the value is 0, the criteria yield no matching filenames and the procedure issues a message indicating that no files were found. If the criteria yield one or more matching files, the procedure displays the Count property of the FoundFiles object before presenting each name in FoundFiles.

Sorting the Return Set

The following sample sorts the return set from a search by file size. The first two parameters for the Execute method designate the sort criterion and order, respectively. The constant names for the first parameter indicate the variable on which to sort the returned filenames. These constants are: msoSortByFileName, msoSortByFileType, msoSortByLastModified, and msoSortBySize. The Execute method's second parameter specifies either ascending or descending order. The sample designates a search sorted by file size in descending order. This differs from the previous sample, which returned results in the default ascending order based on filename.

 Sub FileSearch2() Dim sngMB As Single 'Search in My Documents folder and its subfolders 'for Chapter*.mdb. With Application.FileSearch 'Start a new search.     .NewSearch 'Set search criteria.     .LookIn = "C:\My Documents"     .FileName = "Chapter*.mdb"     .SearchSubFolders = True End With With Application.FileSearch 'Return found files in descending order by file size.     If .Execute(msoSortBySize, msoSortOrderDescending) > 0 Then         MsgBox "There were " & .FoundFiles.Count & _             " file(s) found."         For i = 1 To .FoundFiles.Count 'Compute file size in MB and display with filename.             sngMB = FileLen(.FoundFiles(i)) / (1024 ^ 2)             MsgBox .FoundFiles(i) & vbCrLf & vbTab & _                 "Filesize (MB): " & Round(CDec(sngMB), 3)         Next i     Else 'If no files found, say so.         MsgBox "There were no files found."     End If End With End Sub

The message box that displays the return set shows the file sizes and filenames. You pass the FoundFiles object to the FileLen function to determine the size of a file. The file sizes are rounded to the nearest 1/1000 of a MB.

NOTE
The Visual Basic for Applications (VBA) Round function is new in VBA 6. To derive consistent results with this function, you should first pass its argument to the CDec function. The sample above uses this syntax. (See Chapter 1 for information on the CDec function.)

Searching Based on File Contents

Even with a simple search, such as the two previous samples, you can selectively search for specific text in the document or its DocumentProperty object. The sample below does this. You use the FileSearch object's TextOrProperty property to target a text string in the file's body or its Properties collection. Notice that you can specify folders on remote computers using the Uniform Naming Convention (UNC). (\\cab233\c\cab points to the cab folder in the share named c of a computer named cab233.)

 Sub FileSearch3() Dim sngStart As Double, sngEnd As Double Dim i As Integer 'Search in cab folder on linked computer 'for files containing CAB. With Application.FileSearch 'Start a new search.     .NewSearch 'Set search criteria.     .LookIn = "\\cab233\d\cab\"     .SearchSubFolders = False 'When searching for text consider 'restricting the files you search. '*.* takes 300 seconds, but 'msoFileTypeWordDocuments takes 22 seconds. '    .FileName = "*.*"     .FileType = msoFileTypeWordDocuments     .TextOrProperty = "CAB" End With With Application.FileSearch 'Execute the search.     sngStart = Now     If .Execute() > 0 Then         sngEnd = Now         Debug.Print DateDiff("s", sngStart, sngEnd)         MsgBox "There were " & .FoundFiles.Count & _             " file(s) found." 'Display names of all found files.         For i = 1 To .FoundFiles.Count             MsgBox .FoundFiles(i)         Next i     Else 'If no files found, say so.         MsgBox "There were no files found."     End If End With End Sub

Some file searches can be lengthy. By specifying a restrictive FileSearch property, you can dramatically improve the performance of the Execute method. For example, the sample above finds all Word documents in a folder that contain a specific string. By using the msoFileTypeWordDocuments constant for the FileType property, the sample restricts the search to just Word document files. You might be tempted to specify *.* for the FileName property and then filter the returned results, but this would seriously impair performance. For the sample files in the cab folder on the cab233 computer, the difference is 22 seconds for the msoFileType constant vs. 300 seconds for the *.* FileName specification. (Notice that it takes just three lines to time the operation—one line before the Execute and two more immediately after it.)

Specifying Multiple Search Criteria

The advanced search format lets you specify multiple search criteria for your return set in the FoundFiles object. You use the Add method two or more times to specify multiple criteria for the PropertyTests collection. Your individual criterion specifications must include Name and Condition settings.

The Add method can specify a Connector setting as well as one or two Value settings. The Add method's Condition setting determines whether a criterion requires Value settings. You view the members of the MsoCondition class to see all the available options. (Figure 8-2 shows an excerpt.) Your Connector settings can take one of two values to specify how to combine a criterion with other criteria. This setting enables And or Or operators for merging a criterion with other search criteria. You use Or to treat the criterion separately, and you use And to combine the designated criterion with others. And is the default setting. The Condition, Value, and Connector settings together offer the same functionality as the Find dialog box.

You can enumerate PropertyTests members using a For…Each loop. Each member constitutes a unique search criterion. The Name property identifies the criterion as you enumerate them.

click to view at full size.

Figure 8-2. You use the members of the msoCondition enumeration group to specify conditions for advanced criteria in the PropertyTests collection of the FileSearch object.

The last FileSearch sample, shown below, has three segments. The first segment specifies the criteria after setting a reference to a FileSearch object. The sample targets all database files between two dates. It shows the correct syntax for invoking the Add method for the PropertyTests collection. The first criterion designates a database type. The second criterion denotes files last modified between January 1, 1996, and June 30, 1999. The msoConnectorOr setting indicates that files must meet both criteria separately to be in the return set. You need not specify a Connector property for the second criterion because it adopts the default msoConnectorAnd value. Before displaying the return set, the procedure enumerates the PropertyTests members in its second segment. The final segment displays the return set.

 Sub Search4() Dim fs As FileSearch, mystring As String Dim i As Integer     Set fs = Application.FileSearch      'Set lookin and subfolder properties.     With fs         .NewSearch         .LookIn = "c:\My Documents"         .SearchSubFolders = False     End With     'Set a pair of property conditions.     With fs.PropertyTests         .Add name:="Files of Type", _             Condition:=msoConditionFileTypeDatabases, _             Connector:=msoConnectorOr         .Add name:="Last Modified", _             Condition:=msoConditionAnytimeBetween, _             Value:="1/1/1996", SecondValue:="6/30/1999"     End With     'Display property tests.     For i = 1 To fs.PropertyTests.Count         With Application.FileSearch.PropertyTests(i)         mystring = "This is the search criteria: " _             & " The name is: " & .name & ". The condition is: " _             & .Condition         If .Value <> "" Then             mystring = mystring & ". The value is: " & .Value             If .SecondValue <> "" Then                 mystring = mystring _                     & ". The second value is: " _                     & .SecondValue & ", and the connector is" _                     & .Connector             End If         End If         MsgBox mystring         End With     Next i 'Display return set from property tests.     With fs 'Execute the search.         If .Execute() > 0 Then             MsgBox "There were " & .FoundFiles.Count & _                 " file(s) found." 'Display names of all found files.             For i = 1 To .FoundFiles.Count                 MsgBox .FoundFiles(i)             Next i         Else 'If no files found, say so.             MsgBox "There were no files found."         End If     End With End Sub