Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

Building a Regular Chart

While pivot charts are very useful, sometimes you want to chart data using a regular chart, or you do not want your users pivoting the data. In these cases, you have a couple of choices. You can, of course, build the chart manually each time you need it. You can also build the chart manually and use automation from Access to push in updated data. If you're feeling ambitious, you can use VBA to build the chart from scratch.

If you already have a good chart, I recommend saving and opening it, using it as a template, and just changing the data. However, sometimes this is not practical, or you might not want to rely on having an Excel file available. Also, sometimes clients want a generic charting tool run from Access where they can choose the data they want and have it created in a chart. In cases like that, you have to build the chart each time with VBA.

To demonstrate building a chart with VBA, let's start with the data we exported earlier. Assume that you want to show the number of units and total sales of each product in one chart and total sales by location in another—on two axes on the first chart and one axis on the other. You can do this with two Group By queries; select the text fields as Group By and select Quantity and/or TotalCost using Sum as the function. For Example 6-4, the queries are saved as qry_SalesbyProduct and qry_SalesbyCenter.

Example 6-4. Chart from scratch example

Public Sub BuildCharts() Dim xlApp As Excel.Application Dim xlwb As Excel.Workbook ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page