Access™ 2007 VBA Programmer's Reference

16.3. Sending Information from Access to Excel

Access 2007 provides the capability to create forms and reports, which include graphs and tables. However, you may want to leverage some of the powerful Excel features, such as the new Charting and Conditional Formatting features new to Office 2007. Also, users may find it useful to be able to export their data in an Excel spreadsheet. The code samples for working with Excel can be found in the code behind the Export Report Manager form in the sample database. As with Outlook, using Excel features from VBA in an Access database solution requires a VBA reference to the Microsoft Excel 12.0 Object Model in the Visual Basic Editor's References dialog box.

16.3.1. Working with Data in Excel

A common scenario in any company is to communicate public data via charts, tables, and graphs through periodic reports. By allowing users to export data directly to Excel, you empower them to leverage the rich set of Excel features to create their own charts and tables at a whim, without having to modify the data or design of the architecture of your Access database solution.

The first example here utilizes a form with a list box control that exposes Queries within the application. The Row Source type of the list box control in Access is Table/Query by default, but you can fill your list box in several different ways. If you want users to be able to choose any Report to export, it is pretty easy to add a few lines of code to populate the list box ...