Microsoft Office COM Automation Libraries

One of the strengths of developing within Microsoft Access is the ability to integrate with the other applications in the Microsoft Office suite.

The most reliable way to do this is via COM Automation.

Use Early Binding for Development

With early binding, you are "binding" your code to the external Office libraries (Excel, Word, Outlook, etc.) at compile time.  

There are several advantages to using early binding:

• Compile-time type checking
• IntelliSense
• Event handling
• Better performance (though likely negligible in practice)

For these reasons, I always recommend using early binding for development.

Early binding is a two-step process:

1. Add a reference to the library via Tools > References...
2. Declare your object variables using the appropriate COM type

Dim xl As Excel.Application

I do not recommend using the As New construct with COM objects, because it makes it harder to convert to late binding for deployment, as we will see below.

Use Late Binding for Deployment

The disadvantage with early binding–and it's a HUUUGE disadvantage–is that if your user has an older version of the Office application than you are developing against, then your Access application probably will not work at all.

For example, if you are developing against Excel 2016, then any user with Excel 2013, or Excel 2010, or no version of Excel at all, will not be able to use your application.  And not just the parts that rely on the Excel library.  None of it will be usable.  That's because VBA checks all of the early-bound references at startup when your user launches the application.

The best practice, then, is to switch your application from early binding to late binding prior to deployment.  This is also a two-step process, though you will need to repeat the second step for each affected object variable:

1. Uncheck the reference via Tools > References...
2. Switch your object variable declarations to use the generic Object type
3. Declare constants to take the place of application-specific constants

'--== STEP 2. Switch declarations to use the generic Object type ==--
'Dim xl As Excel.Application   'early binding; leave for future development
Dim xl As Object  'Excel.Application  (use late-binding for deployment)

'--== STEP 3. Declare local versions of application constants ==--
Const wdSaveChanges As Long = -1
Const xlWorksheet As Long = -4167

' OR to recreate entire enums:
Enum xlSheetType
    xlChart = -4109
    xlDialogSheet = -4116
    xlExcel4IntlMacroSheet = 4
    xlExcel4MacroSheet = 3
    xlWorksheet = -4167
End Enum

The easiest way to find all of the variables and constants that need to be converted is to compile your application (Debug > Compile).  VBA will highlight all of the instances of your object variables and constants that rely on early binding to the reference you just removed.

VBA will only highlight one error at a time, so you may want to use the Debug > Compile shortcut key ([Alt] + [D],[L]) to shorten the Compile-Identify-Fix cycle.

You can also use conditional compilation with a project-level compile constant to switch between the early-binding and late-binding declarations.  That's beyond the scope of this article.

Use Early Binding for Event Handling

It's not always possible to use late binding when deploying your application.  

If you want to handle events (e.g., Excel's Workbook.Open event) for a referenced Office application, you will need to rely on early binding.  You need to be aware of the limitations of this approach, namely that all of your users need to have the same or newer version of the external application (e.g., Excel) that you do while developing.

This also means that you will need to maintain older versions of Office applications for development purposes.  Be sure to take this into consideration when deciding whether to go down this path.

Use CreateObject to Launch a New Process

I said earlier that I don't recommend using the New keyword when creating new COM object variable instances.  What should you use instead?

The CreateObject function launches a new instance of the COM server.  "Server" in this context is simply the COM terminology for the external process–such as Excel.exe, Winword.exe, etc.–that serves the functionality of that application.

Each line of code below creates a new, hidden process in Windows.  If you open Task Manager and add the "Command Line" column to the Details view, you can identify the application instances you created because they will have /automation -Embedding appended to their command line:

"C:\Program Files\...\MSACCESS.EXE" -Embedding
"C:\Program Files\...\EXCEL.EXE" /automation -Embedding
"C:\Program Files\...\WINWORD.EXE" /automation -Embedding
"C:\Program Files\...\OUTLOOK.EXE" -Embedding
"C:\Program Files\...\POWERPNT.EXE" /AUTOMATION -Embedding
"C:\Program Files\...\MSPUB.EXE" /Automation -Embedding

• Set ac = CreateObject("Access.Application"): MsAccess.exe
• Set xl = CreateObject("Excel.Application"): Excel.exe
• Set ww = CreateObject("Word.Application"): Winword.exe
• Set ol = CreateObject("Outlook.Application"): Outlook.exe
• Set pp = CreateObject("PowerPoint.Application"): PowerPoint.exe
• Set pu = CreateObject("Publisher.Application"): MsPub.exe (note: Publisher immediately crashes when launched via CreateObject on my machine; it may not be possible to create new instances of this process via Automation; instead, you may need to use GetObject as described below)

Quit any Office application processes that you create

Make sure you .Quit any Office application that you generate via CreateObject.

It's not enough to set the object variable to nothing.  In fact, explicitly setting the object variable to nothing is usually unnecessary (except when you need to avoid circular references or the object variable is a global variable).  When the reference counter reaches zero, the object will be released from memory automatically.

That said, it doesn't hurt to explicitly set the object reference to Nothing, and it does signal your intent to other developers that you no longer intend to use the object variable moving forward.

Here's some sample code that shows how to open, show, quit, and release memory for the six most common Office applications:

Sub TestCom()
    
    'Dim ac As Access.Application
    Dim ac As Object
    Set ac = CreateObject("Access.Application")
    ac.Visible = True
    ac.Quit
    Set ac = Nothing

    'Dim xl As Excel.Application  'early binding
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    xl.Quit
    Set xl = Nothing
    
    'Dim ww As Word.Application  'early binding
    Dim ww As Object
    Set ww = CreateObject("Word.Application")
    ww.Visible = True
    ww.Quit
    Set ww = Nothing
    
    'Dim ol As Outlook.Application  'early binding
    Dim ol As Object
    Set ol = CreateObject("Outlook.Application")
    'ol.Visible = True  'Object doesn't support this property or method
    ol.Quit
    Set ol = Nothing
    
    'Dim pp As PowerPoint.Application
    Dim pp As Object
    Set pp = CreateObject("PowerPoint.Application")
    pp.Visible = True
    pp.Quit
    Set pp = Nothing
    
    'Dim pu As Publisher.Application
    Dim pu As Object
    'Set pu = CreateObject("Publisher.Application") 'crashes immediately on my system
    Set pu = GetObject(, "Publisher.Application")  'Publisher must already be open
    'pu.Quit   'Don't force close the user's already running instance!
    Set pu = Nothing
    
End Sub

Use GetObject to Grab an Existing Process

Sometimes, instead of creating a new process, you want to grab one that is already running.

To automate an Office application that is already running, use the GetObject function.

In the case of Publisher, this seemed to be the only way to automate the application as the process crashed immediately after I launched it via CreateObject.

In the case of Outlook, there is no Visible property that you can set to True.  If you want your users to be able to interact with the application, you should grab an already-open process using GetObject.

If you grab a running instance of an Office application, you should clean up the object variable by setting it to Nothing.  However, you don't want to force the application to close via .Quit; your users won't appreciate that!  

Referenced articles

External References

UPDATE [2021-12-05]: I added a third step to the process of converting code from early binding to late binding: declaring application-specific constants.  Thanks for the reminder, Jack Stockton!