Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page