Access.2007.VBA.Bibl..

Y

ou 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 created from a template, and sort the data, create totals, or
apply formatting using Excel commands in VBA code. The following sections
describe using these three methods to export Access data to Excel.

The sample database for this chapter is Access to

NOTENOTE Excel.accdb.

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

Analyzing Data with Excel