Building a COM Add-in for Microsoft Office XP Using Microsoft Visual Basic 6.0

Click here to download sample - odc_comaddinvb6.exe This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

June 2002

Applies to:
    Microsoft® Access 2002
    Microsoft Excel 2002
    Microsoft Word 2002
    Microsoft Visual Basic® 6.0

Summary: How to use and create COM add-ins as alternatives to application-specific add-ins or templates. (41 pages)

Download Odc_comaddinvb6.exe

Contents

Introduction
Why Use COM Add-ins?
COM Add-ins or Application-Specific Add-ins or Templates?
Using the COM Add-in Template Project
Creating, Registering, and Deploying a COM Add-in
Using Application-Specific Events
The File Search COM Add-in
Before Creating the Sample COM Add-in
Creating the File Search COM Add-in
Adding the Application-Specific Procedures and Form
Adding Shared Code
Configuring the Add-in Designers
Debugging the Add-in
Making the DLL
Unloading the File Search COM Add-in
Conclusion

Introduction

A Component Object Model (COM) add-in is a dynamic-link library (DLL) or executable (EXE) file that is specially registered for loading by Microsoft® Office XP applications (such as Microsoft Access, Microsoft Excel, and Microsoft Word). You can build COM add-ins in Microsoft Visual Basic® for Applications (VBA) with Microsoft Office XP Developer, with Microsoft Visual Basic 5.0 (or later), or with Microsoft Visual C++®.

Note   A COM add-in can be either a Microsoft ActiveX® EXE file or a DLL file. However, DLLs generally provide better performance than EXE files.

This article examines why you would use a COM add-in, the advantage of using COM add-ins over application-specific add-ins or templates, using add-in designers and the IDTExtensibility2 interface to create and deploy COM add-ins, and how to add a COM add-in menu item. We will also create a COM add-in in Visual Basic 6.0 which can be used to search folders and sub-folders on a hard drive for application-specific files in Access, Excel and Word.

Why Use COM Add-ins?

In the days before COM add-ins, if you wanted to create a utility that would work across different Office applications, you had to program each application-specific portion of the utility in a programming language unique to the application in which it would be used. For example, let's say you wanted to create a utility to create, format, and print documents from Word, worksheets from Excel, and reports from Access. To do this, you would need to use and understand Word Basic, Access Basic, and Excel macros, each of which had totally different dialects and syntax. In addition, you would also need to know how each type of document is created, saved, and managed in the various applications. And then there are the issues of different printers with different settings using different drivers.

Beginning in Office 2000, many Office family products now support add-ins implementing a common COM interface. COM add-ins use the Component Object Model which makes it possible for you to create a single add-in that is available to any application that supports these types of add-ins. By developing COM add-ins, you can extend the functionality of your Office-based applications without adding complexity for users. And what's more, you can build COM add-ins with Office Developer, with Visual Basic, or with Visual C++.

For more in-depth information about creating COM add-ins with these tools, see the topic What is a COM Add-in? at the Microsoft Developer Network (MSDN) Web site.

COM Add-ins or Application-Specific Add-ins or Templates?

There are two different types of add-ins: COM add-ins and application-specific add-ins. COM add-ins can work in more than one Office application. Application-specific add-ins work only in a single application. For example, you can create an application-specific add-in for Word that builds a custom document from the data in an Access database, and another add-in for Excel that does the same thing. You would save the Word add-in as a Word template file (.dot), and the Excel add-in as an Excel add-in file (.xla). Despite the fact that each add-in shares similar code, the add-ins would be distinct and separate.

With COM add-ins, you could build a single add-in that would include code specific to Word to create a document, code specific to Excel to create a worksheet, and another module containing code that is shared between the applications to retrieve and process the data from Access. Then you would register the add-in so that it was available for use by each application.

The following table lists both types of add-ins and their extensions:

Add-ins File extensions Available to
Word add-ins .dot, .wll, .wiz Word only
Excel add-ins .xla, .xll Excel only
PowerPoint add-ins .ppa, .pwz PowerPoint only
Access add-ins .mda, .mde Access only
Exchange client .dll Outlook and Microsoft Exchange clients only
COM add-ins .dll, .exe Word, Excel, Access, PowerPoint, Outlook, FrontPage, and other Office family applications.

Another way to distribute a custom Office application is to create a template. A template provides the user with a basis for creating a new document. For example, a Word template might include the basic layout for a report that an employee can use to create a new document with a similar layout and simply fill in the new information.

In addition to add-ins and templates, you also can create two specialized kinds of add-ins: wizards and code libraries. Wizards are add-ins that help users through a complex process, step-by-step. Code libraries are add-ins in which you can store frequently used procedures and generic code. By setting a reference to a code library, you can call procedures stored within that library from your current VBA project.

Using the COM Add-in Template Project

In order to create a COM add-in for Office, you create either an in-process (DLL that must run in a host application) or out-process (executable file that can run alone) ActiveX component by using either Office Developer or Visual Basic.

Note   You can use any COM-compliant tool that supports the IDTExtensibility2 interface (Delphi®, Microsoft Visual C++, and others) to create a COM add-in.

To simplify the task of creating COM add-ins, Office Developer provides a template project (COM Add-In.vbp), which can also be used to create a COM add-in in Visual Basic 6.0. The template project supplies the files you need to create a COM add-in, as well as the code that forms the basis for the add-in.

The COM Add-In.vbp provides the following:

  • An add-in designer An add-in designer is a component that helps you to create and register a COM add-in. You can modify an add-in designer to create your COM add-in and hook it into an application's user interface.
  • A reference to the IDTExtensibility2 interface The template project sets a reference to the type library containing this interface, which is contained in the file Msaddndr.dll. The IDTExtensibility2 interface supplies the events you need to run code when your add-in is connected to or disconnected from a host application. More information on this interface is provided later in this article.

An add-in designer is a file included with the template project to help you create and register your COM add-in. You can create a COM add-in without including an add-in designer but using an add-in designer simplifies the process of creating and registering the add-in. You use an add-in designer to specify important information for your COM add-in such as its name and description, what application it is to run in, and how it loads in that application.

Like forms in a Visual Basic project, an add-in designer (see Figure 1) has a user interface component and an associated class module. The user interface component is never visible to the user when the add-in is running; it is visible only to the developer at design time. You can think of the add-in designer as a sort of dialog box where you specify settings for the add-in.

Aa140195.odc_comaddinvb601(en-us,office.10).gif

Figure 1. An Add-in Designer for Visual Basic 6.0

The associated class module contains events that occur when the add-in is loaded or unloaded. These events are implemented in the IDTExtensibility2 interface. You can use these events to integrate the add-in into the host application.

When you create the add-in DLL, Visual Basic 6.0 uses the information you've given to the add-in designer to properly register the DLL as a COM add-in. Visual Basic 6.0 writes the add-in's name, description, and initial load behavior setting to the registry. The add-in's host application reads these registry entries and loads the add-in accordingly.

More information about setting these options and using add-in designers is provided later in this article.

Creating, Registering, and Deploying a COM Add-in

The process of creating a COM add-in can be broken down into three general steps:

  1. Create a new DLL/EXE COM add-in project.
  2. Implement the IDTExtensibility2 interface.
  3. Add settings to the Microsoft Windows® registry consisting of a subkey to indicate which applications can host the COM add-in.

Of course, the details are a little more involved than this but these are the basic goals. Let's look at this process in a little more detail.

Creating a New DLL/EXE COM Add-in Project

When you create a new COM add-in project in Visual Basic 6.0 using the template project, the template adds a form, a module containing shared code, and an add-in designer to the project. If you are going to be using the COM add-in in more than one application, you need to add additional add-in designers to the project, one for each application. After renaming the various components in the project to reflect the function of the COM add-in, you will then need to configure each add-in designer by setting the following options on the form:

