Office 97 Shared Object Models, Part 1

by Rick Dobson

Four Office 97 applications share Visual Basic for Applications (VBA)—and Outlook has a VBA look-alike in VBScript. Despite their similarities, the five Office 97 applications are unique in their object models. While these differences allow you to exploit the special capabilities of each package, they also complicate learning how to program the individual packages.

Fortunately, four shared Office object models are nearly identical across Access, Excel, PowerPoint, and Word. Learning these shared object models will help you feel comfortable programming the various packages. You'll also find that it's highly rewarding to learn each shared model just once—and then use it again and again in all four core packages—Access, Excel, PowerPoint, and Word.

In this article, we'll start with a brief introduction to the four shared models. Then, we'll expand on how to use one of those models, FileSearch. In future articles, we'll address the remaining shared object models.

Table A lists the four shared object-model names. It also provides a brief synopsis of the functionality available from each.

Table A: Shared Office object-model summary

The Office Assistant is one of the signature features of Office 97; users find it a great resource for all kinds of help and messages. Using the Assistant model to integrate the Assistant into your custom applications can build in familiarity and make custom solutions simple to use.

Custom applications also require custom menus. Sometimes, you just need the menus to prevent access to potentially dangerous commands. At other times you want to allow access to special new features. You may even have to totally reshape the menu and toolbar system. The CommandBars model provides the functionality for the redesign.

Office 97 introduced a new drawing layer that's separate from the document layer. The Drawing model includes a list of objects that simplifies the programmatic creation of graphic images on the drawing layer. You can add custom images as well as import images from other sources. (Note: This shared model is available in Excel, PowerPoint, and Word, but not in Access.)

Finally, the FileSearch model allows you much the same file search capability that appears in the File | Open command. Users can use wildcards to designate target files and specify multiple criteria for advanced search filters.

To use any of these models in a custom Office application, you must first create a reference to the Microsoft Office 8.0 Object Library. First, choose References from the Visual Basic Editor's Tools menu. Then, click the check box for the library (as shown in Figure A) and click OK to close the dialog. Now, let's take a detailed look at the first shared model, FileSearch.

Figure A: You must create a reference to the Microsoft Office 8.0 Object Library to use any of the shared Office models.

Three objects comprise the FileSearch model. They are FileSearch, FoundFiles, and PropertyTests.

As we mentioned, the FileSearch object represents the functionality of the standard Open dialog box. You use the FileSearch property for the Application object to return the FileSearch object. The syntax is Application.FileSearch. A FileSearch data type exists in Excel, PowerPoint, and Word so that you can declare a memory variable to represent the object and then assign it with a set statement. Access doesn't offer a FileSearch data type, but you can still reference the FileSearch object by applying its property to the Application object.

Several FileSearch properties allow you to manipulate the object's behavior. Set the Lookin property to a string that represents where the FileSearch object conducts a file search. The SearchSubFolders property takes Boolean values of True or False to control whether the search examines subfolders of the path value in the Lookin property.

The FileName and FileType properties determine the target for a file search. With simple searches, it's easier to represent the file type in the FileName property. You can include wildcard parameters, such as ? and *, in the string expression representing the FileName property value. A FileName property of *.doc, for example, causes a search  for all Word files.

The FileType property is most useful when you're constructing an advanced search. With advanced searches, you represent the search target through multiple criteria—as we'll see shortly.

Various other FileSearch properties help you fine tune the behavior of a search. The LastModified property allows you to restrict the return set of files to those modified in a specified time frame. Setting the property to any of a series of Office constants, such as msoLastModifiedLastMonth, msoLastModifiedLastWeek, or msoLastModifiedThisMonth, makes it easy to designate a specific period. The MatchTextExactly and TextOrProperty properties work together to let you create a criterion that looks for a specific text string in a file's name, contents, or property settings.

Two methods govern the operation of the FileSearch object. Use the Execute method to launch a search after specifying the criteria by setting FileSearch properties. You can use Execute method arguments to sort the file return set.

The NewSearch method restores all FileSearch properties to their default values—except the Lookin property. You might invoke this method just before you prepare to set the criteria for a new search.

The FoundFiles object contains the list of files in a FileSearch return set, as well as the file paths and names. You make this object available by applying the FoundFiles property to the FileSearch object. Use the FoundFiles object to count the number of files matching the criteria in a FileSearch and to list the individual return set members. The contents of this object refresh whenever an application invokes the Execute method for the FileSearch object.

The PropertyTests collection object holds multiple criteria to specify an advanced search. These criteria are like those created when a user clicks the Advanced... button on the standard Open dialog. Any PropertyTests collection object is likely to contain two or more criteria as individual PropertyTest objects. These objects represent the individual criteria.

Now, let's see how the FileSearch model works in the real world. Listing A shows a simple example of an Access subprocedure using the FileSearch object model. This example looks in the administrator's personal folder on an NT Workstation computer for a file named db1.mdb. If the file is there, the sub reports that one file is there and then presents a second message box with the file and path. If the file isn't there, the sub reports that no files match the target.

As you can see, the routine nests most of its code in a With...End loop that references the FileSearch object. It takes a minimum of two factors to launch a search: the Lookin property and the FileName property. If you can accept the Lookin setting from the last use of the FileSearch object, then you don't even have to set that property.

Listing A: An Access subprocedure to search for a file.

Public Sub MySearch1()
Dim i As Integer

With Application.FileSearch
    .LookIn = "C:\Nt\Profiles\Administrator\Personal"
    .FileName = "db1.mdb"
    If .Execute > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

