Access Cookbook - Ken Getz [269]
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
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