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

Chapter 7. Working with Excel Worksheets

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

Just 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 calculations, in a familiar and widely used format (all Office users have Excel, whereas only some have Access). Excel worksheets are often used for entering and analyzing numerical (and text) data, such as timesheets, applications, 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 various 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 TransferSpreadsheet method 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.

Note

Strictly speaking, an .xls (or the new .xlsx) file is a workbook; each workbook contains one or more worksheets. However, in general parlance ...