Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

Chapter 3. Analyzing Data with Excel

IN THIS CHAPTER

• Exporting Access data to Excel using a Ribbon command

• Creating new Excel worksheets from templates

• Formatting Access data exported to an Excel worksheet

You can print Access data directly, using Access reports, as described in Chapter 1, or you can present the data interactively in PivotTables or PivotCharts. But, as mentioned in Chapter 1, these options have some limitations, because you can only work interactively with PivotTables and PivotCharts within an Access database. If you (or others) want to be able to manipulate and analyze Access data without the need to have Access installed, Excel worksheets are an excellent choice.

Rather than preparing Access reports, PivotTables, or PivotCharts (or in addition to them), you can export data to Excel, and allow users to analyze the data with Excel's tools. This option is available to everybody who has Office installed, because even the most basic editions of Office include Excel, whereas Access is only included in some editions (the more expensive ones).

You can export the Access data to an unformatted worksheet and let the users work with it as they want (this works fine if they just need the data and don't require fancy formatting), or you can create Excel templates and export the data to a new worksheet made from a template; this technique allows you to do some of the formatting in advance.

A third alternative is to export the Access data into either a default worksheet or a worksheet ...