Now you know how to export Access data to various types of Word documents, both in the interface and in VBA code, you can produce highly formatted Word documents filled with Access data. Microsoft promotes mail merge, but in my opinion it is best to avoid this method (especially in Office 2007 and Windows Vista), because of the problems it has with security features. With the three other techniques covered in this chapter, you will know how to produce almost any type of document using other methods, and avoid the problems with locked or unavailable databases when using mail merge when it is needed.

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.

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 worksheet, such as in describing how a procedure works.


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

0 0

Post a comment