Microsoft Access VBA Programming for the Absolute Beginner

In Chapter 17, you learned that some of the components used by Access, such as its command bars, are actually shared by all Office applications. VBA is another one of those components. VBA is the programming language used by each of the applications included in Office (as is the VBA Editor, the tool for editing VBA code). So with the knowledge of Access programming that you’ve acquired so far in this book, you’re in an excellent position to begin programming with any of the other Office applications.

Rather than starting from scratch, in order to work effectively with a particular Office application, you simply have to familiarize yourself with the basics of that application’s object model. (An object model is a set of classes that, along with their properties, methods, and events, exposes an application’s functionality. A class is a sort of template from which an object is created.) ADO, for instance, is a generalized object model for data access (that is, it’s product independent), while the Application object that you’ve used in the course of this book is part of the Access object model. And each of the other Office applications has its own object model.

The Office object models themselves vary in complexity. Of the major applications in Office XP, the largest object model belongs to Word, with 245 classes and 251 enumerations (an enumeration is a group of related constants), while the smallest is Outlook, with 67 classes and 54 enumerations. Since even the smallest object model is far too extensive to cover in a single chapter, we’ll look at a relatively simple example of how you might retrieve data from your Access database from Excel.

Let’s take a look at Excel and its programming environment by actually running the application. When you launch Excel and open its VBA Editor (Tools | Macros | Visual Basic Editor), you see the familiar interface shown in Figure 21-1. The Code window occupies the right side of the screen, while the Project Explorer is on the upper-left side and the Properties window is on the lower-left side. The Project Explorer contains different objects than the Project Explorer in Access, since Excel’s basic working objects are worksheets and workbooks.

Figure 21-1: The VBA Editor in Excel

If you select Tools | References from the VBA Editor menu, you see the familiar References dialog box, shown in Figure 21-2. Some of the references themselves are also familiar. Excel, like Access, automatically adds references to Visual Basic for Applications, OLE Automation, and the Microsoft Office Object Library. As you might expect, though, Excel automatically adds some different references to its projects than Access does. Whereas Access automatically adds references to the Microsoft Access Object Library, the Microsoft ActiveX Data Objects Library, and the Microsoft DAO Object Library, Excel adds a reference to the Microsoft Excel Object Library. In order to retrieve data from our sample database, thecornerbookstore.mdb, you should add a reference to the Microsoft ActiveX Data Objects Library to your Excel project.

Figure 21-2: The References dialog box in Excel

Since Excel has some limited database capabilities (one of the predominant uses of Excel, in fact, is as a flat-file database), you would expect that it should provide support for data access. And in fact it does. The Excel object model includes a QueryTables collection object that holds QueryTable objects, each of which represents a table built from data returned by an external data source. The QueryTables collection is a child object of the Worksheet object, and is returned by its QueryTables property.

To retrieve data and add a QueryTable object to the collection, you call its Add method, which can take data from a variety of data sources (in our case, an ADO recordset) and inserts it into a specified range in the worksheet. Here is the code to do this:

Public Sub ImportCustomerData() Dim con As ADODB.Connection Dim rs As New ADODB.Recordset Dim ws As Worksheet Dim qt As QueryTable Dim app As New Excel.Application ' Open recordset with customer data rs.CursorType = adOpenForwardOnly Set con = New ADODB.Connection con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\BegVBA\thecornerbookstore.mdb;" rs.Open "SELECT * FROM tblCustomer", con ' Add Customer table to active worksheet Set ws = Application.ActiveSheet Set qt = ws.QueryTables.Add(rs, Range("A2")) qt.RefreshStyle = xlOverwriteCells qt.Refresh True End Sub

To enter this code, select Insert | Module from the VBA Editor menu. Then double-click on the module (which VBA automatically names Module1) in the Project Explorer. This opens a Code window containing the module’s code. You can then proceed to enter the code, and when you are finished, you can run it by selecting the Run button on the VBA Editor toolbar. The result should resemble Figure 21-3.

Figure 21-3: The Customer table in Excel

Much of this code should be familiar—it’s largely the same as the code used to bind a recordset to a form in Chapter 12. The difference is that since you are using Excel, you must bind your recordset to a worksheet and so must use the Excel object model. Most of the code here, however, is a combination of VBA code and ADO code that can run unmodified under both Access and Excel.

There’s a second difference that’s even more significant. Note that although this data can be edited in the Excel environment, changes cannot be saved back to the Access database. This becomes a local copy that is independent of the original Access table from which it was formed; the table in Access will not be updated to reflect any changes made to the data in the Excel spreadsheet.

To make it easier for others (as well as for yourself) to activate your data access routine, you can do one or more of the following:

• Attach it to a toolbar or menu item programmatically, as illustrated in Chapter 17.

• Attach it to a toolbar or menu item through the user interface. To do this, right-click on the menu and select Customize from the pop-up menu, or select Tools | Customize to open the Customize dialog box. On the Commands tab, select Macros from the Categories list box. The Customize dialog box should now resemble Figure 21-4. If you want to create a menu item, drag the Customize Button icon to the position on a menu where you’d like it to appear. If you want to create a toolbar button, drag the Custom Button icon to the toolbar on which you’d like it to appear. (The toolbar must already be visible.) Finally, right-click on the newly added toolbar or menu item to open a pop-up menu, select the Assign Macro option, and select the macro that you’d like to assign to the button or menu item.

  Figure 21-4: Excel’s Customize dialog box

• Call it from an event procedure, such as the Open event that is fired when a worksheet opens. To do this, go to the Visual Basic Editor, double-click on ThisWorksheet in the Project Explorer to open the worksheet’s Code window, select Workbook in the window’s Objects drop-down list, and select Open in the window’s Members drop-down list. Then call the procedure from the event handler, so that the code appears as follows:

  Private Sub Workbook_Open() ImportCustomerData End Sub

  In this case, the procedure will run automatically whenever the workbook is opened.