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).
Here’s how you can create Excel charts in your own Access applications:
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.