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

Using Export and Analyzing It with Microsoft Office Excel

Now that you have tried to import data, assume you need to go the other way and send data to Excel. This method has far fewer steps than the import method, but before attempting to export data, you need to answer a few questions. First, find out how many rows are being exported. If there are more than 65,536 rows, you need to break the table up into pieces before exporting it. Next, consider the importance of the data format. If the data must be in a particular format, you can export the data, have an Excel report read it, and present it in another sheet. Another option is to use VBA to automate Excel. In this case, neither of these is a problem.

What if you need to put date information into the amortization table and email it to someone as an Excel worksheet? Sure, you could copy and paste from Access into Excel, format it, put in the formulas, etc.—or you could take advantage of the power of a query in the database. Where possible, I attempt to draw comparisons between how you would handle something in Excel versus Access.

In Excel, to add a payment date field that refers to the payment number, write a formula and copy it down to the end of your data. As new rows are added, continue to copy down the formulas. There is nothing stopping you from putting different formulas into every cell or not putting formulas in some cells. This can lead to problems with consistency.

If you contrast that with the Access solution, you see how ...

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