Creating an Embedded Chart from Microsoft Access Data

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Using VBA, you can easily create a chart based on the data retrieved from a Microsoft Access database. The ChartData procedure shown below uses the data fetched from the Microsoft Access Northwind database to create an embedded chart. The chart is created by using the Add method of the Charts collection. The source of the chart data is provided by the Range object. The CurrentRegion method returns all the non-blank cells surrounding cell A1. The remaining part of the procedure formats the chart by setting various properties. The chart code fragment has been recorded in a separate macro and then pasted into the VBA procedure with modifications made to the settings of some of the properties.

Sub ChartData() Dim db As DAO.database Dim qd As DAO.QueryDef Dim rs As DAO.Recordset Dim mySheet As Worksheet Dim recArray As Variant Dim i As Integer Dim j As Integer Dim pathDb As String Dim qdName As String pathDb = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\northwind.mdb" qdName = "Category Sales for 1997" Set db = OpenDatabase(pathDb)

Set qd = db.QueryDefs(qdName) Set rs = qd.OpenRecordset Set mySheet = Worksheets("Sheet2") With mySheet.Range("A1")

.CurrentRegion.Clear recArray = rs.GetRows(rs.RecordCount) For i = 0 To UBound(recArray, 2) For j = 0 To UBound(recArray, 1)

.0ffset(0, j) = rs.Fields(j).Name .0ffset(0, j).EntireColjmn.AutoFit Next j End With mySheet.Activate Charts.Add

ActiveChart.ChartType = xl3DColumnClustered ActiveChart.SetSourceData _

Source:=mySheet.Cells(1, 1).CurrentRegion, PlotBy:=xlRows ActiveChart.Location Where:=xlLocationAsObject, Name:=mySheet.Name With ActiveChart .HasTitle = True

.ChartTitle.Characters.Text = qdName .Axes(xlCategory).HasTitle = True .Axes(xlCategory).AxisTitle.Characters.Text = "" .Axes(xlSeries).HasTitle = False .Axes(xlValue).HasTitle = True

.Axes(xlValue).AxisTitle.Characters.Text = mySheet.Range("B1") _ & "($)"

.Axes(xlValue).AxisTitle.Orientation = xlUpward End With db.Close End Sub

The result of running the ChartData procedure is shown in Figure 15-18.

Figure 15-18:

You can create an embedded chart programmaticall y with VBA based on the data retrieved from a Microsoft Access table, a query, or an SQL statement.

Was this article helpful?

+1 0

Post a comment