This article shows you how to use automation to create and format a
chart in Microsoft Excel.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
The following steps demonstrate how to use a Visual Basic for Applications
procedure to create an Excel chart that is based on a query in
the sample database Northwind.mdb:
: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
Start Access and open the sample database Northwind.mdb.
- Create the following query based on the Orders table and the Order
Details Extended query:
Save the query as qrySalesByCountry and close it.
Type: Select Query
Join: [Orders].[OrderID] <-> [Order Details Extended].[OrderID]
Total: Group By
Query: Order Details Extended
- Create a new module and type the following line in the Declarations
section if it isn't already there:
- On the Tools menu, click References. In the References dialog box, ensure that the Microsoft Excel 9.0 Object Library check box is selected and click OK.
- Type or paste the following procedure into the module:
Function CreateChart(strSourceName As String, _
strFileName As String)
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlColPoint As Excel.Point
On Error GoTo Err_CreateChart
' Create an Excel workbook file based on the
' object specified in the second argument.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, strFileName, False
' Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
' Determine the size of the range and store it.
Set xlSourceRange = _
' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
' Format the chart.
' Specify chart type as 3D.
.ChartType = xl3DColumn
' Set the range of the chart.
.SetSourceData Source:=xlSourceRange, _
' Specify that the chart is located on a new sheet.
' Create and set the title; set title font.
.HasTitle = True
.Characters.Text = _
"Total Sales by Country"
.Font.Size = 18
' Rotate the x-axis labels to a 45-degree angle.
.Axes(xlCategory).TickLabels.Orientation = 45
' Delete the label at the far right of the x-axis.
' Delete the legend.
.HasLegend = False
' Set each datapoint to show the dollar amount
' and format the datapoint to be currency
' with no decimals.
.DataLabels.NumberFormat = "$#,##0"
' Position the points further from the tops
' of the columns.
For Each xlColPoint In _
xlColPoint.DataLabel.Top = _
xlColPoint.DataLabel.Top - 11
' Save and close the workbook
' and quit Microsoft Excel.
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
MsgBox CStr(Err) & " " & Err.Description
- On the Debug menu, click Compile Northwind.
- Press CTRL+G to open the Immediate Window.
- To run this procedure, type the following line in the Debug window, and then press ENTER:
- Open the file c:\Sales.xls in Excel. Click the chart sheet Chart1 to view the Sales By Country chart.
For more information about getting help with Visual Basic for
Applications, please see the following article in the Microsoft Knowledge Base:
OFF2000: Programming Resources for Visual Basic for Applications
Article ID: 202169 - Last Review: October 11, 2006 - Revision: 3.2
- Microsoft Access 2000 Standard Edition
|kbhowto kbprogramming KB202169|