Option Description
Add-in Display Name The name that will appear in the COM Add-ins dialog box in an Office application.
Add-in Description Descriptive text for a COM add-in, available from VBA in the Description property of the COMAddIn object.
Application The application in which the add-in will run. This list displays applications that support COM add-ins.
Application Version The version of the application in which the add-in will run.
Initial Load Behavior The way that the add-in will load in the Office application. The list of possible settings comes from the registry. For more information, see the next table.
Add-in is command-line safe Does not apply to COM add-ins running in Office applications.

When a COM add-in has been properly registered on a user's computer (either by creating the DLL in Visual Basic or using the Regsvr32 command line option), it's available to whatever applications are specified in the add-in designers. Registered COM add-ins appear in the COM Add-ins dialog box in each application or can be added to the application by browsing to the COM add-in DLL. Selecting the check box next to an add-in in the COM Add-ins dialog box loads (connects) the add-in and makes it available to the user; clearing the check box unloads (disconnects) the add-in so that it can't be run.

As the COM add-in developer, you specify when a COM add-in should be loaded. You do this in the Initial Load Behavior text box in the add-in designer. You can specify that an add-in be loaded in one of the following ways:

  1. Only when the user loads it in the COM Add-ins dialog box, or when VBA code loads it by setting the Connect property of the corresponding COMAddIn object.
  2. Every time the application starts.
  3. The first time the application starts, so that it can create a toolbar button or menu item for itself. After that, the add-in is loaded only when the user requests it by clicking the menu item or button.

The following table describes the different settings for the Initial Load Behavior setting.

Initial load behavior setting Behavior
None The COM add-in is not loaded when the application boots. It can be loaded in the COM Add-ins dialog box or by setting the Connect property of the corresponding COMAddIn object.
Startup The add-in is loaded when the application boots. Once the add-in is loaded, it remains loaded until it is explicitly unloaded.
Load on Demand The add-in is not loaded until the user clicks the button or menu item that loads the add-in, or until a procedure sets its Connect property to True.
Load at Next Startup Only After the COM add-in has been registered, it loads as soon as the user runs the host application for the first time, and it creates a button or menu item for itself. The next time the user boots the application, the add-in is loaded on demand, that is, it doesn't load until the user clicks the button or menu item associated with the add-in.

After you've specified the information in the add-in designer, you then need to add code in the designer's class module to handle integration of the COM add-in with the host application when the add-in is loaded or unloaded. If the add-in contains a form, the add-in designer may also contain code to display the form.

Implementing the IDTExtensibility2 Interface

A COM add-in has events that you can use to run code when the add-in is loaded or unloaded, or when the host application has finished starting up or is beginning to shut down. In order to use these events, you must implement the IDTExtensibility2 interface, which provides a programming interface for integrating COM add-ins with their host applications. Microsoft provides a type library (MSADDNDR.DLL) for this interface. By default, this file is located in the C:\Program Files\Common Files \Designer subfolder for Office or Visual Basic 6.0 installations. More information about MSADDNDR.DLL can be found in the DLL Help Database. When you implement the IDTExtensibility2 interface within a class module, the library makes a set of new events available to control your COM add-in.

Note   If you create your add-in project by using the COM add-in template project, the IDTExtensibility2 interface is automatically implemented for you in the add-in designer's class module.

Sequence of IDTExtensibility2 Events Procedures

The IDTExtensibility2 interface provides five events that you can use to integrate the COM add-in and the host application: OnConnection, OnDisconnection, OnAddInsUpdate, OnStartupComplete, and OnBeginShutdown. For most COM add-ins, you will use the OnConnection and the OnDisconnection events. Each event is briefly described below. For more detailed information about these events, see the article Working with the IDTExtensibility2 Event Procedures.

The OnConnection event

The OnConnection event occurs when the COM add-in is loaded (connected). An add-in can be loaded in one of the following ways:

  1. The user starts the host application and the add-in's load behavior is specified to load when the application starts.
  2. The user loads the add-in in the COM Add-ins dialog box.
  3. The Connect property of the corresponding COMAddIn object is set to True. The COMAddIn object represents a COM add-in in the Office host application. The COMAddIn object is a member of the COMAddIns collection.

The OnConnection event procedure takes four arguments, described in the following table: 

Argument Type Description
Application Object Provides a reference to the application in which the COM add-in is currently running.
ConnectMode Long A constant that specifies how the add-in was loaded.
AddInInst Object A COMAddIn object that refers to the instance of the class in which code is currently running. You can use this argument to return the programmatic identifier for the add-in.
Custom Variant An array of Variant type values that provides additional data. The numeric value of the first element in this array indicates how the host application was started: from the user interface (1), by embedding a document created in the host application in another application (2), or through Automation (3).

The OnDisconnection event

The OnDisconnection event occurs when the COM add-in is unloaded. You can use the OnDisconnection event to run code that restores any changes made to the application by the add-in and to perform general clean-up operations. For example, in the File Search COM add-in sample described later in this article, the OnDisconnection event is used to execute the code that removes the File Search COM add-in menu item from the Office application.

An add-in can be unloaded in one of the following ways:

  1. The user clears the check box next to the add-in in the COM Add-ins dialog box.
  2. The host application closes. If the add-in is loaded when the application closes, it is unloaded. If the add-in's load behavior is set to Startup, it is reloaded when the application starts again.
  3. The Connect property of the corresponding COMAddIn object is set to False.

The OnDisconnection event procedure takes two arguments, described in the following table: 

Argument Type Description
RemoveMode Long A constant that specifies how the add-in was unloaded.
Custom Variant An array of Variant type values that provides additional data. The numeric value of the first element in this array indicates how the host application was started: from the user interface (1); by embedding a document created in the host application in another application (2); or through Automation (3).

The OnStartupComplete event

The OnStartupComplete event occurs when the host application completes its startup routines, in the case where the COM add-in loads at startup. If the add-in is not loaded when the application loads, the OnStartupComplete event does not occur—even when the user loads the add-in in the COM Add-ins dialog box. When this event does occur, it occurs after the OnConnection event.

You can use the OnStartupComplete event procedure to run code that interacts with the application and that should not be run until the application has finished loading. For example, if you want to display a form that gives users a choice of documents to create when they start the application, you can put that code in the OnStartupComplete event procedure.

The OnBeginShutdown event

The OnBeginShutdown event occurs when the host application begins its shutdown routines, in the case where the application closes while the COM add-in is still loaded. If the add-in is not loaded when the application closes, the OnBeginShutdown event does not occur. When this event does occur, it occurs before the OnDisconnection event.

You can use the OnBeginShutdown event procedure to run code when the user closes the application.

The OnAddInsUpdate event

The OnAddInsUpdate event occurs when a set of loaded COM add-ins changes. When an add-in is loaded or unloaded, the OnAddInsUpdate event occurs in any other loaded add-ins. For example, if add-ins A and B both are loaded currently, and then add-in C is loaded, the OnAddInsUpdate event occurs in add-ins A and B. If C is unloaded, the OnAddInsUpdate event occurs again in add-ins A and B.

If you have an add-in that depends on another add-in, you can use the OnAddInsUpdate event procedure in the dependent add-in to determine whether the other add-in has been loaded or unloaded.

Note   The OnStartupComplete, OnBeginShutdown, and OnAddInsUpdate event procedures each provide only a single argument, the Custom argument, which is an empty array of Variant data type values. This argument is ignored in COM add-ins for Office XP applications.

Adding Command Bar Control and Menu Items

If your COM add-in has a user interface, it must be integrated with the host application in some way, so that the user can interact with it. For example, if the user interface for your COM add-in includes a form, then at some point you must include code in the add-in to display the form.

