5.1. High-Level Excel Objects

There are three primary objects that your Access VBA needs creating in order to automate Excel: the Excel Application object, the Excel Workbook object, and the Excel Worksheet object. These objects are created in that order.

In order to use the Excel objects , you need to create a reference to Excel from Access VBA. When you are in an Access database, click on the Modules table and then press the New button. This will bring up a new module. You can also do this from the Insert menu by going to Insert Module. In the Module View, go to Tools References, and Access brings up the References Dialog as shown in Figure 5-1. Scroll down to the Microsoft Excel Object Library and check its box. The library is Version 11.0 for Office 2003, but if you have different versions, choose the highest version available.

Figure 5-1. The References dialog box, which lets you set a reference to any applications that you want to automate, including Excel, Word, PowerPoint, or MapPoint

After creating that reference, go to Insert Procedure. This brings up the dialog box shown in Figure 5-2. For this example, call the procedure GetExcel, and click the radio buttons for Sub and Public.

Figure 5-2. The Add Procedure dialog, which adds new subs, functions, or properties to a module

5.1.1. The Application Object

In order to use the Application object, you must first use the Dim statement to create a variable to access the object. Once you have created the object, you will use the Set statement to set the variable to a new Excel Application object. This opens Excel, although you won't be able to see it until you set the Visible property of the Application object to TRue, as shown in Example 5-1.

Example 5-1. Creating an Excel application object

 Public Sub GetExcel( ) Dim xlApp As Excel.Application Set xlApp = New Excel.Application xlApp.Visible = True End Sub 

This code is the starting point of any Excel automation task because you need the Excel Application object to use the other objects in Excel. The only risk of using this method of automation is that if you use a different version of Excel than the people using the application, your users would need to change the reference to their own version. While this is not that difficult, it makes distribution a challenge for some of your novice users since they would need to know how to change references in order to use the application.

You can eliminate this problem by using something called late binding . In late binding, you create a variable As object instead of specifying an exact object type, and then use Createobject to produce the required object. (Setting a reference to Excel and using the New keyword to set a reference is called early binding .) To use late binding, use the code in Example 5-2.

Example 5-2. Using late binding to create an Excel application object

 Public Sub GetExcelLate( ) Dim xlApp As object Set xlApp = Createobject("Excel.Application") xlApp.Visible = True End Sub 

While it would seem like you could just use late binding all the time, it does make it more difficult to write the code, as some of the Visual Basic Editor's automation isn't available when you use late binding. For example, in the early binding example, when you type xlApp.Visible, by the time you got to vi, you would see the entire word, visible. At that point, press the tab key, and "visible" will be filled in. This is especially helpful when you search for a method or property to use.

When I am doing work for a client and I don't know their version of Excel or whether they have several versions of Microsoft Office running at their company, I use object and late binding to automate Excel. While this makes it more difficult to program, it is certainly easier for the user.

5.1.2. The Workbook Object

The Application object does very little on its own, but it is a necessary foundation for using the other objects in Excel. As you may know, when you use Excel and want a new workbook, go to either File Open or File New. When you automate Excel from Access, you need to use the Workbooks object, which is actually a collection of objects. When you do that, you will see several methods available.

The two main methods that will be covered here are the Add method and the Open method. The Add method simulates the File New functionality that you access from Excel. The Open method is used less frequently, but can be helpful if you are using prebuilt templates, or just want to import data. However, just about anything that you could put into a template can be done from VBA in Access. And, while it adds to the programming, when creating the material in VBA, you do not need to be concerned about whether the user has the correct template available on his computer. Example 5-3 and Example 5-4 show how to create a new workbook and open an existing workbook. The code to create the application is also included.

Example 5-3. Workbooks collection add method example

 Public Sub GetExcel( ) Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Add xlWb.SaveAs "Test.xls" End Sub 

Example 5-4. Workbooks collection open method example

 Public Sub GetExcel( ) Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Open("C:\Devwork\Chapter5.xls") End Sub 

The Add method example uses the SaveAs method of the Workbook object to save the workbook. In most cases, when you create a new workbook you will want to save it. When you want to save the workbook, you have several choices: Save, SaveAs, SaveAsXMLData or SaveCopyAs. The SaveAsXMLData method is new to Office 2003 and is only available in the Professional edition. After you have used the SaveAs method, you can use the Save method to continue saving the workbook. The SaveCopyAs method is useful if you want to save the workbook in its current state but continue working with the original. This can be useful if you are working with an Excel workbook that will be sent to various users and you want each to get progressively more information.

