Access.2007.VBA.Bibl..

J

ust as you might want to export Access data to Word documents to
take advantage of their superior formatting and transportability, you
may also want (or need) to export Access data to Excel worksheets, so
users can review, edit, or add data, or perform various numerical calcula-
tions, in a familiar and widely used format (all Office users have Excel,
whereas only some have Access). Excel worksheets are often used for enter-
ing and analyzing numerical (and text) data, such as timesheets, applica-
tions, and other forms. Or you may want to export Access data to a simple
rows-and-columns worksheet, so that users can manipulate the data in vari-
ous ways and produce charts based on the data, using the tools in Excel.

This chapter describes how you can export data to Excel spreadsheets for a
variety of purposes. You can export Access data to Excel using a command
on the new Ribbon, or use the TransferSpreadsheetmethod in a single
line of code to do a basic export of all the data in a table or query to a plain
worksheet, or write more complex VBA Automation code to create a fully
formatted worksheet filled with Access data.

Strictly speaking, an .xls (or the new .xlsx) file is a
workbook; each workbook contains one or more
worksheets. However, in general parlance you will hear (and read) worksheet
used to reference an .xls file, a practice carried over from the earliest days of
Excel, before workbooks were added to the interface. I will follow that usage
except when it is necessary to distinguish between a workbook and a work-
sheet, such as in describing how a procedure works.

NOTENOTE

IN THIS CHAPTER

The Excel object model

Creating worksheets from

the Ribbon

Creating worksheets from

templates

Formatting worksheets in

VBA code

Filling Excel worksheets with

Access timesheet data

Working with Excel

Worksheets