A.1. Application Object

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:

 Dim xlapp as Excel.Application Set xlapp = New Excel.Application 

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

A.1.1. Application Object Properties

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.

A.1.1.1. ActiveCell

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.

A.1.1.2. ActiveChart

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.

A.1.1.3. ActivePrinter

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:

  ? Application.ActivePrinter 

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.

 xlapp.ActivePrinter = "Microsoft Office Document Image Writer on Ne00:" 

A.1.1.4. ActiveSheet

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.

 Set xlws = xlapp.ActiveSheet 

A.1.1.5. ActiveWorkbook

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.

 Set xlwb = xlapp.ActiveWorkbook 

A.1.1.6. AddIns

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.

 On Error Resume Next If xlapp.AddIns("Analysis ToolPak").Installed = True Then   MsgBox xlapp.AddIns("Analysis ToolPak").Name & _     " is installed", vbInformation End If 

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.

 For x = 1 To xlapp.AddIns.Count MsgBox xlapp.AddIns(x).Title, vbInformation Next x 

A.1.1.7. CalculateBeforeSave

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:

 xlapp.CalculateBeforeSave = False 

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

A.1.1.8. CalculateState

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.

A.1.1.9. Caption

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.

A.1.1.10. DisplayAlerts

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.

A.1.1.11. DisplayFullScreen

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.

A.1.1.12. EnableEvents

This is a property that you can read and write, which returns a Boolean value. It is useful if you want to write values into an Excel workbook without worksheet and workbook events firing, such as Change, BeforeSave, etc. I generally use it more from Excel VBA within Excel, but when I use an existing workbook that has code behind events, this is something that I sometimes have to change in Excel.

A.1.1.13. FileSearch

This property returns a FileSearch object. (Note that the FileSearch object is not an Excel object, but rather an object in the Microsoft Office Object Library. If you automate from Access, make sure that you have the Microsoft Office Object Library selected in Tools References in the VBA Editor.) Performing a file search is useful if you want to open files meeting certain criteria and perform actions on them. For example, you might want to open and print all Excel files that have a particular string in their name. The following piece of code looks for all Excel files on the C drive with the word "Product in the filename. You could also search particular directories instead of an entire drive. The FileSearch object returns a FoundFiles Object (also an object in the Microsoft Office Object Library) that you can loop through. In this example, I have a message box come up for each file that was found. However, you can use the filename to open the file or perform any other action you want where the filename and path are needed. As in the previous examples, assume you have a variable named xlapp, which is the Excel Application object.

 Dim found As Office.FoundFiles With xlapp.FileSearch   .NewSearch   .LookIn = "C:\"   .SearchSubFolders = True   .FileType = Office.msoFileTypeExcelWorkbooks   .Filename = "Product"   .MatchTextExactly = False   .Execute   Set found = .FoundFiles End With For x = 1 To found.Count   MsgBox found.Item(x) Next x 

A.1.1.14. FileDialog

This property returns a FileDialog object that lets the user select files or folders. This example opens a file dialog where you can select more than one file at a time and shows a message box of each file that the user selected. Generally, you will do some type of file operation with each file selected. For this example, you need a reference to the Microsoft Office Object Library.

 With xlapp.FileDialog(Office.msoFileDialogFilePicker)   .AllowMultiSelect = True   .Show   For x = 1 To .SelectedItems.Count     MsgBox .SelectedItems(x), vbInformation   Next x End With 

Here is the same example, except that you select a folder. It is important to note that with the folder picker you cannot select multiple folders. So, I could have simply used the number 1 for SelectedItems, but I wanted to use the same code to show you that it works. Using the same code enables you to potentially use a variable for the FileDialog type and use the same code regardless of which type of file dialog you need.

 With xlapp.FileDialog(Office.msoFileDialogFolderPicker)   .Show   For x = 1 To .SelectedItems.Count     MsgBox .SelectedItems(x), vbInformation   Next x End With 

Here is the same example, except that you select an Excel file, and the Execute method is called to actually open the file.

 With xlapp.FileDialog(msoFileDialogOpen)   .Filters.Add "Microsoft Excel Files (*.xls)", "*.xls", 1   .Show   .Execute End With 

A.1.1.15. Selection

This property returns an object, depending on the object currently selected and is read-only. For example, if a single cell or multiple cells are selected, it will return a Range object. Generally, I use this along with the Select method so that I know what is selected. Normally, you set an object equal to xlapp.Selection to use the object.

A.1.1.16. Sheets

This property returns the collection of all sheets in the active workbook. If you want to get a collection of all the sheets in a specific workbook, use this property along with the Workbook object instead. This collection contains Chart Sheets and Worksheets. There are also collections specifically for Charts and Worksheets if you only want to see one type. The following code brings up a message box with the name of each worksheet in the active workbook:

 For x = 1 To xlapp.Sheets.Count   MsgBox xlapp.Sheets(x).Name, vbInformation Next x 

