Talking to Excel from Access

As you can see, the syntax for automation coding is not difficult to learn. You just set the appropriate reference and then use objects from the automation server just like native Access objects. The real learning curve in automation comes in discovering what you can do with other applications. If you've installed a reasonable amount of software on your computer, you probably have dozens or even hundreds of automation servers available.

We can't possibly give you a comprehensive look at what all those applications can do via automation. Instead, we just show you a couple of examples, one using Microsoft Excel and the other using Microsoft Word.

TIP

The Microsoft Office applications make all their functions available to other programs via automation, so they're a good place to start.

For the first example, we show how to get the results of the WeeklyTimeslips query to Excel. This is a typical use of automation: moving data from one application to another so that it can be further analyzed. Excel makes it easy to get a total for a set of records while still viewing the detail, for example, which is difficult in Access.

To export the data to Excel, you can open the Chapter21 sample module and run the QueryToExcel procedure directly from the Immediate window. Here's the code:

 Sub QueryToExcel()   ' Send the Weekly Timeslips results to Excel   ' for further analysis   Dim rstWeeklyTimeslips As ADODB.Recordset   Dim objXL As Excel.Application   Dim objWS As Excel.Worksheet   Dim fld As ADODB.Field   Dim intCol As Integer   Dim intRow As Integer   Set rstWeeklyTimeslips = New ADODB.Recordset   ' Get the desired data into a recordset   rstWeeklyTimeslips.Open _    "WeeklyTimeslips", CurrentProject.Connection   ' Launch Excel   Set objXL = New Excel.Application   ' Create a new worksheet   objXL.Workbooks.Add   Set objWS = objXL.ActiveSheet   ' Copy the data   ' First the field names   For intCol = 0 To rstWeeklyTimeslips.Fields.Count - 1     Set fld = rstWeeklyTimeslips.Fields(intCol)     objWS.Cells(1, intCol + 1) = fld.Name   Next intCol   ' Now the actual data   intRow = 2   Do Until rstWeeklyTimeslips.EOF     For intCol = 0 To rstWeeklyTimeslips.Fields.Count - 1       objWS.Cells(intRow, intCol + 1) = _        rstWeeklyTimeslips.Fields(intCol).Value     Next intCol     rstWeeklyTimeslips.MoveNext     intRow = intRow + 1   Loop   ' Make the worksheet visible   objXL.Visible = True   ' Don't call the Excel Quit method, so the   ' worksheet will hang around after this   ' procedure exits End Sub 

This procedure might look a bit complex, but the complexity is there only because it's manipulating both ADO and Excel objects simultaneously. After declaring variables, it begins to do its work by opening a Recordset on the WeeklyTimeslips query. We haven't bothered to specify a lock type or cursor type for the Recordset, so it opens as a forward-only, read-only Recordset. This is the fastest type and perfectly appropriate here, because all you want to do is read through the data one time.

The next step is to launch Excel and create a new worksheet where the data can go. When you launch Excel from the Start menu it automatically creates a default workbook with three worksheets for you, but that doesn't happen when you launch it by automation. Instead, you need to call the Add method of the Excel Workbooks collection to do so. After that, the ActiveSheet property of the Application object provides a handy reference to the default worksheet.

Copying the data involves two parts. First, the code loops through the Fields collection of the Recordset to set up column heads in Excel. After that, it moves through the Recordset itself to move the values in the data over. The Cells object, a child of the Excel Worksheet object, enables you to refer to individual cells on a worksheet by using the row and column numbers as indexes. Note that there's some adjustment necessary because the columns of a Recordset are numbered starting at zero, whereas those of a worksheet are numbered starting at one.

After creating the worksheet and moving the data over, the procedure makes it visible. It's faster to do this than to make it visible before writing, so the screen doesn't have to be updated constantly. The final step is simply to exit. Although this causes the objXL variable to go out of scope, it doesn't dispose of the automation server that it pointed to. Excel remains running, and displays the data as shown in Figure 21.3.

Figure 21.3. Access data exported to Excel via automation.