One way to integrate your add-in with an application's user interface is to include code in the OnStartupComplete event procedure to create a new command bar control (toolbar button or menu item) in the host application. When the add-in is loaded, the user can click the button or menu item to work with the add-in. Similarly, you can add code to unload your add-in in the OnBeginShutdown event procedure or the OnDisconnection event procedure.

The critical aspect of integrating an add-in through a command bar control is the process of setting up the event sink. You must create a command bar control that is event-ready so its Click event is triggered when the user clicks the control. You can use the WithEvents keyword to create an event-ready command bar control.

More detailed information about adding command bar control items can be found in the article Hooking a COM Add-in Up to a Command Bar Control. Step-by-step procedures to add a command bar control can be found in the article Creating a Command Bar Control on MSDN.

Working with the Different Applications

The next step in the process of creating a COM add-in is to include code that provides the add-in's functionality in the applications in which it will be used, and to set references to the object libraries the add-in will work with. If your add-in will be used in more than one application, you can use the OnConnection event procedure to determine which application your add-in is currently running in and then selectively run code that works with that application's objects. In the template project, you can determine which application the add-in is currently running by using the object supplied by the Application argument of the OnConnection event. Assign this object variable to a global object variable, check it during run time, and then use that application's object model to perform the task.

A COM add-in DLL is loaded into memory only once, but each application that accesses the DLL gets its own copy of the DLL's data, stored in a separate space in memory. Therefore, you can use global variables in a COM add-in without worrying about data being shared between multiple applications. For example, the File Search COM add-in, used later in this article, can run simultaneously in Access, Excel, and Word. When Word loads the add-in, the OnConnection event occurs and a reference to the Word Application object is stored in a global variable of type Object. If Excel then loads the add-in, the OnConnection event occurs and a reference to the Excel Application object is also stored in a global variable of type Object, but in a different space in memory. Within the code for the add-in, you can use the If TypeOf . . . End If construct to check which application's Application object the variable points to.

Debugging, Making and Distributing the COM Add-in

After creating the COM add-in in Visual Basic, you can debug the add-in by putting the project into run mode. With the project in run mode, the COM add-in can be loaded and used in an Office application. There it can be tested and debugged by using the Visual Basic debugging tools (breakpoints, Stop statement, and watches).

After you've debugged your COM add-in, you will then make it into a DLL. In Visual Basic 6.0, you would click Make projectname.dll on the File menu. The process of making the DLL registers it on the local computer. In addition, the information in the add-in designer is used to add a subkey (HKEY_CURRENT_USER\Software\Microsoft\Office\<AppName>\Addins) in the Windows registry, indicating which applications can host the add-in. The COM add-in then appears in the COM Add-ins dialog box in those applications for which it is registered. You can also register the COM add-in on a user's computer by using the command:

regsvr32 projectname.dll

If you're planning to distribute your COM add-in to other users, you will need to install all the necessary files on each user's system and register the add-in. The easiest way to distribute a COM add-in is to include an add-in designer in the add-in project for each application, and then create a setup program for the add-in. The user can install and register the add-in by running the setup program. To create the setup program, run the Package and Deployment Wizard in Visual Basic or Office Developer on the add-in project. When the user runs the setup program, all the files required for the add-in to run will be copied to the user's computer and registered. For more information on the Package and Deployment Wizard, see the article The Package and Deployment Wizard on MSDN.

Using Application-Specific Events

In addition to using the events in the IDTExtensibility2 interface, you may also want to use application-level events in the applications hosting the COM add-in. For example, you might want to add code in your COM add-in that uses the Word OnDocumentSave event to add a watermark, representing your company's logo, to each saved document.

Application-level events occur, as the name implies, at the upper-most level in the application and are typically triggered when an event that is fundamental part of the functionality of the application occurs. For example, when a new Word document is created, the NewDocument event is triggered. When an Excel workbook is created, the NewWorkbook event is triggered.

When you create a new VBA project in Word or Excel, the project contains, by default, a class bound to the application's current document. In Word, this is the ThisDocument class and in Excel, the ThisWorkbook class. You can then write procedures that respond to the application-level events in these classes. As a result, all application-level events are available to you in the VBE Procedures drop-down list when you click the Application object in the Object drop-down list.

You can also use the WithEvents keyword in your own classes to display all of the events associated with the Application object. For example, if you enter the following code in the ThisWorkbook class in Excel,Public WithEvents xlApp As Application, xlAppwill appear in the Object drop-down list and all of the events associated with the Excel Application object will appear in the Procedures drop-down list.

Access provides a different model that responds to events on Form, Report, and Data Access Page objects, and the controls on these objects such as ListBox objects and TextBox object. Thus, when you create a new VBA project in Access, the project doesn't contain a class module bound to the current database. Instead there are events bound to forms, reports, data access pages, and the controls they contain.

In addition to application-level events, there are also object-levels events. However, the difference between object-level events and application-level events is that application-level events apply to all objects in the application, thus, application-level events apply to all workbooks in Excel or all documents in Word. Object-level events apply only to a single object.

To see the application-level events for Word 2002, do the following:

  1. Start Word 2002.
  2. Create a new blank document.
  3. On the Tools menu, click Macro, and then click Visual Basic Editor.
  4. In the Project Explorer, double-click ThisDocument to open the ThisDocument module.
  5. In the Object box in the Code window, click Document, and then click the drop-down arrow in the Procedure box.
  6. You can see that Close, Open, and New are the application-level events available for Word. Select Open.

The VBE will create an event procedure template for the document's Open event.

Private Sub Document_Open()
    ' Code goes here.
End Sub

You can then enter any code you want to run whenever the document is opened.

To see the application-level events for Excel 2002, do the following:

  1. Start Excel 2002.
  2. On the Tools menu, click Macro, and then click Visual Basic Editor.
  3. In the Project Explorer, double-click ThisWorkbook to open the ThisWorkbook module.
  4. In the Object box in the Code window, click Workbook, and then click the drop-down arrow in the Procedure box.

You can see that there are a number of application-level events available for Excel (see Figure 2).

Aa140195.odc_comaddinvb602(en-us,office.10).gif

Figure 2. Application-level events in Excel

To see the events associated with an Access form, do the following:

  1. Start Access 2002.
  2. Under Objects, click Forms, and then click New.
  3. On the View menu, click Properties.
  4. In the Properties window, click the Event tab.

You can scroll through the list of events associated with the form (see Figure 3).

Aa140195.odc_comaddinvb603(en-us,office.10).gif

Figure 3. Form-level events in Access

More information about events and event procedures can be found in the article Creating Events and Event Procedures located on MSDN.

The File Search COM Add-in

Now let's look at a sample COM add-in, the File Search Add-in, that is used to find files created by an Office application and display the application version that created the file. The sample add-in described in this article works in Access, Excel, and Word but could easily be extended to work in other applications.

To use the COM add-in, the DLL must be registered on the user's computer. After it is registered, the COM add-in appears as a menu option on the Tools menu in Word, Excel, and Access. When the user clicks the File Search Add-in menu item, a form is displayed that prompts the user for a search directory. The user types in a directory and clicks the search button. This executes code that examines a global variable to determine which application is running the add-in and then passes control to a procedure specific to the current application. This procedure passes the directory search path and target file extensions (.mdb, .adp, .mde for Access; .xls, .xla, .xlt for Excel; .doc, .rtf, .dot for Word) to a shared procedure. The shared procedure searches for the files and adds the results to a Dictionary object. Control is then returned to the application-specific procedure that parses the information in the Dictionary object into a document appropriate for the applicable. For example, the procedure creates and populates a table in Access, a worksheet in Excel, and a document in Word. The procedure also looks at properties of each returned file, determines which version of the application created the file, and adds that information to the results document. Once the results are returned, the form is unloaded.

