Working with Excel from Access

The first two samples below demonstrate some capabilities that the Excel installable ISAM can add to an application. The third sample shows a simple but powerful way to use automation. Instead of directly manipulating detailed elements of the object model of an automation server, the procedure launches a procedure within the automation server. The procedure, in turn, updates the spreadsheet file, but at the time and in the manner that an Access application determines.

Working with Values from Excel Worksheets

All three samples work with the Excel workbook depicted in Figure 9-2. The file is MyGas.xls. The first four columns of Sheet1 contain manually entered data, and the next four columns contain expressions based on the first four. The formula bar shows the expression for entries in the MPG column. The data resides in a range named gas. The Define Name dialog box shows the extent of the range in Sheet1.

Figure 9-2. An Excel spreadsheet with a named range, gas, extending over cells A1 through H45.

The first sample reads the entries from Excel, performs some calculations in Access, and prints the results to the Immediate window. After establishing a connection to the Excel data source, your application can programmatically treat the data source just like an internal Access table. For example, you can enumerate records in the table or compute values based on the entries in the record source. The sample prints to the Immediate window the existing entries in the Excel worksheet alongside the results of expressions computed in Excel. This, incidentally, confirms that Access computations can generate results identical to those in Excel. This capability can reduce the amount of data that your application must read from a computationally intensive spreadsheet.

 Sub openXLComputePrint() Dim cnn1 As New ADODB.Connection Dim rst1 As ADODB.Recordset Dim computedMPG As Double, computedTotal As Currency 'Make connection to Excel source.     cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\Programming Access\Chap09\mygas.xls;" & _         "Extended Properties=Excel 8.0;"         'Open read-only recordset based on Excel source. 'Recall default is read-only.     Set rst1 = New ADODB.Recordset     rst1.Open "gas", cnn1, , , adCmdTable      'Enumerate records and compute with field values.     Do Until rst1.EOF         computedMPG = rst1.Fields("Miles") / _             rst1.Fields("Gallons")         computedTotal = rst1.Fields("Gallons") * _             rst1.Fields("Price per Gallon")         Debug.Print rst1.Fields("Date"), _             rst1.Fields("Miles"), _             rst1.Fields("Gallons"), _             rst1.Fields("Price per Gallon"), _             rst1.Fields("MPG"), computedMPG, _             rst1.Fields("Total"), computedTotal         rst1.MoveNext     Loop 'Close connection to source.     cnn1.Close End Sub 

The subprocedure declares and creates a new Connection object, and then it opens the Connection object. This is critical when you work with an ISAM driver because this is how you manage your link to the data source outside of Access. The connection string points the object at the Excel file MyGas.xls. (Its data is excerpted in Figure 9-2.) You must conclude the connection string with an Extended Properties specification that points at the ISAM driver that your application uses. The sample uses one ( Excel 8.0) that works with Excel 2000 and Excel 97 workbook files.

The Recordset reference that follows is another critical and relatively standard component of ISAM applications. By defining a recordset on the connection, your application gains the ability to read from and write to the remote data source. If your Access Application links to the Excel data source, you use the Recordset object's AddNew and Update methods to add new rows to a worksheet from Access. The recordset specification must also designate which portion of the workbook to link. If you reference the gas range, the recordset can use the built-in range for the worksheet. The syntax for referencing an external Excel table is identical to that for referencing an internal Access table.

NOTE

---

If your application must write to or revise an Excel data source from Access, be sure to define a cursor that supports this functionality (for example, pass the adOpenKeyset constant for the cursor type and the adLockOptimistic constant for the lock type). Unlike DAO, the default ADO cursor does not support updating.

The next major element of the procedure is a Do loop that enumerates all the records in the gas range. The first two lines in the loop evaluate expressions for two computed values. The computedMPG and computedTotal variables compare Access to Excel arithmetic as they confirm your ability to manipulate data read from an Excel data source. The next seven lines within the Do loop print to the Immediate window Excel table field values along with the two computed variables for each row. You navigate through an Excel table exactly as you do an internal table. Of course, you must invoke a MoveNext method within the loop to progress through the spreadsheet rows.

Figure 9-3 shows the five rightmost columns of the output from the preceding sample. The first two columns show identical results for MPG from Excel (the first column) and Access (the second column). The same is true for the total gas bill for each gas fill-up. This confirms that the double and currency data types from Access can duplicate results from Excel.

Figure 9-3. An excerpt from the output of the openXLComputePrint procedure. Notice the identical computational results from Access and Excel.

Dynamically Creating Access Tables Based on Excel Worksheets

