ACC97: How to Use Automation to Create a Microsoft Excel Chart

ID: Q184273


The information in this article applies to:

Advanced: Requires expert coding, interoperability, and multiuser skills.


SUMMARY

This article shows you how to use automation to create and format a chart in Microsoft Excel 97.


MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
The following steps demonstrate how to use a Visual Basic for Applications procedure to create a Microsoft Excel 97 chart that is based on a query in the sample database Northwind.mdb:
  1. Start Microsoft Access and open the sample database Northwind.mdb.


  2. Create the following query based on the Orders table and the Order Details Extended query:
    
       Query: qrySalesByCountry
       ------------------------------------------------------------------
          Type: Select Query
          Join: [Orders].[OrderID] <-> [Order Details Extended].[OrderID]
    
          Field: ShipCountry
             Table: Orders
             Total: Group By
          Field: ExtendedPrice
             Query: Order Details Extended
             Total: Sum 
    Save the query as qrySalesByCountry and close it.


  3. Create a new module and type the following line in the Declarations section if it isn't already there:


  4. 
    Option Explicit 
  5. On the Tools menu, click References. In the References dialog box, ensure that the Microsoft Excel 8.0 Object Library check box is selected, and then click OK.


  6. Type or paste the following procedure into the module:


  7. 
    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 = _
             xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
       ' Create a new chart.
       Set xlChartObj = xlApp.Charts.Add
       ' Format the chart.
       With xlChartObj
    
          ' Specify chart type as 3D.
          .ChartType = xl3DColumn
          ' Set the range of the chart.
          .SetSourceData Source:=xlSourceRange, _
                PlotBy:=xlColumns
          ' Specify that the chart is located on a new sheet.
          .Location Where:=xlLocationAsNewSheet
          ' Create and set the title; set title font.
          .HasTitle = True
          With .ChartTitle
             .Characters.Text = _
                "Total Sales by Country"
             .Font.Size = 18
          End With
    
          ' 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.
          .Axes(xlSeries).Delete
          ' Delete the legend.
          .HasLegend = False
          ' Set each datapoint to show the dollar amount
          ' and format the datapoint to be currency
          ' with no decimals.
          With .SeriesCollection(1)
             .ApplyDataLabels Type:=xlDataLabelsShowValue
             .DataLabels.NumberFormat = "$#,##0"
          End With
    
       End With
    
       ' Position the points further from the tops
       ' of the columns.
       For Each xlColPoint In _
            xlChartObj.SeriesCollection(1).Points
          xlColPoint.DataLabel.Top = _
                xlColPoint.DataLabel.Top - 11
       Next xlColPoint
       ' Save and close the workbook
       ' and quit Microsoft Excel.
       With xlWrkbk
          .Save
          .Close
       End With
    
       xlApp.Quit
    
    Exit_CreateChart:
       Set xlSourceRange = Nothing
       Set xlColPoint = Nothing
       Set xlChartObj = Nothing
       Set xlWrkbk = Nothing
       Set xlApp = Nothing
    Exit Function
    
    Err_CreateChart:
    
       MsgBox CStr(Err) & " " & Err.Description
       Resume Exit_CreateChart
    
    End Function 
  8. On the Debug menu, click Compile And Save All Modules.


  9. Press CTRL+G to open the Debug window.


  10. To run this procedure, type the following line in the Debug window, and then press ENTER:


  11. 
    ?CreateChart("qrySalesByCountry","c:\Sales.xls") 
  12. Open the file c:\Sales.xls in Microsoft Excel 97. Click the chart sheet Chart1 to view the Sales By Country chart.



REFERENCES

For more information about getting help with Visual Basic for Applications, please see the following article in the Microsoft Knowledge Base:

Q163435 VBA: Programming Resources for Visual Basic for Applications

Additional query words: Graph Plot Data Series


Keywords          : kbdta AccCon IntpOlea 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999