|
|
The first two samples in this section demonstrate 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 second procedure in the Excel Automation server, in turn, updates the spreadsheet file, but at the time and in the manner that an Access application determines. The section closes with a fourth sample that enables navigation through Excel data with an Access form.
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 the first value in the MPG (miles per gallon) column. The data resides in a range named gas. The Define Name dialog box shows the extent of the range in Sheet1. The sample application utilizes this named range. When you apply this model application to your data, you will need a corresponding range in your adaptation of the application.
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 Access. 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 Dim computedTotal As Currency 'Make connection to Excel source cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Access2003Files\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("days since last refill"), _ rst1.Fields("Total"), computedTotal rst1.MoveNext Loop 'Clean up objects rst1.Close Set rst1 = Nothing cnn1.Close Set cnn1 = Nothing End Sub
The sub procedure declares and creates a new Connection object, and then it opens the Connection object. This is critical when you work with most ISAM drivers 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. (Figure 9-2 displays an excerpt of the data from the workbook file.) You must conclude the connection string with an Extended Properties specification that points at the ISAM driver that your application uses. The sample uses the syntax for the Extended Properties specification (Excel 8.0) that works with Excel 2003 through Excel 97 workbook files.
The Recordset sample that follows illustrates another critical and relatively standard feature of ISAM applications. By defining a recordset on the connection, your application can gain the ability to read from and write to the remote data source. The ability to update data depends on the functionality available through the ISAM driver. If your Access application links to the Excel data source, you can 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.
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 read and manipulate data 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 through 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.
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 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 Dim strSQL As String 'Set Connection, 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:\Access2003Files\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", cnn1, , , adCmdTable 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: Set pk1 = Nothing Set tbl1 = Nothing Set cat1 = Nothing rst1.Close rst2.Close Set rst1 = Nothing Set rst2 = Nothing cnn1.Close cnn2.Close Set cnn1 = Nothing Set cnn2 = Nothing Exit Sub createTableTrap: If Err.Number = -2147217857 Then 'If the gas table already exists, delete it cat1.Tables.Delete "gas" Resume Else 'Else print the Err object Number 'and Description properties Debug.Print Err.Number; Err.Description End If End Sub
The previous procedure 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. Because 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). The procedure 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. In the event of such an error, 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 a keyset cursor type for the link to the local table so that it can add records. Because Access can exactly duplicate the computations of Excel, there is no need to copy computed fields. In addition, not copying computed fields keeps your table's field values independent of one another so that your table is normalized.
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.
The security level for running Excel macros can be set with the steps described in a Note in the "Working with Values from Excel Worksheets" section of this chapter. The Security dialog box for macros in Excel permits you to choose High, Medium, or Low, depending on your needs and the policies of your organization. If the Medium security level is selected, you will see a prompt when you attempt to run a macro in a workbook file. Choose Enable Macros to run the ComputeOnGas procedure. If you select the Low security level (as I did for the MyGas.xls file), the macros behind the worksheet will run without a prompt. Do not run the procedure with the High security level selected because it will disable the code in the unsigned module within the workbook file.
After the ComputeOnGas procedure from the Excel file returns control to Access, the runXL procedure invokes the Save method for the ActiveWorkbook object in Excel. This commits the changes to storage and avoids a prompt asking whether to do so 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.
| Note |
For your convenience when rerunning this sample, the .mdb file for this chapter includes a procedure called RunRestoreXLSheet. This procedure restores the MyGas.xls workbook to the state it was in prior to invoking RunXL. |
Sub RunXL() Dim myXLWrkBk As Excel.Workbook 'Open connection to XL workbook and make it visible Set myXLWrkBk = GetObject("C:\Access2003Files\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 the worksheet 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 can contain their widest entry.
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're 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)
With mySheet
lastRow = Range("gas").Rows.Count
'Assign column heading
.Cells(1, 9) = "Miles per Day"
'Compute miles per day
For iRow = 3 To lastRow
.Cells(iRow, 9) = _
Format(Range("gas").Cells(iRow, 2) / _
Range("gas").Cells(iRow, 7), _
"0.##")
sumDays = sumDays + .Cells(iRow, 7)
Next iRow
'Compute summary statistics
.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 and format cell like column D
.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(.Cells(lastRow + 2, 2) / _
.Cells(lastRow + 2, 3), "0.###")
ActiveCell.Font.Bold = True
'Compute cents per mile
ActiveCell.Offset(0, 1).Activate
ActiveCell = Format(.Cells(lastRow + 2, 8) / _
.Cells(lastRow + 2, 2), "0.###")
'Compute miles per day
ActiveCell.Offset(0, 3).Activate
ActiveCell = Format(.Cells(lastRow + 2, 2) / sumDays, "0.###")
End With
'Resize columns to show values
Worksheets("Sheet1").Columns("a:I").AutoFit
End Sub
The ability to show Excel data in an Access form is a common request. This functionality enables spreadsheet analysts to work with data in Excel even while your application shows the same data within an Access form. Figure 9-5 shows an excerpt from Sheet1 in the Customers.xls file open above the frmCustomers form from the Chapter09.mdb file. The .xls and .mdb files are open concurrently. The workbook file shows the first customer selected. The Access form displays the same data from a concurrent Access session. The buttons below the text boxes enable an Access user to navigate backward and forward through the Excel data.
| Note |
Figure 9-5 (on the next page) shows Excel opening Customers.xls, and then Access opening the frmCustomers form. Attempting to concurrently display data in Excel and Access applications by other methods can result in faulty displays. |
The frmCustomers form is an unbound form that depends on an ISAM link to the Customers.xls file. The code behind the form enables three tasks. First, it populates the recordset and assigns the first row in the recordset to the text boxes on the form. Second, it permits clicks to the button with a right arrowhead to move forward through the data. Third, it permits clicks to the button with a left arrowhead to move backward through the data. The form has built-in intelligence to disable either arrowhead button when the action the button permits is not legitimate. The following listing shows the three event procedures that respond to the button clicks.
The Form_Open event procedure starts by creating a Connection object (cnn1) to the Excel workbook file (Customers.xls) and creating a Recordset object (rst1) based, in part, on cnn1. The first row of column values from rst1 is copied to the TextBox controls on the form. Although rst1 has a keyset cursor, the form does not enable an updated value in a text box to flow through to Sheet1 in Customers.xls. This capability depends on the state of the workbook file and additional code that you can add behind the form. Essentially, the code must implement any kind of database maintenance tasks that you decide to enable from the form. See the sample in the "Running Excel Procedures from an Access Procedure" section of this chapter for one approach to modifying a spreadsheet from Access. The Form_Open event procedure closes with code that manages the appearance of the form. Namely, the next to the last section disables the button for moving backward beyond the first record, and the closing section removes three selected design features, such as the built-in navigation buttons.
| Note |
The application declares the rst1 variable with a Public keyword so that any of the three procedures in the application can refer to it. |
The button for moving forward through rst1 has the name cmdNext. Therefore, the cmdNext_Click procedure begins by moving to the next record and re-populating the Value property of the TextBox controls with the current row in rst1. Before closing, the cmdNext_Click procedure manages the Enabled value of the navigation buttons and sets the focus if necessary. For example, if the values displayed are for the last row in rst1, then the procedure disables the cmdNext button and sets the focus to the cmdPrevious button for moving backward through rst1.
The cmdPrevious_Click procedure has the same general design as the cmdNext_Click procedure. For the cmdPrevious_Click procedure, the application moves backward through rst1. In addition, it shifts the focus to cmdNext if appropriate.
Public rst1 as ADODB.Recordset
Private Sub Form_Open(Cancel As Integer)
Dim cnn1 As New ADODB.Connection
Dim fld1 As ADODB.Field
Dim str1 As String
Dim frm1 As Access.Form
'Create connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Access2003Files\Customers.xls;" & _
"Extended Properties=Excel 8.0;"
'Create recordset
'requires named range of customers in .xls file and
'adCmdTable argument to reference object instead of
'SQL string
Set rst1 = New ADODB.Recordset
With rst1
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "customers", cnn1, , , adCmdTable
End With
'Assign selected columns from first row of
'recordset to text box controls on form
With Me
.Text0.Value = rst1("CustomerID")
.Text2.Value = rst1("CompanyName")
.Text4.Value = rst1("ContactFirstName")
.Text6.Value = rst1("ContactLastName")
.Text8.Value = rst1("PaymentTerms")
End With
'Disable cmdPrevious button initially
Me.cmdPrevious.Enabled = False
'Format form on open to leave out
'built-in navigation buttons, record
'selector, and dividing lines
Me.NavigationButtons = False
Me.RecordSelectors = False
Me.DividingLines = False
End Sub
Private Sub cmdNext_Click()
'Move to next row and populate form controls
rst1.MoveNext
With Me
.Text0.Value = rst1("CustomerID")
.Text2.Value = rst1("CompanyName")
.Text4.Value = rst1("ContactFirstName")
.Text6.Value = rst1("ContactLastName")
.Text8.Value = rst1("PaymentTerms")
End With
'Disable cmdNext button if at last record
If rst1.AbsolutePosition = rst1.RecordCount Then
Me.cmdPrevious.SetFocus
Me.cmdNext.Enabled = False
End If
'Enable cmdPrevious button if it is disabled
If Me.cmdPrevious.Enabled = False Then _
Me.cmdPrevious.Enabled = True
End Sub
Private Sub cmdPrevious_Click()
'Move to previous row and populate form controls
rst1.MovePrevious
With Me
.Text0.Value = rst1("CustomerID")
.Text2.Value = rst1("CompanyName")
.Text4.Value = rst1("ContactFirstName")
.Text6.Value = rst1("ContactLastName")
.Text8.Value = rst1("PaymentTerms")
End With
'Disable cmdPrevious button if at first record
If rst1.AbsolutePosition = 1 Then
Me.cmdNext.SetFocus
Me.cmdPrevious.Enabled = False
End If
'Enable cmdNext button if it is disabled
If Me.cmdNext.Enabled = False Then _
Me.cmdNext.Enabled = True
End Sub
|
|