Generally, use either the Charts or Worksheets collection. However, there are times when you will want to take certain actions on all sheets. You can also use a single line such as xlapp.Sheets.PrintOut to print all of the sheets in the active workbook.

A.1.1.17. ShowWindowsInTaskbar

This is a property that you can read and write, which returns a Boolean value. If you set it to FALSE, it does not show the name of each open Excel document in the task bar. Instead, you access the open files through the Windows menu option. If you set this property to trUE, you have each open file separately in the taskbar. Setting this property to FALSE is useful if you will have several files open at the same time and don't want to clutter the user interface.

A.1.1.18. UserName

This property gives you the current username and is read-only. If you have certain options that you want to show to particular users, you can use a Select...Case statement on the value of the UserName property to do that. However, I don't suggest using this if security is the primary concern. It would be more something to show user preferences. Anyone can create a username, which would be shown by this property, making it easy to defeat this method if it were used for security.

A.1.1.19. Visible

This is a property that you can read and write, which returns a Boolean value. Use this property to hide the Excel application from the user. This is sometimes nice if you are just automating Excel and saving files and don't need the user to see the result. When doing that, set this property to FALSE after the Excel application object is set to New Excel.Application. However, use this with caution because when errors occur and you exit out of the module, the Excel application is still open but not visible. You can close these instances of Excel by going into the Processes tab on the Task List in Windows and selecting End Process for each Excel instance that you did not want open. I suggest always setting this to visible when in testing mode and having error traps that close the Excel application when errors occur in production mode.

A.1.1.20. Workbooks

This returns a collection with all of the workbooks currently open. With this collection, you can call methods that Add workbooks, Close workbooks, and Open workbooks among other options. When you automate Excel and want to create a new workbook, set the variable for the workbook equal to xlapp.Workbooks.Add. To open a workbook where you want to hold a reference to the workbook in a variable, use:

 Dim xlwb As Excel.Workbook Set xlwb = xlapp.Workbooks.Open("filename.xls") 

If you just want to open a workbook, you should not put in the parenthesis. That line of code would look like this:

 xlapp.Workbooks.Open "filename.xls" 

A.1.1.21. WorksheetFunction

This property returns a WorksheetFunction object, which allows you to perform a worksheet function on arguments such as variables, individual numbers, ranges, etc. If you set a cell equal to a WorksheetFunction statement, only the value is stored in Excel. If you want to keep the formula in the cell in Excel, use one of the formula properties of the Worksheet object.

A.1.1.22. Worksheets

This property returns a collection of all the Worksheet objects in the active workbook. This collection exists for each workbook. See the Sheets property for additional information on how to use this property.

A.1.2. Application Object Methods

While there are many methods available in the Application object, there are fewer methods as compared to properties that I find useful when automating Excel. However, if you use VBA in Excel directly, there are many methods that I encourage you to evaluate, such as AddCustomList, Goto, OnTime, OnKey, OnUndo, Volatile, and Wait. While these methods can be useful, they are less likely to be useful when automating Excel. The following methods are the ones I find most useful.

A.1.2.1. Calculate, CalculateFull, CalculateFullRebuild

These methods are very similar; they are all used to force a recalculation. The Calculate method can be called from the Application, Worksheet, and Range objects. The CalculateFull method performs a full calculation of all open workbooks. The CalculateFullRebuild method also performs a full calculation of all open workbooks, and it also rebuilds all of the dependencies. The CalculateFullRebuild method simulates entering all of the formulas in the workbooks again. The code example below assumes that you have a variable called xlapp, which is the Excel Application object, does a full calculation, and rebuilds all dependencies.

 xlapp.CalculateFullRebuild 

The CalculateFullRebuild method is useful if you have custom functions that are not set to be volatile, so you need to force the calculation. By simply calling the Calculate method, you do not get the same result. I found that out the hard way when I first began writing custom functions that were called from the worksheet in a formula.

A.1.2.2. ConvertFormula

There are times when you have a range or a formula that is in one format, and you need to have it in another because a method needs it that way. This method makes those formatting changes. The first code example shows how to change an A1-style range to an R1C1-style range and puts it into a string variable called formulastr.

 Formulastr = xlapp.ConvertFormula("$A$1:$A$6",Excel.xlA1,Excel.xlR1C1) 

The result of this formula is R1C1:R6C1. The next example changes a Sum formula in A1-style to a Sum formula in R1C1-style.

 formulastr = xlapp.ConvertFormula("=Sum($A$1:$A$6)", Excel.xlA1, Excel.xlR1C1) 

The result of this formula is =SUM(R1C1:R6C1).

A.1.2.3. InchesToPoints

The InchesToPoints method is useful when you have properties that need to be set in points (such as margins in page setup) but you know what you want in inches. This method returns a number representing the number of points. The following code example prints the number of points that are equivalent to two inches in the immediate window.

 Debug.Print xlapp.InchesToPoints(2) 

The result of this method is 144.

A.1.2.4. Quit

This method is used to close down the Excel application instance. The code would look like the following code example:

 xlapp.Quit