The preceding sample exposes the values in a spreadsheet through a recordset. If your application must regularly work with the data in a spreadsheet, you can improve performance by copying the spreadsheet values to a local table within Access. In addition, an application can reduce its demand for connection resources by copying spreadsheet values to local tables when it requires simultaneous access to several different spreadsheet ranges. The following sample programmatically creates a table that has an index for a spreadsheet range and then populates the table with values from the range. Incidentally, the sample uses the new Identity data type to specify the start and step values for the table's index field ( MyID).

 Sub createTableFromXL() On Error GoTo createTableTrap Dim cnn1 As ADODB.Connection Dim cnn2 As New ADODB.Connection Dim rst1 As ADODB.Recordset Dim rst2 As ADODB.Recordset Dim cat1 As ADOX.Catalog Dim tbl1 As ADOX.Table Dim pk1 As ADOX.Index 'Set catalog and table objects.     set cnn1 = CurrentProject.Connection     Set cat1 = New ADOX.Catalog     cat1.ActiveConnection = cnn1     Set tbl1 = New ADOX.Table 'Define table named gas and append it 'to the Tables collection.     With tbl1         .Name = "gas"         .Columns.Append "Date", adDate         .Columns.Append "Miles", adDouble         .Columns.Append "Gallons", adDouble         .Columns.Append "PricePerGallon", adCurrency     End With     cat1.Tables.Append tbl1     strSQL = "ALTER TABLE Gas ADD COLUMN MyID Identity(2,2)"     cnn1.Execute strSQL          Set pk1 = New ADOX.Index     With pk1         .Name = "MyPrimaryKey"         .PrimaryKey = True         .Unique = True         .IndexNulls = adIndexNullsDisallow     End With     pk1.Columns.Append "MyID"     tbl1.Indexes.Append pk1      'Make connection to Excel source.     cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _        "Data Source=C:\Programming Access\Chap09\mygas.xls;" & _        "Extended Properties=Excel 8.0;"         'Open read-only recordset based on Excel source. 'Recall default is read-only.     Set rst1 = New ADODB.Recordset     rst1.Open "gas", cnn2, , , adCmdTable                           'Open read-write recordset based on local table 'named gas.     Set rst2 = New ADODB.Recordset     rst2.ActiveConnection = cnn1     rst2.CursorType = adOpenKeyset     rst2.LockType = adLockOptimistic     rst2.Open "gas"          Do Until rst1.EOF         With rst2             .AddNew             .Fields("Date") = rst1.Fields("Date")             .Fields("Miles") = rst1.Fields("Miles")             .Fields("Gallons") = rst1.Fields("Gallons")             .Fields("PricePerGallon") = _                 rst1.Fields("Price Per Gallon")             .Update         End With         rst1.MoveNext     Loop      createTableExit:     Exit Sub createTableTrap:     If Err.Number = -2147217857 Then         cat1.Tables.Delete "gas"         Resume     Else         Debug.Print Err.Number; Err.Description         Resume createTableExit     End If End Sub 

The procedure above is lengthy because it performs several discrete but related functions. To create a local table with Excel spreadsheet values, the sample needs a pair of Connection and Recordset objects. These objects provide simultaneous connectivity to the spreadsheet and the local table so that the procedure can copy a row from one data source to the other. To define a local table programmatically within Access, the code declares Catalog, Table, and Index objects.

Before copying the data from Excel, the procedure prepares a local table to accept them. It starts by assigning the connection for the current project to the cnn1 reference. Since cnn1 refers to the native project connection, there is no need to include the New keyword in its declaration. On the other hand, the procedure does create new instances of the Catalog and Table objects (and their declarations reflect this by the inclusion of New). It then uses ADO code to define and append fields for holding spreadsheet values. However, it reverts to SQL code for specifying the start and step values for the index. This capability depends completely on built-in Jet engine functionality. Therefore, the SQL code is specific to the Jet database engine. After completing the definition of the index and appending it to the table, the procedure opens a connection to the spreadsheet. (This sample uses the same spreadsheet as the preceding one.)

Any attempt to redefine an existing table generates error number -2147217857. The procedure deletes the old table and resumes adding the new table. In a full-scale application, you might want to archive the old table.

The procedure prepares for copying values by creating two Recordset objects—one for the spreadsheet and one for the local table. The code uses the default cursor for the spreadsheet because it just reads values sequentially from it, but it uses an adOpenKeyset cursor type for the link to the local table so that it can add records. Since Access can exactly duplicate the computations of Excel, there is no need to copy computed fields. This keeps your table's field values independent of one another so that your table is normalized.

Running Excel Procedures from an Access Procedure