In this article, we will look at the custom code and the general process used to create the sample File Search COM add-in. For detailed, step-by-step procedures on creating a COM add-in in the Office XP Developer, see the Knowledge Base article HOW TO: Create Office COM Add-Ins by Using VBA and Office Developer on the Microsoft Product Support Services Web site. For additional information about creating COM add-ins by using Visual Basic 6.0, see the article Building COM Add-ins for Office Applications on MSDN. This article contains procedures for creating COM add-ins both with and without the template project.

Before Creating the Sample COM Add-in

Before using the File Search COM add-in, you will need to register the COM add-in (which is done for you if you create the DLL in Visual Basic 6.0) and the DsoFile.dll on the computer using the COM add-in by using the Regsvr32 command. Both DLL files are provided as a download with this article.

Also note that after registering the File Search COM add-in on your computer, you will need to close and restart Access, Excel, and Word in order to have the COM add-in appear as a menu item on the Tools menu. If you use Microsoft Outlook® as your e-mail application with Word as the default editor, you will also need to close Outlook before registering the COM add-in.

Additionally, you will need to have the security settings in each application set at Medium or below in order to use the COM add-in.

Important   For more general information about Office security, see the Office XP Document: Macro Security White Paper, which was written for Microsoft Office XP security. You can find additional information about security on the Microsoft Security and Privacy site.

You can adjust your security settings by pointing to Macro on the Tools menu and then clicking Security. On the Security Level tab, click the Medium (or lower) option. It is recommended that you keep the security settings at Medium or higher to guard against unsafe macros and code.

To simplify the task of creating the COM add-in, the COM add-in template project (COM Add-In.vbp) provided with Office 2000 Developer and Office XP Developer was used. In order for the COM add-in template project to show up in the New Project dialog box, you need to copy the project from the Office Developer CD to C:\Program Files\Microsoft Visual Studio\VB98\Template\Projects folder of your Visual Basic 6.0 installation.

Note   Although Visual Basic 6.0 ships with the Add-in template, which can be used to create add-ins for the Visual Basic environment, you can't use that template for creating add-ins for Office without additional coding.

The File Search COM add-in consists of the following components:

  1. frmFileSearch A form that prompts the user for the path on a hard drive to search. The form contains procedures that handle the results of the file search and a text box that displays the number of files found. This form is used by all three applications that host the COM add-in.
  2. frmSearchResults A form used to display the results of the file search in Access.
  3. modSharedCode A module created by the template project containing code that can be called from each add-in designer to add a new command bar item to the Tools menu. In the File Search COM add-in, this module also contains the shared code that searches for files in a directory and stores the results in a Dictionary object.
  4. dsrFileSrchAccess An add-in designer for Access. This designer (and the other application-specific designers) includes a simple user interface and class module that helps you create and register the File Search COM add-in.
  5. dsrFileSrchExcel Add-in designer for Excel
  6. dsrFileSrchWord Add-in designer for Word

Creating the File Search COM Add-in

To create the File Search COM add-in, we first need to create the COM add-in project and then modify the generic form provided by the template project to work with our add-in. Perform the following steps:

  1. Start Visual Basic 6.0.

  2. In the New Project dialog box, click COM Add-In project and click OK. The template project creates a new template project containing a form with OK and Cancel buttons, an add-in designer named AddInDesigner1, and the modSharedCode module, which contains code to add a menu item in each Office application. The form, module, and the add-in designer both contain code that serves as a starting point for the add-in.

  3. Rename the project, form, and add-in designer by changing their Name property settings in the Properties window to give them meaningful names that are appropriate to the add-in that we're creating. In this case, type FileSrchAddin for the project name, frmFileSearch for the form name, and dsrFileSrchAccess for the add-in designer.

  4. In order to use the File Search Add-in in each of the three Office applications, you will need to set a reference to the following object libraries by clicking References on the Project menu, scrolling to the file, and selecting the check box (some of these may already be selected by default):

    Name File Name
    DS: OLE Document Properties 1.4 Object Library (available in the article Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and ASP). You will also need to register this DLL on the users computer by using the Regsvr32 <Path name\file name of DLL> command before using the File Search COM add-in. Dsofile.dll
    Microsoft Access 10.0 Object Library (available with Office) MSACC.OLB
    Microsoft ActiveX Data Object 2.7 Library (available in MDAC 2.7 with Office) MSADO15.DLL
    Microsoft Add-In Designer (available with the COM Add-in template project) MSADDNDR.DLL
    Microsoft ADO Ext. 2.7 for DDL and Security (available in MDAC 2.7 with Office) MSADOX.DLL
    Microsoft Excel 10.0 Object Library (available with Office) EXCEL.EXE
    Microsoft Office 10.0 Object Library (available with Office) MSO.DLL
    Microsoft Scripting Runtime (available with Visual Basic 6.0) SCRRUN.DLL
    Microsoft Word 10.0 Object Library (available with Office) MSWORD.OLB
    OLE Automation (available with Visual Basic 6.0) STDOLE2.TLB
    Visual Basic for Applications (available with Visual Basic 6.0) MSVBVM60.DLL
    Visual Basic objects and procedures (available with Visual Basic 6.0) VB6.OLB
  5. Because you must include one instance of the add-in designer for each application the COM add-in will run in, you will need to add one add-in designer for Word and Excel. On the Project menu, click Add Addin Class. Rename the designer dsrFileSrchExcel. Repeat this step and name the designer dsrFileSrchWord.

  6. Next, modify the frmFileSearch form by first deleting the OK and Cancel buttons. Then add controls and change their properties to match those in the following table:

    Controls Properties Values
    Label1 Caption Enter a starting directory for the search and click Search:
    Label2 Caption Files Found This Search
    TextBox Name txtSrchPath
    TextBox Name txtSrchResults
    CommandButton Name cmdSearch
      Caption Search
    CommandButton Name cmdClose
      Caption Close Form

    The form should look similar to Figure 4.

    Aa140195.odc_comaddinvb604(en-us,office.10).gif

    Figure 4. The File Search form

  7. Double click thecmdClosecommand button and insert the following in the cmdClose_Click event procedure to remove the form from memory when the user clicks the button:

    Unload Me
    
  8. Now we need to add code to close the form if the user presses the ESC key. In the Object drop-down list, make sure the Form object is selected. In the Properties drop-down list, select the KeyPress event. Add the following code to the Form_KeyPress subroutine:

    If KeyAscii = vbKeyEscape Then
        Unload Me
    End If
    
  9. Insert the following code for the cmdSearch command button in the code window:

    Private Sub cmdSearch_Click()
        ' Purpose: The procedure calls the application-specific
        '          routine to retrieve the files based on
        '          which application the COM add-in is running in.
        '
        On Error GoTo cmdSearch_Click_Err
    
        ' Get the search path.
        If Me.txtSrchPath = "" Then
            MsgBox "Please enter a path to search."
            GoTo cmdSearch_Click_End
        Else
            strPath = Me.txtSrchPath
        End If
        Me.txtSrchResults = ""
    
        If TypeOf gobjAppInstance Is Excel.Application Then
            Call GetExcelFiles(strPath)
        ElseIf TypeOf gobjAppInstance Is Word.Application Then
            Call GetWordFiles(strPath)
        ElseIf TypeOf gobjAppInstance Is Access.Application Then
            Call GetAccessFiles(strPath)
        End If
    
        ' Unload the form when the search is complete.
        Unload Me
    
    cmdSearch_Click_End:
        Exit Sub
    cmdSearch_Click_Err:
        MsgBox Err.Number & ": " & Err.Description
        Resume cmdSearch_Click_End
    End Sub
    

This procedure prompts the user for the directory path to search and then calls the application-specific subroutine (GetAccessFiles, GetExcelFiles, or GetWordFiles) based on the value of gobjAppInstance variable. The gobjAppInstance variable is a global variable created by the template project to store a reference to the application hosting the add-in. It can be checked anywhere in the project to determine which application is currently running the COM add-in.