For example, if you are building a report that has divisional and corporate information, and you have one group of users that gets the divisional data and another group that gets divisional and corporate data, you can create a new workbook, use the SaveAs method, and call it Corporate.xls. Add the divisional data and save the workbook using the SaveCopyAs method, calling it Divisional.xls. When you add the corporate data, use the Save method to save it. If you had used the SaveAs method for the divisional data, you would have had to use the SaveAs method again. There are many other times when you want to save a workbook as a different name but want to continue working in the current workbook, and the SaveCopyAs method is the easiest way to do that.

As you can see in the Open method example, it is very simple to open an existing workbook. However, there are also many other options available, such as whether there is a password, whether you want to update links, etc. The Open method is useful when you are opening a workbook that will be used as a template. You also use the Open method when you have data in Excel that you want to bring into Access in an automated fashion.

An example of this is an Excel workbook that holds various types of information within each row. Let's say that you have an Excel workbook that holds the amount of time worked by each participant on a project, and column A specifies whether the person is an employee or a contractor. In an Access database, if you keep the time of employees in a separate table than that of contractors, you need to sort or filter in Excel to be able to import or cut and paste the data. This might be manageable, but if you get several of these each week, it quickly takes up more time than it is worth.

From Access, you could open up every workbook in a directory using VBA, and in an automated fashion, process them line by line, using the Select...Case statement to process each row. If the row held contractor data, you would have code to put it into the contractor table, and if the row were an employee record, you could put that data into the employee table.

If you are working with data that needs to go into multiple tables in exactly the same format, you can easily handle this with one line of code for each table, instead of recreating all of the code. Have one table for items ordered and another for items on backorder, with the same fields for each.

Another example is if you have separate tables for home addresses and business address. Using the example from the text, if you have an employee table and a contractor table, and the data being entered is the same for each, write a Select...Case statement and have all of the code in each Case. This results in duplication of code.

As an alternative, have the Select...Case statement set the reference to the correct table, and have the code run after the Select...Case statement. This reduces the amount of code that you have and also make it easier to maintain, since changes need to be made in only one place, instead of for each Case. This won't work if the data is different in each table, but when the data is the same, it makes coding much easier.

5.1.3. The Worksheet Object

The place where most of the code happens is with the Worksheet object. As with the application object, the Worksheet object has many objects, methods, and properties available. There is a Worksheets collection that holds all of the worksheets in a workbook and provides methods to add, copy, delete, move, etc. the worksheets in the workbook. When you add a new workbook to the Excel application object, it adds the number of worksheets specified on the General tab of the Excel options dialog. You can get to that dialog from within Excel by going to Tools Options, as shown in Figure 5-3.

Figure 5-3. Excel dialog box allowing you to set various options, including the "Sheets in new workbook" option referred to in the text

This is important to know because you cannot assume that your user has the same number of worksheets as you do on your machine. I ran into a problem once in which I prepared three worksheets of data, and my machine had a default of three worksheets for a new workbook. I referred to each as Sheet1, Sheet2, and Sheet3. As it ran, it renamed each. This worked for a while until one day I was called about an error. I could not easily diagnose it over the phone so I went to the person's PC and found the problem was that their default was set to one worksheet for a new workbook. While I could have changed this setting for them, instead I changed the code to add worksheets instead of referring to the default blank worksheets, allowing the code to work on all machines.

The code below shows how to set a reference to a worksheet added to a new workbook. To use an existing sheet, you would use xlWb.Sheets("SheetName") instead of xlWb.Worksheets.Add. In addition to the Sheets collection, you can also use the Worksheets collection. As with other objects, the Worksheet object belongs to multiple collections. The Sheets collection holds all of the worksheets and chart sheets. The Worksheets collection holds only the worksheets. Each worksheet is a member of both collections. In Example 5-5, a new worksheet is added and renamed to MyNewWorksheet.

Example 5-5. Code to add a worksheet

 Public Sub GetExcel( ) Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Dim xlWs As Excel.Worksheet Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.Worksheets.Add xlWs.Name = "MyNewWorksheet" End Sub 

Now that you have been exposed to the primary objects necessary to begin an automation project, the other objects will be discussed. By the time you finish with this chapter, you will be able to dump data from Access into Excel, format and perform calculations on the data, and print the result.