Integrating Excel and Access

Changing Data in an Existing Report

When changing data in an existing Excel report from Access, you have a few different scenarios to consider. In the first scenario, you fill in a few pieces of data in different places on the worksheet. In the second scenario, you replace data that resembles a recordset. In the final scenario, you do a combination of the two. A combination could be a situating in which you change certain variables based on the number of records in a recordset, or possibly a situation in which you bring in a recordset and take other values from the database. A good example of the combination scenario is a profitability model, where sales are loaded from a recordset and specific pricing is entered as separate data points.

Regardless of the scenario, the first few steps are the same when using VBA from Access. Just to open the existing Excel worksheet, you need an Excel Application object and a Workbook object, and I suggest using a string variable to hold the file name. You also need a reference to Excel using Tools → References from Design View in the Access module. The code in Example 8-1 opens an existing workbook. Example 8-2 is designed as a procedure called from another procedure. I pass the variables ByRef, so this assumes that I have Excel Application and Workbook objects declared in the calling procedure. I would just use the code without calling a different procedure in a production environment, but this is just to demonstrate the concept of opening a workbook. ...