Adding the Application-Specific Procedures and Form

Next, we will add the application-specific procedures to the project.

  1. Add the following code to the form:

    Private oFilePropReader  As DSOleFile.PropertyReader
    Private oDocProp         As DSOleFile.DocumentProperties
    Private dctDictionary    As Scripting.Dictionary
    Private p_frmSrchResults As frmSearchResults
    Private varItem          As Variant
    Private strFileType1     As String
    Private strFileType2     As String
    Private strFileType3     As String
    Private strPath          As String
    Private Sub GetAccessFiles(ByVal strPath As String)
        ' Purpose: Calls the GetFiles routine and, after creating
        '          a table to store the results, stores the
        '          search results in a recordset based on the
        '          table. The routine examines each file returned
        '          and determines the version of Access by looking
        '          at the Jet version. At the same time, the routine
        '          populates and displays a report of the results.
        ' Accepts: strPath - The starting path to be searched.
        '
        Dim acApp           As Access.Application
        Dim frmResults      As Form
        Dim rstResults      As Recordset
        Dim conConnect      As Connection
        Dim lngSource       As Long
        Dim strData         As String
        Dim strDB_file      As String
    
        Const VERSION_STRING_SIZE As Integer = 24
        Const JET_2_VERSION_NUMBER_START As Integer = 1
        Const JET_VERSION_NUMBER_START As Integer = 21
        Const LENGTH As Integer = 1
    
        On Error GoTo GetAccessFiles_Err
    
        strFileType1 = "MDB"
        strFileType2 = "ADP"
        strFileType2 = "MDE"
    
        Set acApp = gobjAppInstance
    
        ' Make sure that user has opened a database first.
        If acApp.CurrentDb Is Nothing Then
            MsgBox "You must open a database before using " & _
                "this add-in.", vbOKOnly, "File Search Add-in"
            GoTo GetAccessFiles_End
        End If
    
        Set dctDictionary = New Scripting.Dictionary
    
        ' Show progress to the user.
        DoCmd.Hourglass True
    
        ' If no Access files are found, update the results text box and
        ' return control, otherwise create a results table. Iterate
        ' throught the results dictionary to get the path and file
        ' information. Determine the Access file version and then store
        ' the path name and version information in the table.
        If GetFiles(strTarget:=strPath, dctDictionary:=dctDictionary, _
            strFileType1:=strFileType1, strFileType2:=strFileType2, _
            strFileType3:=strFileType3, bln_Is_Recursive:=True) Then
            If dctDictionary.Count = Empty Then
                Me.txtSrchResults = dctDictionary.Count
            Else
                Me.txtSrchResults = dctDictionary.Count
                Set p_frmSrchResults = New frmSearchResults
    
                ' Create a table to hold the search results.
                Call CreateTable
    
                Set rstResults = New ADODB.Recordset
                Set conConnect = CurrentProject.Connection
    
                rstResults.Open Source:="tblSearchResults", _
                    ActiveConnection:=conConnect,
                      CursorType:=adOpenKeyset, _
                    LockType:=adLockOptimistic
    
                    For Each varItem In dctDictionary
                        rstResults.AddNew
                        rstResults!File = varItem
    
                        p_frmSrchResults.lstResults.AddItem varItem
    
                        ' Determine the version number of the Access
                          file by
                        ' retrieving the Jet version number and
                          inferring
                        ' the Access version from that.
                        strDB_file = varItem
                        lngSource = FreeFile
                        Open strDB_file For Binary As lngSource
                        strData = Space(VERSION_STRING_SIZE)
                        Get #lngSource, , strData
                        Close lngSource
    
                        If Chr(1) = Mid(strData, 
                          JET_2_VERSION_NUMBER_START, _
                            LENGTH) Then
                            rstResults!Version = "Microsoft Access 2"
                            p_frmSrchResults.lstResults.AddItem
                              "Version: Microsoft Access 2"
                        ElseIf Chr(0) = Mid(strData,
                          JET_VERSION_NUMBER_START, _
                            LENGTH) Then
                            rstResults!Version = "Microsoft Access 97"
                            p_frmSrchResults.lstResults.AddItem 
                              "Version: Microsoft Access 97"
                        ElseIf Chr(1) = Mid(strData,
                          JET_VERSION_NUMBER_START, _
                            LENGTH) Then
                            rstResults!Version = "Microsoft Access
                              2000/2002"
                            p_frmSrchResults.lstResults.AddItem 
                              "Version: Microsoft Access 2000/2002"
                        Else
                            rstResults!Version = "Unable to determine 
                              the version " & _
                                "of database file"
                            p_frmSrchResults.lstResults.AddItem 
                              "Version: Unable to determine the version 
                                "
                        End If
    
                        rstResults.Update
    
                    Next varItem
    
                ' Display a report of the search results.
                p_frmSrchResults.Show vbModal
    
            End If
    
            ' Turn off the progress indicator.
            DoCmd.Hourglass False
    
            ' Clean up the objects.
            Set rstResults = Nothing
            Set conConnect = Nothing
        End If
    
    GetAccessFiles_End:
        Exit Sub
    
    GetAccessFiles_Err:
        MsgBox Err.Number & ": " & Err.Description
        GoTo GetAccessFiles_End
    End Sub
    

    This code contains a number of variables that are used by the different application-specific subroutines. The more important of these variables will be explained as we examine the procedures that use them. The GetAccessFiles subroutine accepts the strPath variable, which contains the starting search directory. After the declaration section, String variables are set representing the different Access file types that will be the search targets. Next, a reference is set to the global gobjAppInstance variable, which represents the Access Application object. Then we check to insure that the add-in is being used with an open database.

    ...
    strFileType1 = "MDB"
    strFileType2 = "ADP"
    strFileType2 = "MDE"
    
    Set acApp = gobjAppInstance
    
    If acApp.CurrentDb Is Nothing Then
        MsgBox "You must open a database before using " & _
            "this add-in.", vbOKOnly, "File Search Add-in"
        GoTo GetAccessFiles_End
    End If
    ...
    

    We then set a reference to the Microsoft Scripting Runtime (Scrrun.dll) library. The objects in the Microsoft Scripting Runtime library (such as the Dictionary object) provide access to the file system and are used here to search folders and subfolders on a user's computer.

    ...
    Set dctDictionary = New Scripting.Dictionary
    ...
    

    Next, we call the shared GetFiles subroutine. The GetFiles subroutine resides in the modSharedFiles module and performs the actual file search. We pass the path of the directory to be searched, a reference to the Dictionary object, up to three file targets, and a flag indicating whether the user wants recursive searches (searching subfolders as well as folders).

    ...
    If GetFiles(strTarget:=strPath, dctDictionary:=dctDictionary, _
        strFileType1:=strFileType1, strFileType2:=strFileType2, _
        strFileType3:=strFileType3, bln_Is_Recursive:=True) Then
    ...
    

    If the search produces no results, we display the count 0 in the search results text box on the form and exit the subroutine. If files are found, we display the count in the search results text box, and then set a reference to the frmSearchResults form, which we will use to display the results.

    ...
    Me.txtSrchResults = dctDictionary.Count
    Set p_frmSrchResults = New frmSearchResults
    
    ' Create a table to hold the search results.
    Call CreateTable
    ...
    

    Next, we call the CreateTable subroutine, which creates a table to hold the results. Then we set a reference to an ActiveX® Data Objects (ADO) Recordset object, which is used to populate the table we just created. After establishing a connection to the current database, and opening the table as our data source, we iterate through the items in the Dictionary object and add them to the Recordset object. Then, we add those same items to the lstResults control on the frmSearchResults form.

    ...
    Set rstResults = New ADODB.Recordset
    Set conConnect = CurrentProject.Connection
    
    rstResults.Open Source:="tblSearchResults", _
        ActiveConnection:=conConnect, CursorType:=adOpenKeyset, _
        LockType:=adLockOptimistic
    
    For Each varItem In dctDictionary
        rstResults.AddNew
        rstResults!File = varItem
    
        p_frmSrchResults.lstResults.AddItem varItem
    ...
    

    We then examine the first 24 bytes of the header of each file to determine the version of Jet used to create the file. From this, we infer the version of Access that created the current file.

    ...
    strDB_file = varItem
    lngSource = FreeFile
    Open strDB_file For Binary As lngSource
    strData = Space(VERSION_STRING_SIZE)
    Get #lngSource, , strData
    Close lngSource
    
    If Chr(1) = Mid(strData, JET_2_VERSION_NUMBER_START, _
        LENGTH) Then
        rstResults!Version = "Microsoft Access 2"
        p_frmSrchResults.lstResults.AddItem "Version: Microsoft Access 
          2"
    ElseIf Chr(0) = Mid(strData, JET_VERSION_NUMBER_START, _
        LENGTH) Then
        rstResults!Version = "Microsoft Access 97"
        p_frmSrchResults.lstResults.AddItem "Version: Microsoft Access
          97"
    ElseIf Chr(1) = Mid(strData, JET_VERSION_NUMBER_START, _
        LENGTH) Then
        rstResults!Version = "Microsoft Access 2000/2002"
        p_frmSrchResults.lstResults.AddItem "Version: Microsoft Access
          2000/2002"
    Else
        rstResults!Version = "Unable to determine the version " & _
            "of database file"
        p_frmSrchResults.lstResults.AddItem "Version: Unable to
          determine
          the version "
    End If
    
    rstResults.Update
    ...
    
  2. The recordset is then updated which copies the results to the table. Then we display the frmSearchResults form with the search results.

  3. The GetWordFiles subroutine is similar to the one we just examined except it handles Word files. The following code is also used in the frmFileSearch form:

    Private Sub GetWordFiles(ByVal strPath As String)
        ' Purpose: Calls the GetFiles routine and stores the
        '          search results in a document. The routine
        '          examines the document properties of each
        '          returned file to determine the version of
        '          Word that created the file.
        ' Accepts: strPath - The starting path to be searched.
        '
        Dim wdApp           As Word.Application
        Dim docDocument     As Document
        Dim rngText         As Range
        Dim strText         As String
    
        On Error GoTo GetWordFiles_Err
    
        strFileType1 = "DOC"
        strFileType2 = "RTF"
        strFileType2 = "DOT"
    
        Set dctDictionary = New Scripting.Dictionary
        Set oFilePropReader = New DSOleFile.PropertyReader
        Set wdApp = gobjAppInstance
    
        ' Create a new document to hold the search results.
        Set docDocument = Documents.Add
        docDocument.SaveAs FileName:="Search Results"
        docDocument.Activate
    
        ' If no Word files are found, update the results text box and
        ' return control, otherwise paste the filename, version, and
        ' CLSID data onto the worksheet.
        If GetFiles(strTarget:=strPath, dctDictionary:=dctDictionary, _
            strFileType1:=strFileType1, strFileType2:=strFileType2, _
            strFileType3:=strFileType3, bln_Is_Recursive:=True) Then
            If dctDictionary.Count = Empty Then
                Me.txtSrchResults = dctDictionary.Count
                MsgBox "No files found."
                GoTo GetWordFiles_End
            Else
                Me.txtSrchResults = dctDictionary.Count
                With wdApp.Selection
                    .Range.InsertAfter "Search Results starting from " &
                      strPath & vbCrLf & vbCrLf
                    For Each varItem In dctDictionary
                        .Move wdSentence, 1
                        .Range.InsertAfter "Location: " & varItem &
                          vbCrLf
                        .MoveEnd wdParagraph, 4
                        Set oDocProp =
                          oFilePropReader.GetDocumentProperties(varItem)
                        If oDocProp.Version = "" Then
                            .Range.InsertAfter "    Version: Not 
                              available CLSID: Not available" & vbCrLf
                        Else
                            .Range.InsertAfter "    Version: " &
                              oDocProp.Version & " CLSID: " &
                               oDocProp.CLSID & vbCrLf
                        End If
                    Next varItem
                End With
            End If
    
            ' Clean up the objects.
            Set oFilePropReader = Nothing
            Set dctDictionary = Nothing
        End If
    
        Me.txtSrchPath.SetFocus
    
        MsgBox "The results have been posted to the Search Results
          document. " & _
            "Close this form and click on the Search Results icon in the
              " & _
            "Windows task bar to review the results."
    
    GetWordFiles_End:
        Exit Sub
    
    GetWordFiles_Err:
        MsgBox Err.Number & ": " & Err.Description
        GoTo GetWordFiles_End
    End Sub
    

    The first section of this subroutine is similar to the GetAccessFiles routine with the exception that we will be searching for files related to Word documents.

    In addition to setting a reference to the Scripting Runtime object library, we also set a reference to DSOleFile.PropertyReader. The DSOleFile object is part of the DS: OLE Document Properties 1.4 Object Library which is a simple in-process ActiveX DLL that can be used to read and modify the document summary properties for an OLE Structured Storage file such as native Excel, PowerPoint, Microsoft Visio®, and Word documents. The PropertyReader object is the only creatable object in the DLL. An EXE containing the DLL is available in the knowledge base article Dsofile.exe Lets You Edit Office Document Properties from Visual Basic and ASP.

    Next, we create a new document to display the results of the file search.

    ...
    Set docDocument = Documents.Add
    docDocument.SaveAs FileName:="Search Results"
    docDocument.Activate
    ...
    

    Then we call the shared GetFiles subroutine and display the count of files found. Next, we iterate through the items in the Dictionary object and add them to the new document.

    ...
    With wdApp.Selection
        .Range.InsertAfter "Search Results starting from " & strPath & 
          vbCrLf & vbCrLf
        For Each varItem In dctDictionary
            .Move wdSentence, 1
            .Range.InsertAfter "Location: " & varItem & vbCrLf
            .MoveEnd wdParagraph, 4
            Set oDocProp = 
              oFilePropReader.GetDocumentProperties(varItem)
            If oDocProp.Version = "" Then
                .Range.InsertAfter "    Version: Not available CLSID:
                  Not
                  available" & vbCrLf
            Else
                .Range.InsertAfter "    Version: " & oDocProp.Version &
                  " 
                  CLSID: " & _
                    oDocProp.CLSID & vbCrLf
            End If
        Next varItem
    End With
    ...
    

    We then use the GetDocumentProperties method of the DSOleFile.PropertyReader object to get the version and the class identifier (CLSID) of the Excel file. The GetDocumentProperties method is the only method of the DSOleFile.PropertyReader object.

    Note   The CLSID is a Globally Unique Identifier (GUID) associated with a COM class. It is used to uniquely identify the COM component to an application hosting the add-in (the COM server).

    And finally, we display a message to let the user know that the file search results have been posted.

    ...
    MsgBox "The results have been posted to the Search Results " & _
        "document. Close this form and click on the Search Results " & _
        "icon in the Windows task bar to review the results."
    ...
    
  4. The GetExcelFiles subroutine is similar to the two we just examined except it handles Excel files:

    Private Sub GetExcelFiles(ByVal strPath As String)
        ' Purpose: Calls the GetFiles routine and stores the
        '          search results in a worksheet. The routine
        '          examines the document properties of each
        '          returned file to determine the version and
        '          CLSID of Excel.
        ' Accepts: strPath - The starting path to be searched.
        '
        Dim xlApp           As Excel.Application
        Dim wrksSheet       As Worksheet
        Dim intRow          As Integer
    
        Const START_ROW As Integer = 2
    
        On Error GoTo GetExcelFiles_Err
    
        strFileType1 = "XLS"
        strFileType2 = "XLA"
        strFileType2 = "XLT"
    
        Set dctDictionary = New Scripting.Dictionary
        Set oFilePropReader = New DSOleFile.PropertyReader
        Set xlApp = gobjAppInstance
    
        ' If no Excel files are found, update the results text box and
        ' return control, otherwise paste the filename, version, and
        ' CLSID data onto the worksheet.
        If GetFiles(strTarget:=strPath, dctDictionary:=dctDictionary, _
            strFileType1:=strFileType1, strFileType2:=strFileType2, _
            strFileType3:=strFileType3, bln_Is_Recursive:=True) Then
            If dctDictionary.Count = Empty Then
                Me.txtSrchResults = dctDictionary.Count
                MsgBox "No files found."
                GoTo GetExcelFiles_End
            Else
                Me.txtSrchResults = dctDictionary.Count
    
                ' Excel doesn't handle missing worksheets very well
                ' so continue execution after the error is raised
                ' from the Set statement.
                On Error Resume Next
    
                Set wrksSheet = xlApp.ActiveWorkbook.Sheets("Search
                  Results")
                If wrksSheet Is Nothing Then
                    xlApp.ActiveWorkbook.Sheets.Add
                      Before:=xlApp.Worksheets("Sheet1")
                    xlApp.ActiveWorkbook.Sheets(1).Name = "Search
                      Results"
                Else
                    ' Clear any previous contents from the worksheet.
                    xlApp.ActiveWorkbook.Sheets("Search 
                      Results").UsedRange.EntireColumn.ClearContents
                End If
    
                ' Add headers to the worksheet.
                With xlApp.Worksheets("Search Results").Range("A1:C1")
                    .Value = Array("File", "Version", "ClassID")
                    .Font.Bold = True
                End With
                intRow = START_ROW
                For Each varItem In dctDictionary
                    xlApp.Worksheets("Search Results").Cells(intRow,
                      "A").Value = varItem
                    Set oDocProp = 
                      oFilePropReader.GetDocumentProperties(varItem)
                    If oDocProp.Version = "" Then
                        ' Excel versions 4.0 or before didn't store the
                        ' version, CLSID, or ProgID in the document
                        ' properties.
                        xlApp.Worksheets("Search Results").Cells(intRow,
                          "B").Value = _
                            "4.0 or before"
                        xlApp.Worksheets("Search Results").Cells(intRow,
                          "C").Value = _
                            "Not applicable"
                    Else
                        xlApp.Worksheets("Search Results").Cells(intRow, 
                          "B").Value = _
                            oDocProp.Version
                        xlApp.Worksheets("Search Results").Cells(intRow, 
                          "C").Value = _
                            oDocProp.CLSID
                    End If
    
                    intRow = intRow + 1
    
                Next varItem
    
                ' Fit the column widths to the data and sort the data
                  based
                ' on the version column.
                xlApp.Worksheets("Search 
                  Results").UsedRange.EntireColumn.AutoFit
                xlApp.Worksheets("Search 
                  Results").UsedRange.EntireColumn.Sort _
                    Key1:=xlApp.Worksheets("Search 
                      Results").Range("B2"), Header:=xlYes
    
            End If
    
            ' Clean up the objects.
            Set oFilePropReader = Nothing
            Set dctDictionary = Nothing
        End If
    
        Me.txtSrchPath.SetFocus
    
    GetExcelFiles_End:
        Exit Sub
    
    GetExcelFiles_Err:
        MsgBox Err.Number & ": " & Err.Description
        GoTo GetExcelFiles_End
    End Sub
    

    The principle difference between this subroutine and the subroutine used in Word is that in this procedure, we create a new worksheet called Search Results to store the results of our search.

  5. The CreateTable procedure is used to create a table in Access to store the search results:

    Private Sub CreateTable()
        ' This subroutine creates a table "tblSearchResults with two
        ' columns "File" and "Version". The Version column is indexed
        ' for sorting.
    
        Dim objCatalog  As ADOX.Catalog
        Dim objTable    As ADOX.Table
        Dim idxSort     As New ADOX.Index
    
        ' Create the catalog and table objects.
        Set objCatalog = New ADOX.Catalog
        Set objTable = New ADOX.Table
    
        ' Connect the catalog to the current database.
        objCatalog.ActiveConnection = CurrentProject.Connection
    
        ' If the tblSearchResults already exists, delete it.
        For Each objTable In objCatalog.Tables
            If objTable.Name = "tblSearchResults" Then
                objCatalog.Tables.Delete "tblSearchResults"
            End If
        Next objTable
    
        ' Now create a new tblSearchResults table.
        Set objTable = New ADOX.Table
    
        ' Create the table, an index, and the File and Version columns.
        objTable.Name = "tblSearchResults"
        idxSort.Name = "Sort_Version"
    
        objTable.Columns.Append Item:="File", Type:=adVarWChar
        objTable.Columns.Append Item:="Version", Type:=adVarWChar, _
           DefinedSize:=50
        idxSort.Columns.Append Item:="Version"
    
        objCatalog.Tables.Append Item:=objTable
        objTable.Indexes.Append Item:=idxSort
    
        ' Clean up the objects.
        Set objTable = Nothing
        Set objCatalog = Nothing
    End Sub
    

    This routine uses the Catalog object, the Table object, and the Index object, which are contained, in the Microsoft ADO Ext. 2.7 for DDL and Security (ADOX.DLL) library that ships with Office. The Microsoft ADO Ext. 2.7 for DDL and Security library contains members that define the schema and security model for a database. The Catalog object is a database's container for tables, views, procedures, users, and groups. The Table object represents the table used to store our search results and the Index object is a key used to sort the data stored in the table.

    The procedure iterates through the Tables collection of the Catalog object to see if the tblSearchResults table exists and, if so, deletes it. The code then creates a new tblSearchResults table, adds an index to it, and adds columns to the table.

    Next, we need to create a form to display the results of the file search in Access.

  6. Add a new form (Project menu, Add Form) and rename it frmSearchResults.

  7. Add controls to the frmSearchResults form and change their properties to match those in the following table: 

    Controls Properties Values
    Label1 Caption Search Results
    ListBox Name lstResults
    CommandButton Name cmdClose
      Caption Close Report
  8. Double-click the cmdClose command button and add the following code:

    Unload Me
    

Adding Shared Code

When the modSharedCode module is created by the template project, it contains code to add a menu item to the Tools menu of each application hosting the COM add-in. There is also code included to remove the menu item if the user disconnects it from the list of add-ins or when the application closes. This code can also be modified to add the item to a different menu option, add another level of nesting in the menu, and change the name of the menu item to reflect your COM add-in.

The modSharedCode module is also a good place to store code that is shared across applications using the add-in. For the File Search COM Add-in, we will insert the GetFiles subroutine that is called by each application to search for files. Do the following:

  1. Open the modSharedCode module and change the following three variables in the declarations section:

    ...
    Public Const CTL_CAPTION    As String = "&File Search Add-in"
    Public Const CTL_KEY        As String = "FileSrchAddIn"
    Public Const CTL_NAME       As String = "File Search Add-in"
    ...
    
  2. Now add the GetFiles subroutine to the modSharedCode module:

    Public Function GetFiles(ByVal strTarget As String, _
        ByVal dctDictionary As Scripting.Dictionary, _
        ByVal strFileType1 As String, _
        Optional ByVal strFileType2 As String, _
        Optional ByVal strFileType3 As String, _
        Optional ByVal bln_Is_Recursive As Boolean) As Boolean
        ' This function searches all files in a directory and stores the
        ' files into a Dictionary object. If called recursively,
        ' it also searches all files in subfolders. Up to three file
        ' extensions can be specified in the search.
        '
        ' Accepts:
        '       strTarget        The directory to be searched.
        '       strFileType1     |
        '       strFileType2     |-The file types to search for.
        '       strFileType3     |
        '       dctDictionary    Object for the search results.
        '       bln_Is_Recursive Flag to allow recursive searches.
    
        ' Returns:
        '       Flag indicating whether the search was successful.
    
        Dim fsoSystemObject As Scripting.FileSystemObject
        Dim fdrFolder       As Scripting.Folder
        Dim fdrSubFolder    As Scripting.Folder
        Dim filFile         As Scripting.File
        Dim strFileType     As String
        Dim strUCApp        As String
    
        Const FILE_EXTENSION As Integer = 3
    
        Set fsoSystemObject = New Scripting.FileSystemObject
    
        On Error Resume Next
    
        ' Get a folder or alert the user that there's a problem with
        ' the path and prepare for move back to the form.
        Set fdrFolder = fsoSystemObject.GetFolder(strTarget)
        If Err <> 0 Then
            MsgBox "The path " & strTarget & " is not valid."
    
            ' Return a False because of error
            GetFiles = False
            GoTo GetFiles_End
        End If
    
        On Error GoTo 0
    
        ' Search through each file and add any Excel files to the
        ' dictionary.
        For Each filFile In fdrFolder.Files
            ' Convert file name to uppercase and get the file
            ' extension.
            strUCApp = UCase(filFile)
            strFileType = Right(strUCApp, FILE_EXTENSION)
    
            If (strFileType = strFileType1 Or strFileType = strFileType2
              Or _
                strFileType = strFileType3) Then
                dctDictionary.Add Key:=filFile.Path, Item:=filFile.Path
            End If
        Next filFile
    
        ' If Recursive flag is true, call recursively to search 
          subfolders.
        If bln_Is_Recursive Then
          For Each fdrSubFolder In fdrFolder.SubFolders
             GetFiles strTarget:=fdrSubFolder.Path,
               dctDictionary:=dctDictionary, _
             strFileType1:=strFileType1, strFileType2:=strFileType2, _
             strFileType3:=strFileType3, bln_Is_Recursive:=True
          Next fdrSubFolder
        End If
    
        ' Return True if no error occurred.
        GetFiles = True
    
    GetFiles_End:
        Exit Function
    End Function
    

    The GetFiles subroutine is used by each of the applications hosting the COM add-in to perform the file search. By not hard-coding the names of the target files, the routine can be used by any procedure that needs to search for files of a particular type. The procedure begins by checking for a valid path and then searches for folders starting at the directory specified. Each file found is assigned as an item in the Dictionary object. If the bln_Is_Recursive flag is set to True, the routine is recursively called to search any subfolders.

Configuring the Add-in Designers

Next, we need to configure the add-in designers so that each application will properly load and configure the COM add-in.

  1. For each of the add-in designers, double-click the designer in the Project window, click the General tab on the form, and change the information displayed to match the values in the following table: 

    Field Access designer Excel designer Word designer
    Addin Display Name File Search Add-in File Search Add-in File Search Add-in
    Addin Description File Search Add-in for Access File Search Add-in for Excel File Search Add-in for Word
    Application Microsoft Access Microsoft Excel Microsoft Word
    Application Version Microsoft Access 10.0 Microsoft Excel 10.0 Microsoft Word 10.0
    Initial Load Behavior Load at next startup only Load at next startup only Load at next startup only

    The Load at next startup only value in the Initial Load Behavior field specifies that the COM add-in loads as soon as the user runs the host application for the first time, at which time it creates a button or menu item for itself. The next time the user boots the application, the add-in is loaded on demand. In other words, the COM add-in doesn't load until the user clicks the button or menu item associated with it. The other fields in the form are self-explanatory.

    The add-in designers contain code that responds to the event procedures exposed by the IDTExtensibility2 interface. To modify the code to work with the File Search COM add-in, we need to change the references from the generic form that the template project creates, to the frmFileSearch form.

  2. To do this, change the following statement in the declarations section from:

    Private p_frmCOMAddIn As frmCOMAddIn
    
    to:
    Private p_frmFileSrch As frmFileSearch
    
  3. Now change the name of the p_frmCOMAddIn variable in any of the event procedures where it appears to p_frmFileSrch.

Debugging the Add-in

Next, we need to debug the add-in to ensure that it works correctly in each of the Office applications it will run in. When running the add-in in any host, you will need to restart the host application before attempting to debug the add-in. Otherwise, the host application will not see the add-in or it will use the last version you compiled. Perform the following steps:

  1. In Visual Basic 6.0, click Start with Full Compile on the Run menu. This compiles your project, alerting you to any compilation errors, and then puts the project into run mode.

    Note   If you experience errors during this test, go back to step 1 and place any desired breakpoints, Stop statements, or watches in the code to troubleshoot.

  2. Start the intended host application for the COM add-in. For example, start Access 2002. Since we set the load behavior of the add-in to Load at Next Startup Only, the add-in loads as soon as you start Access, adding the File Search Add-in item to the Tools menu.

  3. Click the File Search Add-in item on the Tools menu. The frmFileSearch form is displayed.

  4. Type in a starting search directory (for example, type C:\Program Files) and click Search. A progress indicator (hourglass) is displayed.

  5. After a period of time, the count of Access files found is displayed in the search results text box and frmSearchResults form is displayed with the full path to each file and the version of Access that created the file.

  6. Repeat these steps for Word and for Excel.

Once the COM add-in is debugged and working correctly, we need to make the DLL.

Making the DLL

When the COM add-in is working correctly, we need to make it into a DLL.

  1. In Visual Basic, click Make MyCOMAddin.dll on the File menu. The Make Project dialog box appears.
  2. Type FileSearchAdd-in.dll in the File Name text box and click OK.

The process of making the DLL registers it on the local computer.

This completes the process of creating a COM add-in that can be used to add functionality to your Office applications. If you're planning to distribute your COM add-in to other users, you will need to install all the necessary files on each user's system and register the add-in. This means that you will need to register the COM add-in (which is done for you if you create the DLL in Visual Basic 6.0 on the computer using the COM add-in) and the DsoFile.dll on the computer using the COM add-in by using the Regsvr32 command. Both DLL Files are provided as a download with this article.

Also note that after registering the File Search COM add-in on your computer, you will need to close and restart Access, Excel, and Word in order to have the COM add-in appear as a menu item on the Tools menu. If you use Outlook as your e-mail application, you will also need to close it before registering the COM add-in. IN addition, you will need to have the security settings in each application set at Medium or below in order to use the COM add-in.

Unloading the File Search COM Add-in

You can unload the File Search add-in by using the following steps:

  1. If you have already added the COM Add-Ins command to the Tools menu, skip to Step 6.
  2. On the Tools menu, click Customize, and then click the Commands tab.
  3. In the Categories box, click Tools.
  4. Drag COM Add-Ins from the Commands box over the Tools menu. When the Tools menu displays the menu commands, point to the location where you want the COM Add-Ins command to appear on the menu, and then release the mouse button.
  5. Click Close. On the Tools menu, click COM Add-Ins and do one of the following:
    • To unload an add-in from memory but keep its name in the list, clear the check box next to the add-in name.

    • To remove an add-in from the list and also from the list of registered add-ins in the registry file, select its name, and then click Remove.

      Note   Unloading or removing an add-in doesn't remove it from your computer.

Conclusion

In this article, we discussed COM add-ins; what they are, why you would want to use one, the differences between COM add-ins, application-specific add-ins and templates, and the process of creating a COM add-in. We also demonstrated creating, debugging, and making a COM add-in DLL that can search for Access, Excel, and Word files and display the version of Office the file was created in. By using the ideas and techniques described in this article, you can create your own COM add-ins and extend the functionality of your Office applications for your users.