Creating a Microsoft Office 2000 COM Add-in

John Clarkson
Microsoft Corporation

June 1999

Applies To: Office 2000 Developer

Summary: This article describes how to use Microsoft® Office 2000 Developer to develop a COM add-in that creates a simple report in Microsoft FrontPage®, Microsoft Word, or Microsoft PowerPoint® with data from a Microsoft Access database. (9 printed pages)

Click to view or copy the mso2kaddin sample discussed in this article.

Introduction

Because most Microsoft Office 2000 applications support the Component Object Model (COM) add-in architecture, you can use the same tools and installation file formats (an ActiveX® .dll or .exe) to develop add-ins for most Office applications. By building COM add-ins, you can extend the functionality of your Office-based applications without adding complexity for the user.

You can also create add-ins for the Visual Basic® Editor. You can make such add-ins available from—or to—any application that supports Visual Basic for Applications 6.0, including applications other than Microsoft Office.

This article describes how to use Microsoft Office 2000 Developer to develop a COM add-in that creates a simple report in FrontPage®, Word, or PowerPoint® based on data from an Access database.

Choosing Development Tools

For Office developers, the best choice for creating COM add-ins is either Microsoft Visual Basic (version 5.0 or 6.0) or Microsoft Office 2000 Developer. Both Office 2000 Developer and Visual Basic 6.0 provide an add-in designer to quick-start your add-in development. The designer has a graphical user interface component that allows you to quickly specify basic characteristics, such as the add-in's load behavior and target application. You can also use Visual Basic 5.0 to develop COM add-ins for Office 2000, but note that Visual Basic 5.0 doesn't include the add-in designer.

You can also develop COM add-ins for Office 2000 by using any application development environment that supports the creation of COM objects, such as Microsoft Visual C++® or Microsoft Visual J++®.

Using Visual Basic 6.0

To create a COM add-in in Visual Basic 6.0, click New Project on the File menu, and then click Add-In Project in the New Project dialog box. You'll get a project that includes a form and an add-in designer. The add-in designer in Visual Basic 6.0 comes populated with code in seven different procedures, including those for the OnConnection and OnDisconnection events, and a function to add an Office CommandBarControl object to an Office application menu. With Visual Basic 6.0, you also get the ability to run your compiled dynamic-link library (DLL) in break mode in order to debug your project.

Using Microsoft Office 2000 Developer

With Office 2000 Developer you get an add-in designer (referred to in the remainder of this article as simply "designer") that's nearly identical to the Visual Basic 6.0 designer. The differences are summarized in the following table.

Visual Basic 6.0 designer Office 2000 Developer designer
Includes code for seven procedures, including the OnConnection and OnDisconnection events. Not populated with code.
Display Name and Description fields are populated. Display Name and Description fields are blank.
Load behavior settings include two command-line options. Load behavior settings include Load on Demand and Load at Next Startup Only.

Office 2000 Developer also allows you to save your projects as separate .vba files. The .vba project files are accessible in any of the Office applications that support the Visual Basic Editor, which means you can open and work on the same project across applications. Being able to store Visual Basic for Applications (VBA) code in a separate file may make it a lot easier for you to move around in the Office/VBA environment.

Setting Add-in Options with the Add-in Designer

The key component in building a COM add-in is the designer. A single designer is added automatically when you open a new add-in project. The designer allows you to easily set the options listed below.

General Tab

Addin Display Name

This value appears in the COM Add-ins dialog box.

Addin Description

Sets the value of the Description property of the COMAddIn object.

Application

Sets the host application.

Application Version

Determines for which version of the host application the add-in is intended.

Initial Load Behavior

Determines how the add-in loads. The available selections are explained in the table below.

Selection Description
Load at Next Startup Only Allows you to automatically connect the add-in at the next start of the host application. On subsequent startups the add-in is loaded on demand, usually by clicking a menu item or toolbar button.
Startup Loads the add-in automatically when the host starts. The add-in then remains loaded until explicitly unloaded.
Load on Demand Allows users to load the add-in whenever they want, usually by clicking a menu item or toolbar button.
None Means the add-in is not loaded, and no load behavior is specified.

Advanced Tab

Satellite Dll Name

The name of a .dll file containing translated resource strings; this file must be in the same folder as the add-in's .dll file.

Registry Key for Additional Addin Data

Determines the registry subkey where any additional, optional data accompanying the add-in is stored.

Addin Specific Data

Additional optional data to be stored in the registry subkey.

Using More Than One Designer

