Access Cookbook

Send Access Data to Excel and Create an Excel Chart

Problem

You want to export data from Access to Excel and create a chart programmatically.

Solution

You can use an ADO Recordset object to export data to Excel programmatically, then use Automation with Excel to create a chart based on the exported data.

Load and run frmExcel from 12-06.MDB. This form calls out to Excel, passing in the values from a recordset to create an Excel spreadsheet and chart based on sales data from the Northwind sample database (see Figure 12-9).

Figure 12-9. The finished Excel worksheet and chart

Here’s how you can create Excel charts in your own Access applications:

1. Create the query that will hold your data. In the sample database, you’ll find qryTopTenProducts, which calculates the top 10 products by dollar amount sold. There are two columns: the product name and the total dollar amount. The datasheet view of the query is shown in Figure 12-10.

   

   Figure 12-10. qryTopTenProducts in datasheet view

2. Set a reference in your project to the Microsoft Excel object library and the ADO library, as shown in Figure 12-11.

   

   Figure 12-11. References needed to make the code work

3. Create the procedure that exports the data to ...