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
- 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:
- Add a reference to the library via Tools > References...
- Declare your object variables using the appropriate COM type
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:
- Uncheck the reference via Tools > References...
- Switch your object variable declarations to use the generic
- 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!
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!