Each designer is specific to a single host application. To build a COM add-in for Excel, you select Excel in the designer as the host application. If you want to extend your designer to support a second host, let's say Word, you add a second designer to your project and select Word as the host application in that designer. When you build the DLL, registry settings are made so that your add-in is available to the appropriate applications.

To add another designer to your project

  1. In the Visual Basic Editor, open an add-in project.

  2. On the Insert menu, click Components.

  3. In the Components dialog box, click the Designers tab.

  4. On the Designers tab, select the check box for Addin Class, and then click OK.

  5. On the Insert menu, click Addin Class. A new add-in project will appear.

Registering Your Add-in Automatically

The registry entry for your add-in is made automatically at the time you build the DLL. The registry subkey is \HKEY_CURRENT_USER\Software\Microsoft\Office\appname\AddIns. The Description entry is your setting for the Addin Description option in the add-in designer. The FriendlyName entry is your setting for the Addin Display Name option in the designer.

On the Advanced tab of the designer, you can enter the name of a resource file, a subkey to store additional data, and the data you want stored at this registry subkey.

The registry entries for your add-in are removed when the add-in is removed. (For example, by using the COM Add-Ins dialog box, which you can open by clicking COM Add-Ins on the Tools menu, you can clear the registry entries.) The registry entries are re-added when the add-in is loaded again.

Debugging Your COM Add-in Project

You can run your COM add-in project, set breakpoints, and use the various debugging windows just as you do with other types of VBA projects. The following procedure explains how.

To debug a COM add-in project

  1. Open your COM add-in project. Make sure it is the active project.

  2. Set breakpoints as appropriate.

  3. On the Run menu, click Run Project.

  4. Open a new instance of the application that is hosting your COM add-in.

  5. Launch the add-in.

Creating a COM Add-in for Multiple Hosts

The following procedure provides the basic steps to create a COM add-in for two or more Office applications.

