Recipe 12.6 Send Access Data to Excel and Create an Excel Chart | Access Data Analysis Cookbook (Cookbooks)

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:

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

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

Create the procedure that exports the data to Excel and creates a sample chart. Here's the complete listing:

Private Const conQuery = "qryTopTenProducts" Private Const conSheetName = "Top 10 Products" Public Sub CreateExcelChart( )     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          On Error GoTo HandleErr          ' Create Excel Application object.     Set xlApp = New Excel.Application          ' Create a new workbook.     Set xlBook = xlApp.Workbooks.Add          ' Get rid of all but one worksheet.     xlApp.DisplayAlerts = False     For i = xlBook.Worksheets.Count To 2 Step -1         xlBook.Worksheets(i).Delete     Next i     xlApp.DisplayAlerts = True          ' Capture reference to first worksheet.     Set xlSheet = xlBook.ActiveSheet          ' Change the worksheet name.     xlSheet.Name = conSheetName          ' Create recordset.     Set rst = New ADODB.Recordset     rst.Open _      Source:=conQuery, _      ActiveConnection:=CurrentProject.Connection          With xlSheet         ' Copy field names to Excel.         ' Bold the column headings.         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              ' Copy all the data from the recordset         ' into the spreadsheet.         .Range("A2").CopyFromRecordset rst               ' Format the data.         .Columns(1).AutoFit         With .Columns(2)             .NumberFormat = "#,##0"             .AutoFit         End With     End With          ' Create the chart.     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 reference, so you     ' must retrieve a new reference to the chart.     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          ' Display the Excel chart.     xlApp.Visible = True ExitHere:     On Error Resume Next     ' Clean up.     rst.Close     Set rst = Nothing     Set xlSheet = Nothing     Set xlBook = Nothing     Set xlApp = Nothing     Exit Sub      HandleErr:     MsgBox Err & ": " & Err.Description, , "Error in CreateExcelChart"     Resume ExitHere End Sub

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.