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

Chapter 8. Advanced Excel Reporting Techniques

You should now be familiar enough with moving data between Access and Excel that you think of ways to automate reporting and analysis traditionally done in Excel. When considering report automation, I break it into three categories:

  • Changing data in an existing report

  • Creating a report from scratch including all data, formulas, and formatting

  • Using a report template built in a database table

In order to take advantage of these options, you need to become very familiar with the Excel Worksheet and Range objects. You also need to work effectively with multiple worksheets in the same workbook and work step by step through the reporting process. Even for the most complicated reports, you will most likely be able to walk through the steps manually and put in logic checks for each thought process. Then it's a matter of getting the data, writing the formulas, and formatting the worksheet.

Writing Flexible Formulas

In addition to dealing with the Excel objects, understanding how to use R1C1 format to write formulas makes coding much easier. For example, subtracting the cell one row above from the cell two rows above is very straightforward when using R1C1 notation . If you use A1 notation, you have to go through multiple steps to write the string. First, figure out what column and row you are in, and then turn the column number into a letter. Next, create strings by concatenating the column letter with the row numbers needed for your formula. Doing ...

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