Access 2003 VBA Programmer's Reference

15.3. Sending Information from Access to Excel

Within your Access database, you can create detailed reports including graphs and tables. However, it's impossible to design enough reports to please all of your users, all the time. Sometimes it's better to simply allow your users to export some of the data to Excel where they can manipulate the data in a variety of different ways. In addition to exporting data from Access to Excel, you can even create charts in Excel directly from Access.

First, add a reference to the Excel 11 object model from the References dialog box. Now you can manipulate not only the Excel application, but worksheets, cells, and graphs.

15.3.1. Provide Management with Flexible Data Access

Within any company, you typically have a variety of managers. Some are quite technically savvy while others are not. Some love to fiddle and massage data, creating their own reports and graphs. By allowing management to export a variety of queries directly to Excel, you don't have to worry about designing a new report every time they want to view the data in a slightly different manner. Our first example utilizes a form (shown in Figure 15-5) with a list box that a manager can use to choose the query he or she wants to export.

When you create your form and list box, make sure to set the Row Source for the list box to Value List. The default Row Source value of a list box is Table/Query. You can fill your list box in two different ways. If you want the managers to be able ...