Building a COM Add-in for Microsoft Office XP Using Microsoft Visual Basic 6.0Click 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® 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)
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
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.
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.
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.
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.
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.
The process of creating a COM add-in can be broken down into three general steps:
- Create a new DLL/EXE COM add-in project.
- Implement the IDTExtensibility2 interface.
- 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:
|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:
- 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.
- Every time the application starts.
- 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:
- The user starts the host application and the add-in's load behavior is specified to load when the application starts.
- The user loads the add-in in the COM Add-ins dialog box.
- 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:
|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:
- The user clears the check box next to the add-in in the COM Add-ins dialog box.
- 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.
- The Connect property of the corresponding COMAddIn object is set to False.
The OnDisconnection event procedure takes two arguments, described in the following table:
|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:
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.
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, xlApp will 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:
- Start Word 2002.
- Create a new blank document.
- On the Tools menu, click Macro, and then click Visual Basic Editor.
- In the Project Explorer, double-click ThisDocument to open the ThisDocument module.
- In the Object box in the Code window, click Document, and then click the drop-down arrow in the Procedure box.
- 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:
- Start Excel 2002.
- On the Tools menu, click Macro, and then click Visual Basic Editor.
- In the Project Explorer, double-click ThisWorkbook to open the ThisWorkbook module.
- 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).
Figure 2. Application-level events in Excel
To see the events associated with an Access form, do the following:
- Start Access 2002.
- Under Objects, click Forms, and then click New.
- On the View menu, click Properties.
- In the Properties window, click the Event tab.
You can scroll through the list of events associated with the form (see Figure 3).
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.
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 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.
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:
- 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.
- frmSearchResults A form used to display the results of the file search in Access.
- 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.
- 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.
- dsrFileSrchExcel Add-in designer for Excel
- dsrFileSrchWord Add-in designer for Word
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:
- Start Visual Basic 6.0.
- 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.
- 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.
- 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
- 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.
- Next, modify the
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.
Figure 4. The File Search form
- Double click the
cmdClosecommand button and insert the following in the
cmdClose_Clickevent procedure to remove the form from memory when the user clicks the button:
- 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
- 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
Next, we will add the application-specific procedures to the project.
- 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
... Me.txtSrchResults = dctDictionary.Count Set p_frmSrchResults = New frmSearchResults ' Create a table to hold the search results. Call CreateTable ...
Next, we call the
... 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 ...
- The recordset is then updated which copies the results to the table. Then we display the
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
In addition to setting a reference to the Scripting Runtime object library, we also set a reference to
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
... 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." ...
- 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.
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
Next, we need to create a form to display the results of the file search in Access.
- Add a new form (Project menu, Add Form) and rename it
- Add controls to the
Controls Properties Values Label1 Caption Search Results ListBox Name lstResults CommandButton Name cmdClose Caption Close Report
- Double-click the
- Open the
... 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" ...
- 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
Next, we need to configure the add-in designers so that each application will properly load and configure the COM add-in.
- 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
- To do this, change the following statement in the declarations section from:
Private p_frmCOMAddIn As frmCOMAddIn to: Private p_frmFileSrch As frmFileSearch
- Now change the name of the
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:
- 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.
- 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.
- Click the File Search Add-in item on the Tools menu. The
- Type in a starting search directory (for example, type C:\Program Files) and click Search. A progress indicator (hourglass) is displayed.
- After a period of time, the count of Access files found is displayed in the search results text box and
- Repeat these steps for Word and for Excel.
Once the COM add-in is debugged and working correctly, we need to make the DLL.
When the COM add-in is working correctly, we need to make it into a DLL.
- In Visual Basic, click Make MyCOMAddin.dll on the File menu. The Make Project dialog box appears.
- 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.
You can unload the File Search add-in by using the following steps:
- If you have already added the COM Add-Ins command to the Tools menu, skip to Step 6.
- On the Tools menu, click Customize, and then click the Commands tab.
- In the Categories box, click Tools.
- 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.
- 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.
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.