Recipe 12.6 Send Access Data to Excel and Create an Excel Chart

12.6.1 Problem

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

12.6.2 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:

Figure 12-10. qryTopTenProducts in datasheet view

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

12.6.3 Discussion

Two constants are declared in this procedure—one for the name of the query used to export data, and one for the name of the worksheet in Excel:

Private Const conQuery = "qryTopTenProducts"
Private Const conSheetName = "Top 10 Products"

You need to declare an ADO Recordset variable as well as Excel Application, Workbook, Worksheet, and Chart object variables:

Dim rst As ADODB.Recordset

' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlChart As Excel.Chart

Dim i As Integer

The Application object variable is needed to launch Excel; the Workbook variable is needed to create a new workbook; the Worksheet variable is needed to work with the worksheet when exporting the data; and the Chart variable is needed for creating and manipulating the chart.

The first section of code launches Excel, creates a new workbook, removes all but one worksheet, and renames the worksheet:

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
    xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = conSheetName

Next, the ADO recordset is created based on the saved query:

Set rst = New ADODB.Recordset
rst.Open _
 Source:=conQuery, _
 ActiveConnection:=CurrentProject.Connection

Once the recordset is opened, the field names are copied into the Excel worksheet and formatted:

With xlSheet
    With .Cells(1, 1)
        .Value = rst.Fields(0).Name
        .Font.Bold = True
    End With
    With .Cells(1, 2)
        .Value = rst.Fields(1).Name
        .Font.Bold = True
    End With

Only a single line of code is needed to copy the data from the ADO recordset to the Excel worksheet:

.Range("A2").CopyFromRecordset rst

Next, the columns are formatted one at a time, using Autofit to size the rows to the widest entry, and assigning a number format to the second column:

    .Columns(1).AutoFit
    With .Columns(2)
        .NumberFormat = "#,##0"
        .AutoFit
    End With
End With

The chart is then created and formatted using the Chart object:

Set xlChart = xlApp.Charts.Add
With xlChart
    .ChartType = xl3DBarClustered
    .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
    .PlotBy = xlColumns
    .Location _
     Where:=xlLocationAsObject, _
     Name:=conSheetName
End With

Setting the location loses the references, so you must retrieve a new reference to the Chart object. The chart is then formatted using the methods and properties of the Chart object:

With xlBook.ActiveChart
    .HasTitle = True
    .HasLegend = False
    With .ChartTitle
        .Characters.Text = conSheetName & " Chart"
        .Font.Size = 16
        .Shadow = True
        .Border.LineStyle = xlSolid
    End With
    With .ChartGroups(1)
        .GapWidth = 20
        .VaryByCategories = True
    End With
    .Axes(xlCategory).TickLabels.Font.Size = 8
    .Axes(xlCategoryScale).TickLabels.Font.Size = 8
End With

The worksheet and chart are then displayed by setting the Application object's Visible property to True:

xlApp.Visible = True

Finally, the cleanup code runs, shutting down all the objects that have been used and reclaiming memory:

rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

The examples shown here barely scratch the surface of the capabilities of Excel Automation. Excel has a complex object model that is very easy to get lost in!

If you can't figure out the proper syntax for working with an Excel Automation object, launch Excel and choose Tools Macro Record new macro from the menu, then record the actions that you are having problems with. Once you stop the macro recorder, you can examine the code that was created by pressing Alt-F11 and expanding the Modules node. You may then be able to figure out how to plug the code into your Access code.