In the following procedure, runXL, Access uses the GetObject function to create an instance of the Excel Application object that contains the MyGas workbook shown in Figure 9-2. It sets the Visible property of the Application and Window objects to True. Then it invokes the Application object's Run method for the computeOnGas procedure in the ThisWorkbook folder of the MyGas.xls file. After the computeOnGas procedure from the Excel file returns control to Access, the runXL procedure invokes the Save method for the ActiveWorkbook in Excel. This commits the changes to storage and avoids a prompt asking whether to do that when the next line invokes the Quit method. If you want to close Excel without saving the changes and without a prompt that asks whether to save them, you set the workbook's Saved property to True before invoking the Quit method. (See the commented line for the correct syntax.) You retrieve the automation resources by setting the automation object reference to Nothing.

 Sub runXL() Dim myXLWrkBk As Excel.Workbook 'Open connection to XL workbook and make visible.     Set myXLWrkBk = GetObject("c:\Programming Access\Chap09\MyGas.xls")     myXLWrkBk.Application.Visible = True     myXLWrkBk.Application.Windows("MyGas.xls").Visible = True      'Run procedure in ThisWorkBook folder.     myXLWrkBk.Application.Run "ThisWorkBook.computeOnGas" 'Close automation object. 'Either invoke the Save method or set the Saved 'property to True to avoid a prompt about saving changes.     myXLWrkBk.Application.ActiveWorkbook.Save '    myXLWrkBk.Application.ActiveWorkbook.Saved = True     myXLWrkBk.Application.Quit     Set myXLWrkBk = Nothing      End Sub 

Figure 9-4 shows the worksheet after computeOnGas runs. Notice that it computes summary information two rows below the table's last row, and it adds a new column that displays the miles traveled per day between refills. The procedure also resizes the columns so that they fit their widest entry.

Figure 9-4. An excerpt from the output of the computeOnGas procedure. Notice the new column of data and the resized columns.

The computeOnGas procedure involves nothing more than standard VBA, but it uses objects, properties, and methods that are unique to Excel. When you perform automation, you inevitably require some knowledge of at least one other object model—namely the object model for the Office application that you are automating. One advantage of using the Run method, as in the runXL procedure, is that it lets individual developers specialize in particular object models. When a developer wants to use a standard function in an unfamiliar application, he or she can copy a procedure designed by another developer. Even without detailed knowledge of an application, a developer can invoke the Run method for the copied procedure.

 Sub computeOnGas() Dim mySheet As Worksheet Dim iRow As Integer, lastRow As Integer Dim sumDays As Long 'Set reference to first worksheet.     Set mySheet = Worksheets(1)     lastRow = Range("gas").Rows.Count      'Assign column heading.     mySheet.Cells(1, 9) = "Miles per Day"      'Compute miles per day.     For iRow = 3 To lastRow         mySheet.Cells(iRow, 9) = _             Format(Range("gas").Cells(iRow, 2) / _             Range("gas").Cells(iRow, 7), _             "0.##")         sumDays = sumDays + mySheet.Cells(iRow, 7)     Next iRow 'Compute summary statistics.     mySheet.Cells(Range("gas").Rows.Count + 2, 1).Select     ActiveCell.Formula = "Summary" 'Compute total miles.     ActiveCell.Offset(0, 1).Activate     ActiveCell.Formula = "=Sum(b2:b" & lastRow & ")" & "" 'Compute total gallons.     ActiveCell.Offset(0, 1).Activate     ActiveCell.Formula = "=Sum(c2:c" & lastRow & ")" & "" 'Compute total gas dollars.     ActiveCell.Offset(0, 5).Activate     ActiveCell.Formula = "=Sum(h2:h" & lastRow & ")" & "" 'Compute days since last refill.     ActiveCell.Offset(0, -1).Activate     ActiveCell.Formula = "=Sum(g3:g" & lastRow & ")" & "" 'Compute price per gallon.     mySheet.Cells(Range("gas").Rows.Count + 2, 4).Select     ActiveCell.Formula = "=H" & (lastRow + 2) & "/C" & (lastRow + 2) 'Compute miles per gallon.     ActiveCell.Offset(0, 1).Activate     ActiveCell = Format(mySheet.Cells(lastRow + 2, 2) / _         mySheet.Cells(lastRow + 2, 3), "0.###")     ActiveCell.Font.Bold = True 'Compute cents per mile.     ActiveCell.Offset(0, 1).Activate     ActiveCell = Format(mySheet.Cells(lastRow + 2, 8) / _         mySheet.Cells(lastRow + 2, 2), "0.###") 'Compute miles per day.     ActiveCell.Offset(0, 3).Activate     temp = mySheet.Cells(lastRow + 2, 2)     temp2 = sumDays     ActiveCell = Format(mySheet.Cells(lastRow + 2, 2) / sumDays, "0.###") 'Resize columns to show values.    Worksheets("Sheet1").Columns("a:I").AutoFit End Sub