Linking to Multiple Office Applications

The start of this chapter mentioned that one of the fundamental advantages of COM Add-Ins over xla or xlam Add-Ins is that the same DLL can target multiple Office applications. All you need to do to achieve this is to add a new Add-In Designer class for each application you want to target, in exactly the same way you added the Designer to target Excel previously in the chapter. Of course, you still have to handle the idiosyncrasies of each application separately.

In the following simple example, you make the Sequence function available through the COM Add-Ins collection in Access and use it to populate a list box on a form.

Start by opening the Excel2007ProgRef project and adding a new Add-In class to the project. In the Properties window, change its name to AccessAddIn, set its Public property to True (ignoring any warnings), and complete the Designer's form as follows:

Add-In Display Name

Excel 2007 Prog Ref Sequence

Addin Description

Example to expose the Sequence function through Access' COM Addins.


Microsoft Access

Application Version

Microsoft Access 12.0

Initial Load Behavior


Click View O Code and copy the following into the Designer's code module:

' Simple COM Add-In to provide the Sequence function to MS Access, ' through Access' COMAdd-Ins collection

Private Sub Add-InInstance_OnConnection(ByVal Application As Object, _

ByVal ConnectMode As Add-InDesignerObjects.ext_ConnectMode, ByVal Add-InInst As Object, custom() As Variant)

' Set the Add-In instance's Object property to be this class, ' providing access to the Com Add-In's object model from within VBA. ' Note that we don't use Set here! Add-InInst.object = Me

End Sub

' Property to return a reference to our Simple class, providing access ' from VBA:

'vaSeq = Application.ComAdd-Ins("Excel2007ProgRef.ComAdd-In").Object _

Public Property Get SimpleFuncs() As Simple

Set SimpleFuncs = New Simple End Property

Save the project and use File O Make Excel2007ProgRef.dll to build the DLL. Start Access 2007 with a blank database, create a new form, add a list box, and copy the following code into the form's code module:

Private Sub Form_Load()

Dim vaSequence As Variant

Dim i As Integer

' Use the COMAdd-In to get the sequence

vaSequence = Application.COMAddIns("Excel2007ProgRef.AccessAddIn")

.Object.SimpleFuncs.Sequence(5, 10, 2)

' Add the sequence to the list box

List0.RowSourceType = "Value list"

For i = LBound(vaSequence) To UBound(vaSequence)

List0.AddItem vaSequence(i)


End Sub

Save the form and run it to show the COM Add-In at work (see Figure 18-3).

Figure 18-3
0 0

Post a comment