This procedure is more complicated than necessary if you only want to search for one file. We purposely developed it this way so that you would have a reference for looking for two or more files. By changing the FileName property to *.doc, for example, the subroutine will report all the Word documents in an individual folder.

Changing the Lookin property in Listing A to C:\ will cause the routine to examine all folders on the drive. Because the SearchSubFolders property is True by default, this operation can take a long time—depending on the speed of the hard drive and whether your application references a networked computer. Setting SearchSubFolders property to False can speed the search by limiting it to the root directory. To set this property, just add this line after the line beginning with .FileName:

.SearchSubFolders = False

You can also hurry a search by not refreshing the file index. The Execute method updates the index by default, but you can change this by setting the AlwaysAccurate argument to False. To do so, replace the Execute in the template with Execute(, , False) to avoid compiling a new file index for a search.

Listing B shows a similar subprocedure written for Word instead of Access. The Lookin property for this particular search targets the Program Files folder in the c share on another computer named cabxp5. In addition, this subprocedure explicitly sets the SearchSubFolders property to True. In all other respects, it's identical to the procedure in Listing A. The similarity of the two subs demonstrates the shared nature of the FileSearch object model across different Office applications. In addition, this basic template works equally well in Excel and PowerPoint.

Listing B: A Word subprocedure to search for files

Public Sub AccessSearch()
Dim i As Integer

With Application.FileSearch
    .LookIn = "\\cabxp5\c\Program Files"
    .SearchSubFolders = True
    .FileName = "*.doc"
    If .Execute(, , False) > 0 Then
    MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

Listing C improves on the Word routine. First, it writes a copy of the return set at the end of the active document, which is a more convenient alternative than using message boxes as the return set grows in size. Second, it demonstrates how to use a memory variable to represent the search object. (Recall that Access doesn't permit this.)

Listing C: Improved Word subprocedure to search for files

Public Sub WordSearch1()

Dim i As Integer, myRange As Range
Dim fs As FileSearch

Set myRange = ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd
Set fs = Application.FileSearch
With fs
    .LookIn = "\\cabxp5\c\Program Files"
    .SearchSubFolders = True
    .FileName = "*.doc"
    If .Execute > 0 Then
    myRange.InsertAfter "There were " & .FoundFiles.Count & _
            " file(s) found." & vbCrLf
        For i = 1 To .FoundFiles.Count
            myRange.InsertAfter .FoundFiles(i) & vbCrLf
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

This routine writes the return set at the end of the myRange range object. It initializes the range to the active document's contents. Then, the Collapse method moves the range's insertion point to the end of the document. Finally, the InsertAfter method enters the number of files and the file names and paths in the active document. The routine still uses a simple message box if no files match the criteria.

The fs variable represents the FileSearch object in the code. A Dim statement declares it as a FileSearch type. A Set statement assigns the FileSearch object to it. Finally, the With...End loop references the memory variable for all the other search properties and objects. This loop isn't strictly necessary in this application, but it's useful for storing a set of search settings and using them later.

Listing D demonstrates one instance where being able to assign the search object to a variable simplifies the code; it also demonstrates how to use the PropertyTests collection object. Recall that this object stores advanced search criteria similar to those in the Advanced Find dialog that appears when a user clicks the Advanced... button in the standard Open dialog.

Listing D: Applying the PropertyTests collection object

Public Sub WordSearcp()

Dim i As Integer, myRange As Range
Dim fs As FileSearch

Set myRange = ActiveDocument.Content
myRange.Collapse Direction:=wdCollapseEnd
Set fs = Application.FileSearch
fs.NewSearch
With fs.PropertyTests
    .Add Name:="Files of Type", _
    Condition:=msoConditionFileTypeDatabases, _
    Connector:=msoConnectorOr
    .Add Name:="Last Modified", _
    Condition:=msoConditionAnytimeBetween, _
    Value:="1/1/96", SecondValue:="12/31/97", _
    Connector:=msoConnectorAnd
End With
With fs
    .LookIn = "c:\Program Files"
    .SearchSubFolders = True
    If .Execute > 0 Then
    myRange.InsertAfter "There were " & .FoundFiles.Count & _
            " file(s) found." & vbCrLf
        For i = 1 To .FoundFiles.Count
            myRange.InsertAfter .FoundFiles(i) & vbCrLf
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

A Set statement assigns the FileSearch object to a variable named fs. The PropertyTests property for fs returns the PropertyTests object, and a With...End loop references this object.

The statements inside the With...End loop set the advanced criteria for a search. The first line inside the With loop uses the Add method to designate a criterion for a search. This criterion sets three arguments and extends over three lines. It looks for all Office database files.

The next three lines designate the second  criterion, which searches for any file last modified between 1/1/96 and 12/31/97. The With...End loop assigns both criteria to the fs memory variable representing the FileSearch object.

A second With...End loop retains the advanced criteria and adds more criteria before executing the search and writing out the return set to the end of the active document. Notice that the second loop doesn't specify either the FileName or FileType property for the search object. The advanced criteria designate the object for the search. The second loop indicates where to look (c:\Program Files), and whether to search subfolders.

The shared Office object models represent a simple way to extend your programming skills from one Office application to another. These models also deliver significant functionality that you'll want to incorporate in your custom Office systems.

In this article, we've presented an overview of all four shared models, focusing on the FileSearch model. We'll cover the other shared models in upcoming issues.

Rick Dobson, Ph.D., is president of CAB, Inc. a consultancy offering database, Internet, and Office development services. Rick is Microsoft certified to train developers in Access and Office 97 development. He's a contributing editor to Microsoft Interactive Developer and a frequent contributor to Byte. You can reach Rick online at RickD@cabinc.win.net or by fax at (502) 426-3743.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.