Michael Schmalz - Integrating Excel and Access

The Application object contains many properties and methods that can be very useful. In some cases, the collections of objects contained under the Application object also appear in the Workbook and Worksheet objects. When that is relevant, I'll note it in the text. In the examples shown, assume that the following code has already been entered in the VBA module from Access:

By doing this, the variable xlapp can be used to get the properties and methods discussed below.

The Application object's properties include a number of key pieces that provide your automation with a perspective on Excel, similar to the perspective users get through a GUI.

This property is a range object that references the active cell in the application. If you have multiple workbooks open, and each has multiple worksheets, it refers to the active cell in the active workbook on the active sheet. If you have a range selected on the active worksheet, it refers to the cell in the range currently showing in the Name Box on the formula bar. The property returns a Range object and is read-only.

This property returns the active embedded chart or chart sheet. If there are no active charts, this property returns Nothing . This property is read-only.

The ActivePrinter property returns the name of the active printer; this is the printer that comes up when you go to File Print on the menu. This is a property that you can read and write. Programming this property is very useful if you know the names of the printers available and you want to print a workbook or worksheet to a specific printer. This could come in handy if you automate Excel reports through Access and want to print reports to different printers. For example, if you work in an organization that has network printers in different locations, you can set code to automatically print different worksheets to each printer. Your best bet to get the exact names of your printers is to print something to the printer, go into the Immediate Window in Excel, and type in:

This formula gives you the exact name of your printer. The following code sets the current printer to the Microsoft Document Image Writer, if it is an installed printer in the same location.

The ActiveSheet property returns a Sheet object that represents the current active sheet for the application. There is also an ActiveSheet property available as part of the Workbook object that returns the active sheet for that particular workbook. This property is useful to set a variable equal to the active sheet to work with it. If you have a variable called xlws and want it to refer to the active sheet for the application, the following code shows how. The ActiveSheet property is read-only.

The ActiveWorkbook property returns a Workbook object that represents the currently active workbook in Excel. While you will generally work on a specific workbook, this can be useful when you have code working on multiple workbooks. You can set your variable to refer to the currently active workbook; note that if the active workbook changes after you set your variable, the variable still refers to the workbook that was active when it was set. If you have a variable called xlwb and you want to refer to the active workbook, see the following line of code. The ActiveWorkbook property is read-only.

The AddIns property is a collection of all of the Add-Ins available to Microsoft Excel. To see all of the Add-Ins available, use an AddIn object and a For Each...Next loop to cycle through all of them. That may be useful from Excel, but from Access your use for this generally revolves around seeing whether a particular Add-In is installed. For example, the following If...Then statement only runs if the Analysis ToolPak is installed.

You need to have the On Error Resume Next line as the first line because if the particular Add-In is not available on the computer (versus being on the Add-Ins list but not installed), referring to it by title generates an error. It is important that the string needed to bring up the AddIn object is in the Title Property, which is not the same as the Name property. The Title property of the AddIn object is not documented in Excel or on the object browser, but it is available. Since Add-Ins are referred to by an Index , you can loop through the Add-Ins by going from 1 to the count of the number of Add-Ins. The following code gives you a message box with the title of each AddIn object. You need an integer variable called x to use this code.

This is a property that you can read and write. Generally, you want this property to be trUE . However, there may be times when you don't want workbooks recalculated before they are saved. In those cases, you can set this property to FALSE using the following line of code:

If you change this property for a particular purpose, be sure to change it back at the end of the code.

This property is a read-only property that tells whether Excel is currently performing calculations. This is useful if you want to wait until all calculations are done before running a portion of code. There are three constants that can be returned (the integer value of the constant is in parenthesis): xlDone (0), xlCalculating (1), and xlPending (2). Generally, you will check that the property is equal to 0, which means that there are no calculations being performed.

This is a property that you can read and write, which gives you the name on the Title Bar of Excel. You might want to set this if you build an application that you want to show up differently.

This is a property that you can read or write, which returns a Boolean value. If you set it to FALSE , while your VBA code runs, alerts are automatically given the default response.

This is a property that you can read and write, which returns a Boolean value. Generally, this property is set to TRUE if you need more space to display a report, chart, etc. You also want to have an event programmed that returns the screen back to normal by setting this property to FALSE .