Automating Microsoft Excel from Access (Part I)

Access developers frequently have requirements to interact with Excel in their applications (importing or exporting data).  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but if you want to give your users more control of these interactions, you will need use automation to add an additional level of manageability.

This first article in the series will discuss:

1.  Early vs Late binding

2.  How to open a new instance of Excel or select an existing instance of Excel to work with.

3.  How to elegantly close Excel

Early or Late binding

When working with Excel you can define a reference to Microsoft Excel (early binding) or you can let Access determine which version of Excel to work with at run time (late binding).  Early binding provides intelli-sense during development, making it much easier to identify the objects, properties, methods, and constants necessary to automate Excel.  After creating a reference to the Microsoft Excel ##.0 Object Library, you could then declare Excel objects with code similar to:

Dim objXL as Excel.Application
Dim objWBK as Excel.Workbook

However, early binding can bite you in the butt if a user opens your application in a newer (later) version of Office and then tries to open it again in an earlier version.  If you develop and deploy your applications using the earliest version of Office which your users have access to (this is a "best practice") and deploy it so that all users have their own copy of the application front-end (FE) on their desktop (also a "best practice"), then early binding should not be an issue.  But many developers or IT departments fail to implement Access applications using these practices and allow users to share a FE on a network server.

Office products have an amazing ability to recognize the version of Office being used, and to upgrade any reference used in the application to the latest version of that reference.  But when the application is closed, these upgraded references are saved in the database file.  Unfortunately, Office products do not have the same ability to downgrade the version from a later version to an earlier version.   For example:

Assume you have developed and deployed your application in A2010 and the Access FE is being shared on a network server.  When a user with A2013 opens the FE, the references to Excel or other Office products would be updated to the appropriate 2013 version (Microsoft Excel 15.0 Object Library) of that reference.  If someone running Office 2010 subsequently opens that same FE, the references to the Excel 15.0 Object Library will be broken (the file doesn't exist on the users computer), Access will be unable to change the reference back to the appropriate version (Microsoft Excel 14.0 Object Library), and your application will raise errors whenever any of the Excel automation code is called, and might even raise errors when you call any function of code associated with any of the other open references.

Late binding does not have this reference issue because Access automatically creates references to the appropriate version of those references when they are called.  However, late binding does not provide intelli-sense and is therefore difficult to use during development.

The hybrid solution is to this situation is to use early binding during development, then change over to late binding when you are ready to deploy.  This change over will require you to remove the reference to Excel from your VBE references and then change Excel object declarations from:

Dim objXL as Excel.Application

to:

Dim objXL as Object

I actually like to change this to:

Dim objXL as Object     'Excel.Application    '

This allows me to see the original declaration and quickly modify it to the original if need be.  In addition to modifying these declaration statements, you will also need to modify your code so that you replace any use of Excel constants with either their actual numeric values, or create your own set of Excel constants and use those.

Instantiating an Excel object in your application

After adding a reference to the appropriate version of the Microsoft Excel XX.0 Object Library, the next step in implementing Excel automation is to declare and instantiate an Excel object in your application.  If you want to open a new instance of Excel, and make it visible you can use the following commands.

Dim objXL as Excel.Application
SET objXL = CreateObject("Excel.Application")
objXL.Visible = true

This will open an instance of Microsoft Excel (hidden by default), which can then be referenced within your application.  If you don't want to see the Excel application you just created, leave off the line that makes it visible.  I prefer to make if visible during development and then comment that line out when deploying the application (if appropriate).  

If you want to refer to an existing (open) instance of Excel, you can use the GetObject method.  The syntax for this method is:

set objXL = GetObject([pathname], [class])

If you supply the [pathname] (full path and filename) and don't provide the [class] argument, Access will open the file using the application associated with the file.  If you provide the [class], and no [pathname], then Access will get an existing instance of the class.  If there is no existing instance of the class, Access will return a run-time error (429), which can be handled in an error handler.  When I want to get any instance of Excel that is already open, I use the following technique.

On Error Resume next
Set objXL = GetObject(, "Excel.Application")
if err.number <> 0 then Set objXL = CreateObject("Excel.Application")
On Error Goto ProcError 

If you have multiple instances of Excel open on your desktop, there is no telling which instance this method (GetObject) will capture.  Unfortunately, there is no easy way to identify the various open instances of Excel, but it can be done, and I'll discuss that more in the next article.

Working with Workbooks and Worksheets

I'm not going to go into this much here, I'll leave it for a later article, but in order to actually work with Excel, you must declare object variables for a workbook and worksheet:

Dim objWbk as Excel.Workbook
Dim objSht as Excel.Worksheet

and then instantiate those:

set objWbk = objXL.Workbooks.Open(Filename)
set objSht = objWbk.Worksheets(1)

or possibly:

Set objWbk = objXL.Workbooks.Add
Set objSht = objWbk.Worksheets(1)

Once you have instantiated these objects you would perform whatever other actions are appropriate for your application (copying data, pasting data, formatting a worksheet, saving the workbook, ...).  I'll discuss some of these tasks in detail in subsequent articles.

Closing Excel Elegantly

If you fail to close Excel properly, you can end up with multiple instances of Excel, open but hidden, on your computer.  If this happens, the only way to remove them will be to use the Windows Task Manager, select the Processes tab (they will not show up in the Applications tab), select the "Excel.exe" process,  and clicking "End Process".

To elegantly close the instance of Excel that you were using, you need to release all of the objects you used, generally in the order they were instantiated.  The following code will leave Excel open, in the state that you left it in (hidden/visible, with/without changes).

set objSht = Nothing
set objWbk = nothing
set objXL = nothing

If you want to leave Excel open, when you close your application, make sure you set the visible property of objXL to true before you release these objects.  I generally like to leave Excel in the same state I found it, so if I used the CreateObject method to instantiate my object, I will close excel when I am done.  If I used the GetObject method to instantiate my object I will leave it open; the code for that might look like:

On Error Resume next
Set objXL = GetObject(, "Excel.Application")
if err.number = 0 then
    bXLWasOpen = true
Else
    Set objXL = CreateObject("Excel.Application")
    bXLWasOpen = false
End If
On Error Goto ProcError 

'other code here

ProcExit:
    set objSht = Nothing
    set objWbk = nothing
    if bXLWasOpen = false then objXL.Quit 
    set objXL = nothing
    Exit Sub

Coming next

In the next installment of this series, I will discuss how to identify and select from multiple instances of open Excel applications, enumerate the spreadsheets and named ranges within a selected workbook, and link to the data in the selected spreadsheet.