Microsoft Office 2000/Visual Basic Programmer's Guide

Creating Reusable File-Search Code

Searching for files is something you may do over and over again in any number of different Office solutions. This makes the FileSearch object a great candidate for encapsulation in a class module that could be used in any Office solution that requires file-searching capabilities.

The ExcelExamples.xls sample file in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM shows one way to use a custom class that uses the FileSearch object. The sample file uses the dialog box in Figure 6.2 to gather information about a search from the user.

Figure 6.2 Dialog Box Used to Gather Custom Search Criteria

The Find Office Files dialog box is shown immediately after executing a search for files with an ".xls" extension in the "c:\my documents" directory. Code behind the Find Matching Files command button uses a global variable named objFileInfo to call the GetFileList method of the custom clsGetFileInfo class as follows:

Sub UpdateFileList()
   ' If the file-search specifications are valid, update
   ' the files contained in the form's combo box with a current
   ' list of matching files.
   
   Dim varFoundFiles   As Variant
   Dim varFile         As Variant
      
   varFoundFiles = objFileInfo.GetFileList
   
   If IsArray(varFoundFiles) Then
      With Me
         .cboMatchingFiles.Clear
         For Each varFile In varFoundFiles
            .cboMatchingFiles.AddItem varFile
         Next varFile
         .cboMatchingFiles.ListIndex = 0
         .lblFilesFound.Caption = CStr(objFileInfo.MatchingFilesFound) _
            & " Matching Files Found:"
      End With
   Else
      MsgBox "No files matched the specification: '" & Me.txtFileSpec & "'"
   End If
End Sub

The UpdateFileList procedure is available in the frmFindFileDialog module in ExcelExamples.xls in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

The class contains several properties used to set the properties of the FileSearch object. It also exposes the GetFileList method that returns an array containing all files that match the specified criteria.

Public Function GetFileList() As Variant
   ' This function returns an array of files that match the criteria
   ' specified by the SearchPath and SearchName properties. If the
   ' SearchSubDirs property is set to True, the search includes
   ' subdirectories of SearchPath.

   Dim intFoundFiles    As Integer
   Dim astrFiles()      As String
   Dim fsoFileSearch    As FileSearch
   
   Set fsoFileSearch = Application.FileSearch
   With fsoFileSearch
      .NewSearch
      .LookIn = p_strPath
      .FileName = p_strName
      .FileType = msoFileTypeAllFiles
      .SearchSubFolders = p_blnSearchSubs
      
      If .Execute(p_intSortBy, p_intSortOrder) > 0 Then
         p_intFoundFiles = .FoundFiles.Count
         ReDim astrFiles(1 To .FoundFiles.Count)
            For intFoundFiles = 1 To .FoundFiles.Count
               astrFiles(intFoundFiles) = .FoundFiles(intFoundFiles)
            Next intFoundFiles
            GetFileList = astrFiles
      Else
         GetFileList = ""
      End If
   End With
End Function

The GetFileList procedure is available in the clsGetFileInfo module in ExcelExamples.xls in the ODETools\V9\Samples\OPG\Samples\CH06 subfolder on the Office 2000 Developer CD-ROM.

For more information about creating and using custom class modules in your Office solutions, see Chapter 9, "Custom Classes and Objects."