Working with Excel from Access

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.

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 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.

Note  

The macro security level for the MyGas.xls file is set at Low. This setting is convenient for any .xls file in which you want to run macros via Automation from another application. From the Excel menu, choose Tools, Macros, Security to open the Security dialog box for macros. Select the Security level tab to expose the security settings. Then, choose High, Medium, or Low.

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 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.

 SubOpenXLComputePrint() Dimcnn1AsNewADODB.Connection Dimrst1AsADODB.Recordset DimcomputedMPGAsDouble DimcomputedTotalAsCurrency     'MakeconnectiontoExcelsource cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access2003Files\Mygas.xls;" &_  "ExtendedProperties=Excel8.0;"     'Openread-onlyrecordsetbasedonExcelsource. 'Recalldefaultisread-only. Setrst1=NewADODB.Recordset rst1.Open "gas",cnn1,,,adCmdTable     'Enumeraterecordsandcomputewithfieldvalues DoUntilrst1.EOF computedMPG=rst1.Fields("Miles")/_ rst1.Fields("Gallons") computedTotal=rst1.Fields("Gallons")*_ rst1.Fields("PriceperGallon") Debug.Printrst1.Fields("Date"),_ rst1.Fields("Miles"),_ rst1.Fields("Gallons"),_ rst1.Fields("PriceperGallon"),_ rst1.Fields("MPG"),computedMPG,_ rst1.Fields("dayssincelastrefill"),_ rst1.Fields("Total"),computedTotal rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing     EndSub 

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.

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 Data Access Objects (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 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.

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 Identity data type to specify the start and step values for the table's index field ( MyID ).

 SubCreateTableFromXL() OnErrorGoTocreateTableTrap Dimcnn1AsADODB.Connection Dimcnn2AsNewADODB.Connection Dimrst1AsADODB.Recordset Dimrst2AsADODB.Recordset Dimcat1AsADOX.Catalog Dimtbl1AsADOX.Table Dimpk1AsADOX.Index DimstrSQLAsString     'SetConnection,Catalog,andTableobjects Setcnn1=CurrentProject.Connection Setcat1=NewADOX.Catalog cat1.ActiveConnection=cnn1 Settbl1=NewADOX.Table     'Definetablenamed "gas" andappendit 'totheTablescollection Withtbl1 .Name= "gas" .Columns.Append "Date",adDate .Columns.Append "Miles",adDouble .Columns.Append "Gallons",adDouble .Columns.Append "PricePerGallon",adCurrency EndWith cat1.Tables.Appendtbl1     strSQL= "ALTERTABLEGasADDCOLUMNMyIDIdentity(2,2)" cnn1.ExecutestrSQL     Setpk1=NewADOX.Index Withpk1 .Name= "MyPrimaryKey" .PrimaryKey=True .Unique=True .IndexNulls=adIndexNullsDisallow EndWith pk1.Columns.Append "MyID" tbl1.Indexes.Appendpk1     'MakeconnectiontoExcelsource cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access2003Files\MyGas.xls;" &_  "ExtendedProperties=Excel8.0;" 'Openread-onlyrecordsetbasedonExcelsource. 'Recalldefaultisread-only. Setrst1=NewADODB.Recordset rst1.Open "gas",cnn2,,,adCmdTable 'Openread-writerecordsetbasedonlocaltable 'named "gas" Setrst2=NewADODB.Recordset rst2.ActiveConnection=cnn1 rst2.CursorType=adOpenKeyset rst2.LockType=adLockOptimistic rst2.Open "gas",cnn1,,,adCmdTable     DoUntilrst1.EOF Withrst2 .AddNew .Fields("Date")=rst1.Fields("Date") .Fields("Miles")=rst1.Fields("Miles") .Fields("Gallons")=rst1.Fields("Gallons") .Fields("PricePerGallon")=_ rst1.Fields("PricePerGallon") .Update EndWith rst1.MoveNext Loop     createTableExit: Setpk1=Nothing Settbl1=Nothing Setcat1=Nothing rst1.Close rst2.Close Setrst1=Nothing Setrst2=Nothing cnn1.Close cnn2.Close Setcnn1=Nothing Setcnn2=Nothing ExitSub     createTableTrap: IfErr.Number=-2147217857Then 'Ifthegastablealreadyexists,deleteit cat1.Tables.Delete "gas" Resume Else 'ElseprinttheErrobjectNumber 'andDescriptionproperties Debug.PrintErr.Number;Err.Description EndIf     EndSub 

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.

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.

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 .

 SubRunXL() DimmyXLWrkBkAsExcel.Workbook     'OpenconnectiontoXLworkbookandmakeitvisible SetmyXLWrkBk=GetObject("C:\Access2003Files\MyGas.xls") myXLWrkBk.Application.Visible=True myXLWrkBk.Application.Windows("MyGas.xls").Visible=True     'RunprocedureinThisWorkBookfolder myXLWrkBk.Application.Run "ThisWorkBook.computeOnGas"     'CloseAutomationobject. 'EitherinvoketheSavemethodorsettheSaved 'propertytoTruetoavoidapromptaboutsavingchanges. myXLWrkBk.Application.ActiveWorkbook.Save 'myXLWrkBk.Application.ActiveWorkbook.Saved=True myXLWrkBk.Application.Quit SetmyXLWrkBk=Nothing EndSub 

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.

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'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.

 SubComputeOnGas() DimmySheetAsWorksheet DimiRowAsInteger,lastRowAsInteger DimsumDaysAsLong     'Setreferencetofirstworksheet SetmySheet=Worksheets(1) WithmySheet lastRow=Range("gas").Rows.Count     'Assigncolumnheading .Cells(1,9)= "MilesperDay"     'Computemilesperday ForiRow=3TolastRow .Cells(iRow,9)=_ Format(Range("gas").Cells(iRow,2)/_ Range("gas").Cells(iRow,7),_  "0.##") sumDays=sumDays+.Cells(iRow,7) NextiRow     'Computesummarystatistics .Cells(Range("gas").Rows.Count+2,1).Select ActiveCell.Formula= "Summary" 'Computetotalmiles ActiveCell.Offset(0,1).Activate ActiveCell.Formula= "=Sum(b2:b" &lastRow& ")" & "" 'Computetotalgallons ActiveCell.Offset(0,1).Activate ActiveCell.Formula= "=Sum(c2:c" &lastRow& ")" & "" 'Computetotalgasdollars ActiveCell.Offset(0,5).Activate ActiveCell.Formula= "=Sum(h2:h" &lastRow& ")" & "" 'Computedayssincelastrefill ActiveCell.Offset(0,-1).Activate ActiveCell.Formula= "=Sum(g3:g" &lastRow& ")" & "" 'ComputepricepergallonandformatcelllikecolumnD .Cells(Range("gas").Rows.Count+2,4).Select ActiveCell.Formula= "=H" &(lastRow+2)& "/C" &(lastRow+2) 'Computemilespergallon ActiveCell.Offset(0,1).Activate ActiveCell=Format(.Cells(lastRow+2,2)/_ .Cells(lastRow+2,3), "0.###") ActiveCell.Font.Bold=True 'Computecentspermile ActiveCell.Offset(0,1).Activate ActiveCell=Format(.Cells(lastRow+2,8)/_ .Cells(lastRow+2,2), "0.###") 'Computemilesperday ActiveCell.Offset(0,3).Activate ActiveCell=Format(.Cells(lastRow+2,2)/sumDays, "0.###") EndWith 'Resizecolumnstoshowvalues Worksheets("Sheet1").Columns("a:I").AutoFit     EndSub 

Navigating Imported Excel Data with an Access Form

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.

Figure 9.5: A display of Excel data in concurrent Excel and Access applications.

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.

 Publicrst1asADODB.Recordset PrivateSubForm_Open(CancelAsInteger)     Dimcnn1AsNewADODB.Connection Dimfld1AsADODB.Field Dimstr1AsString Dimfrm1AsAccess.Form 'Createconnection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_  "DataSource=C:\Access2003Files\Customers.xls;" &_  "ExtendedProperties=Excel8.0;"     'Createrecordset 'requiresnamedrangeofcustomersin.xlsfileand 'adCmdTableargumenttoreferenceobjectinsteadof 'SQLstring Setrst1=NewADODB.Recordset Withrst1 .CursorType=adOpenKeyset .LockType=adLockOptimistic .Open "customers",cnn1,,,adCmdTable EndWith     'Assignselectedcolumnsfromfirstrowof 'recordsettotextboxcontrolsonform WithMe .Text0.Value=rst1("CustomerID") .Text2.Value=rst1("CompanyName") .Text4.Value=rst1("ContactFirstName") .Text6.Value=rst1("ContactLastName") .Text8.Value=rst1("PaymentTerms") EndWith     'DisablecmdPreviousbuttoninitially Me.cmdPrevious.Enabled=False     'Formatformonopentoleaveout 'built-innavigationbuttons,record 'selector,anddividinglines Me.NavigationButtons=False Me.RecordSelectors=False Me.DividingLines=False     EndSub     PrivateSubcmdNext_Click()      'Movetonextrowandpopulateformcontrols rst1.MoveNext WithMe .Text0.Value=rst1("CustomerID") .Text2.Value=rst1("CompanyName") .Text4.Value=rst1("ContactFirstName") .Text6.Value=rst1("ContactLastName") .Text8.Value=rst1("PaymentTerms") EndWith     'DisablecmdNextbuttonifatlastrecord Ifrst1.AbsolutePosition=rst1.RecordCountThen Me.cmdPrevious.SetFocus Me.cmdNext.Enabled=False EndIf     'EnablecmdPreviousbuttonifitisdisabled IfMe.cmdPrevious.Enabled=FalseThen_ Me.cmdPrevious.Enabled=True     EndSub     PrivateSubcmdPrevious_Click()      'Movetopreviousrowandpopulateformcontrols rst1.MovePrevious WithMe .Text0.Value=rst1("CustomerID") .Text2.Value=rst1("CompanyName") .Text4.Value=rst1("ContactFirstName") .Text6.Value=rst1("ContactLastName") .Text8.Value=rst1("PaymentTerms") EndWith     'DisablecmdPreviousbuttonifatfirstrecord Ifrst1.AbsolutePosition=1Then Me.cmdNext.SetFocus Me.cmdPrevious.Enabled=False EndIf     'EnablecmdNextbuttonifitisdisabled IfMe.cmdNext.Enabled=FalseThen_ Me.cmdNext.Enabled=True     EndSub