Datasheet

Book IX
Chapter 1
Automation with Other
Office Programs
677
Exporting Data to Excel
Figure 1-7:
The final
merged
document in
Word.
Exporting Data to Excel
Many Office users familiar with Excel just don’t understand the power and
flexibility of Access. And many higher executives are used to viewing and
printing tables of data from an Excel spreadsheet. So even though you’re
convinced that everyone in your organization (and perhaps, the world)
should use Access instead of Excel, you’ll still come across quite a few
people who’d rather see the data in Excel than open an Access database.
Sure, you can export data to Excel (or a number of other formats) from the
Export group on the External Data tab, but that requires one of those pesky
humans to know what to do. As a compromise, you can automate the pro-
cess by writing VBA code to export the data, plus do a number of other for-
matting tasks as well.
Let’s pretend once again that we’re going to create a spreadsheet of every-
one’s phone number in the Customer table. We also want to add a meaningful
title with the date the phone numbers were exported. You can create a button
anywhere in your application to do this, so we’ll just show you the code:
‘Declare and set the Connection object
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
44_532188-bk09ch01.indd 67744_532188-bk09ch01.indd 677 3/29/10 10:59 PM3/29/10 10:59 PM