LOOKING AT THE FILESEARCH OBJECT

A useful addition to the Microsoft Office objects is FileSearch. This object can be used via code to search for files in your system using the same options as found on the Find dialog off the Tools menu, located on the File Open dialog (see Figure 20.24).

Where this would be useful is in applications where you need to track documents for users outside Access. Although most of the time users can locate the file on their own, sometimes they just want to type the name and have your application find the location. Another is to archive files from Access based on the LastEdited property.

As with other objects, the FileSearch object is manipulated using its properties and methods. Two of its properties are actually collections: FilesFound and PropertyTests. FilesFound actually is actually just that—a collection of the files found.

In Figure 20.24, the PropertyTests consists of the section located in the Find dialog (Files That Match These Criteria) and is set programmatically just like you would using the Define More Criteria section in the dialog.

Working with a Simple Example

In the following code, you can see some of the properties that you can set to take advantage of the FileSearch object:

Sub QuickSearch(strName As String, strWhereSearch As String)

    Dim fsCurr As FileSearch
    Dim intCurr As Integer
    Set fsCurr = Application.FileSearch
    fsCurr.FileName = strName
    fsCurr.LookIn = strWhereSearch
    fsCurr.SearchSubFolders = True
    fsCurr.Execute

    For intCurr = 1 To fsCurr.FoundFiles.Count
        Debug.Print fsCurr.FoundFiles(intCurr)
    Next intCurr

End Sub

This code, located in the modFileSearchRoutines module, can be found in the Chap20.mdb database, in the ExamplesChap20 folder on the CD-ROM. You can see the result when this routine is run from the Immediate window in Figure 20.25.

The way that the FileSearch object was used here makes it a step up from the Dir() function because you can get files listed in multiple folders and search for the base named.

Using FileSearch Properties

Table 20.4 is a complete list of the properties for the FileSearch object. Other than Application and the collections just mentioned, these properties are used for quick searches and can be combined in different ways to narrow your search down.

You can set these properties for overall searches, and then narrow them down by using the PropertyTests collection. After setting these properties, to actually get the search going, you need to use the Execute method off the FileSearch object.

Here is the full syntax for this method:

							FileSearchObject.Execute(SortBy,
							SortOrder,
							AlwaysAccurate)

In this syntax, the following arguments are optional:

• SortBy— Item the files are sorted by. Can be one of the following MsoSortBy enums: msoSortbyFileName, msoSortbyFileType, msoSortbyLastModified, or msoSortbySize.

• SortOrder— The order in which the returned files are sorted. Can be either of the following MsoSortOrder enums: msoSortOrderAscending or msoSortOrderDescending.

• AlwaysAccurate— Set to True to have the file search include files that have been added, modified, or deleted since the file index was last updated.

The other method on the FileSearch object is NewSearch, which is used for just the purpose it sounds like it's for—specifying that you want to perform a new search with new properties specified.

Using the PropertyTests Collection

The real power from using the FileSearch property is combining the PropertyTests property with the others just mentioned. There is definitely a trick to this because if you combine them wrong, you will get nothing. The PropertyTests collection consists of individual PropertyTest objects. Here are the properties of a PropertyTest object:

Looking at a More Complex Example That Uses the PropertyTest Object

By using the PropertyTest object, you can give users pretty well the same functionality of the Find dialog off the Open File dialog with your own form. Although I'm not doing that here, I will show you how to take advantage of the PropertyTest object and use some of the msoCondtion constants in a simpler form. Look at the code in Listing 20.6.

Listing 20.6. Chap20.mdb: Narrowing the Search by Specifying File Types and Last Updated

Sub NarrowDownFS(strName As String, strWhereSearch As String, _
            flgNarrowDown As Boolean, _
            Optional msoFileType As ap_msoFileTypes = ap_AllTypes, _
            Optional varDate As Variant)

    Dim fsCurr As FileSearch
    Dim intCurr As Integer

    Set fsCurr = Application.FileSearch

    '-- Clear the search properties to start fresh
    fsCurr.NewSearch

    '-- Specify where to search.
    fsCurr.LookIn = strWhereSearch
    fsCurr.SearchSubFolders = True
    fsCurr.FileType = msoFileType

    '-- If the name down flag is specified, use the PropertyTests object
    '   to narrow it down, otherwise just use the FileName property.
    If flgNarrowDown Then

        With fsCurr.PropertyTests

            .Add Name:="File Name", _
                Condition:=msoConditionEndsWith, _
                Value:=strName, _
                Connector:=msoConnectorAnd

            If Not IsMissing(varDate) Then
                .Add Name:="Last Modified", _
                    Condition:=msoConditionOnOrAfter, _
                    Value:=varDate, _
                    Connector:=msoConnectorAnd
           End If

        End With
    Else

        fsCurr.FileName = strName

    End If

    fsCurr.Execute

    '-- Print out the located files.
    For intCurr = 1 To fsCurr.FoundFiles.Count
        Debug.Print fsCurr.FoundFiles(intCurr)
    Next intCurr

End Sub

Rather than walk through the code, look at the subroutine header and what is passed in (as follows). This will give you an idea of what the routine will do. The rest of the code is documented pretty well, and you can follow through at your leisure.

Sub NarrowDownFS(strName As String, strWhereSearch As String, _
            flgNarrowDown As Boolean, _
            Optional msoFileType As ap_msoFileTypes = ap_AllTypes, _
            Optional varDate As Variant)

Similar to the QuickSearch routine, the first two parameters, strName and strWhereSearch, specify the name of the file and where you want start searching for the file.

The next parameter, flgNarrowDown, lets the routine know that you want to use the PropertyTest object to narrow the search, rather than just use the FileName property off the FileSearch object itself.

The next parameter, msoFileType, is set to a data type that's actually an enum called ap_msoFileType (my own concoction). By using some of the msoCondition constants, I created the enum in the Declaration section of the modFileSearchRoutines module, which follows:

Option Compare Database
Option Explicit

Enum ap_msoFileTypes
   ap_AllTypes = msoConditionFileTypeAllFiles
    ap_Binders = msoConditionFileTypeBinders
    ap_Databases = msoConditionFileTypeDatabases
    ap_Excel = msoConditionFileTypeExcelWorkbooks
    ap_OfficeFiles = msoConditionFileTypeOfficeFiles
    ap_PowerPoint = msoConditionFileTypePowerPointPresentations
    ap_Templates = msoConditionFileTypeTemplates
    ap_WordDocs = msoConditionFileTypeWordDocuments
End Enum

You can see the complete list of msoCondition constants back in Figure 20.26. I created separate enums for these to make it more convenient to call the parameter (see Figure 20.28).

The last parameter, varDate, allows you to specify a date to compare against the Last Modified property of the file(s) for which you are searching.

Now when you go through the code in Listing 20.6, you will have an idea where the parameters passed in are used. When it's run, here are a few calls to the NarrowDownFS routine and the results:

• Use the regular search:

  NarrowDownFS "Chap20","C:Books",False
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.ldb
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.mdb
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.xls
  

• Specify databases only:

  NarrowDownFS "Chap20","C:Books",True,ap_Databases
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.mdb
  

• Specify Office files only:

  NarrowDownFS "Chap20","C:Books",True,ap_OfficeFiles
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.mdb
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.xls
  

• Specify Office files only for Yesterday (the .xls file is dated a year ago):

  NarrowDownFS "Chap20","C:Books",True,ap_OfficeFiles,Date-1
  C:BooksPwrPrg2000AppCDExamplesChap20Chap20.mdb
  

There you have it. For the flexibility of the routine, that's not too much code. You can do a lot more with the FileSearch object. Play with it and have fun.