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

Formatting Techniques Example

So far you've seen how to pull in a recordset and how to do some formatting in Excel. The first couple of chapters show many examples of how to pull in recordsets because pulling is the primary method for bringing in data.

This example brings formatting and recordset pulling together. There is a query in the Northwind database that is called [Sales by Category], which is in brackets because there are spaces in the name. That data is not updated daily, but assume that you have a query named that on your system that is updated daily (or monthly, weekly, etc.). You also have a user who wants to see an Excel report that shows that data and produces subtotals by category. I will show you several methods to give the user the desired report, and you can pick the one that suits you best.

Getting to the Data

The first choice you have to make is whether to use ADO or DAO. Since Access will be my only source of data, I will use DAO. The next option is to decide where you want to put the data. I am going to default to the active worksheet starting in cell A4. This will give you room to put a title on the report. Once that is done, determine what you want to do with the data. The program offers two options: using the Subtotal feature in Excel or using a PivotTable. You could also use an input box and have the user type in a number, etc. I will use a message box to ask the user whether she wants a PivotTable. If she clicks Yes, it pulls the data and puts in a pivot ...

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