To create and run a COM add-in for Word and PowerPoint

  1. In Word, open the Visual Basic Editor.

  2. In the Visual Basic Editor, click New Project on the File menu, and then click Add-In Project. Do this one more time to add a second project.

  3. Rename the designer in the second project to indicate which application is the host; for example, dsrWord. (Press F4 to display the designer's Properties box, where you can change the name.)

  4. Drag the designer from the second project to the first project, and then close the second project without saving. (For other ways to add designers to a project, see the section "To add another designer to your project," earlier in this article.)

  5. On the General tab, enter the appropriate settings in each designer's user interface. Set the Initial Load Behavior option in each to Startup or Load at Next Startup Only.

  6. For an example of code you might add to a designer, see the designer code in the attached sample. If you want code to add and remove command bar buttons, see the modShared.bas file and consider adding it to your project.

  7. On the File menu, click Make <projectname.dll>.

  8. Close Word; open either Word or PowerPoint and run the add-in.

Of course, all you've got at this point is code to connect and load the add-in. For more useful behavior, see the code in the sample applications that ship with Microsoft Office Developer, and the application accompanying this article.

The COM Add-ins dialog box is the user interface element that allows you to add and remove COM add-ins in Office 2000 applications. By default, there's no menu item or toolbar button to display the COM Add-ins dialog box, but you can easily display it in any Office 2000 application by using the following procedure.

To add a menu item or toolbar button for the COM Add-ins dialog box

  1. In the Office 2000 application, click Customize on the Tools menu.

  2. Click the Commands tab.

  3. In the Categories list, click Tools.

  4. In the Commands list, click COM Add-ins. You may have to scroll through the list to find it.

  5. Drag the COM Add-ins command to a toolbar or a menu.

  6. Close the Customize dialog box.

Overview of the Northwind Report COM Add-in

This sample COM add-in uses an Access query as a data source, and builds a simple tabular report in Word, FrontPage, and PowerPoint. The query pulls data from the Northwind Traders sample database, Northwind.mdb, which ships with Office 2000 and Microsoft Visual Studio® 6.0. The project file is available as a download with this article.

To try it out

  1. After downloading Nwind.vba, open Word, and open the Visual Basic Editor in Word.

  2. In the Visual Basic Editor, open Nwind.vba.

  3. On the File menu, click Make Nwind.dll.

  4. Close and restart Word.

  5. On the Tools menu, click Northwind Report.

Note that these instructions also work for FrontPage and PowerPoint.

Comments on the Code

This section provides an overview of the code included in the sample add-in.

The Shared Code Module

The shared code module (modSharedCode.bas) is imported from the Image Gallery sample that ships with Office 2000 Developer. The only changes made were to change the string constants from "Image Gallery" to "Northwind". Note that this same code module is also used in the DevTools and VB_COM_AddIn samples provided on the Office 2000 Developer CD-ROM. A great example of code re-use!

The CreateAddinCommandBarButton custom function adds a "Northwind Report" menu item to the Tools menu in the host application. The function is called from the OnConnection event of the AddInInstance object. It uses the FindControl method of the CommandBar object to verify that the menu item doesn't already exist, and then uses the Add method of the Controls collection to add the menu item as a CommandBarButton object. A With…End With structure sets the new menu item's properties.

Set ctlBtnAddIn = cbrMenu.Controls.Add(Type:=msoControlButton, _
        Parameter:=CTL_KEY)

With ctlBtnAddIn
    .Caption = CTL_CAPTION
    .Tag = CTL_KEY
    .Style = msoButtonCaption
    .OnAction = PROG_ID_START & AddInInst.ProgId _
         & PROG_ID_END
End With

The OnAction property in the code fragment above specifies the procedure that runs when the Northwind Report menu item is clicked. The AddInInst object in this statement is a reference to an AddIn object representing the instance of the add-in. The ProgID property of the AddIn object returns the ProgID as obtained from the add-in's registry subkey. The two constants preceding and following the AddInInst object provide the opening and closing angle brackets required by the OnAction property syntax: 

object.OnAction = !<ProgID>

Finally, the CommandBarButton object is passed back to the designer in the OnConnection event. The object variable in the OnConnection event is declared by using the WithEvents keyword, which specifies that this variable will be used to respond to events triggered by the menu object in the host application.

The RemoveAddinCommandBarButton procedure removes the command-bar button if the user disconnects the add-in. The procedure is called by the AddInInstance object's OnDisconnect event, which passes in a custom constant indicating whether the user disconnected the add-in, or whether the host was shut down.

The Three Designers

Each of the three designers contains the same three event procedures, which do the following:

An object variable is declared to respond to events triggered by the CommandBarButton object. This object variable is returned by a function called by the AddInInstance object's OnConnection event.

The OnConnection event calls the CreateCommandBarButton function in the modSharedCode standard module, which adds the Northwind Report command to the Tools menu of the host application. The Set statement in the OnConnection event is used to assign a reference to the CommandBarButton object.

The code in all three designers is identical, with one exception: In the Word designer's OnDisconnection event, there is additional code to save the Normal template. Unless the Normal template is saved first, the Northwind Report menu item added to the Tools menu will not be deleted.

Finally, the object variable's Click event calls a procedure in the modNWindReport module, which creates the report.

The Report Code Module

The structure of this module is very simple. There is a controlling procedure, ReportMaster, which first creates a Recordset object; and then, depending on which application is currently the host, calls one of three procedures to create the report document within the host.

If you're unfamiliar with using VBA to build solutions in FrontPage 2000, you should search the FrontPage Help topics for "Getting Started with Visual Basic in Microsoft FrontPage."

The BuildFrontPageDocument custom function begins by adding a table containing Northwind data to a file called Nwind.htm. First it uses the FileExists method of the FileSystem object to determine if such a file already exists.

Set fsFileObject = New Scripting.FileSystemObject
bolFile = fsFileObject.FileExists _
    ("C:\My Webs\Reports\Quarterly\Nwind.htm")

If not, the procedure creates one by opening a new Web object and using the LocatePage method of the Web object to set a reference to a PageWindow object variable.

Webs.Open ("C:\My Webs\Reports\Quarterly")
Set fpPageWin = Application.ActiveWeb.LocatePage("Nwind.htm")

Then it builds a String variable (strHTML) that contains the HTML code required to build a table populated with data from the Access query, and inserts the HTML on the page by using the insertAdjacentHTML method of the <BODY> element in the Page object model.

Call fpPageWin.Document.body. _
        insertAdjacentHTML("BeforeEnd", strHTML)

For the code to create the Recordset object and build the reports in Word and PowerPoint, you can see similar examples discussed in these articles on the Office Developer Web site:

"Passing Tabular Data into a Microsoft Word Report," located at www.microsoft.com/worddev/articles/TabDat.htm.

"Use Visual Basic to Build an Access Data Presentation in PowerPoint," located at www.microsoft.com/powerpointdev/articles/ProgSlid.htm.

Conclusion

COM add-ins in Office 2000 offer a number of advantages over the previous add-in architecture. They're simpler to develop, it's possible to develop these add-ins with a variety of tools, you can use the same tools and techniques across all Office applications, and your product can be used across all Office applications.

Other Resources

Here is a survey of resources on COM add-ins for Office 2000 that were available at the time this article was written (June 1999).