HOWTO: Create Excel Chart w/OLE Automation from Visual Basic

Last reviewed: September 30, 1997
Article ID: Q142387

The information in this article applies to:
  • Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit only, for Windows, version 4.0
  • Microsoft Excel, version 7.0

SUMMARY

This article shows by example how to insert data into a Microsoft Excel version 7.0 worksheet and create a chart by using OLE automation in a Visual Basic application.

MORE INFORMATION

There are five key points you need to keep in mind when creating a Microsoft Excel chart in a Visual Basic program:

  1. A chart can be either on a Worksheet or a Chart sheet.

  2. A chart on a Worksheet is a ChartObject.

  3. A ChartObject has a Chart property, which is a Chart Object.

  4. The data associated with the Chart is part of the SeriesCollection.

  5. You can add a new data series with a Named Range.

NOTE: Complete definitions for these objects can be found in the Microsoft Excel documentation.

A Workbook has a Charts collection, which is the collection of all Chart sheets in the workbook. All Charts on WorkSheets are part of that WorkSheet's ChartObjects collection. Therefore to add a new Chart to a Worksheet, you can use the Add method on the worksheet's ChartObject collection.

The SeriesCollection property of the Chart object contains the reference to the data linked to the table. In the example below, you'll add two data series--each of which contains 10 data points--by using the Add method on the SeriesCollection object.

Although the example passes the Range of cells containing the data as a named range, you could specify a Range in R1C1 notation.

Steps to Create Example Program

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add a command button (Command1) to Form1.

  3. Add the following code to the Command1_Click event procedure:

       Private Sub Command1_Click()
          Dim objXLsheet As Object ' Object reference to Excel Worksheet
          Dim objRange1 As Object  ' First series in the chart
          Dim objRange2 As Object  ' Second series in the chart
          Dim objChart1 As Object  ' Object reference to the chart we create
    
          Dim iRow As Integer ' Index variable for the current Row
          Dim iCol As Integer ' Index variable for the current Row
    
          Dim strTmpRange As String ' Temporarily hold Range in R1C1 notation
    
          Const cNumCols = 10 ' Number of points in each Series
          Const cNumRows = 2  ' Number of Series
    
          ' Create a Worksheet Object:
          Set objXLsheet = CreateObject("Excel.Sheet")
    
          Randomize Timer
    
          ' Insert Random data into Cells for the two Series:
          For iRow = 1 To cNumRows
             For iCol = 1 To cNumCols
                objXLsheet.Cells(iRow, iCol).Value = Int(Rnd * 50) + 1
             Next iCol
          Next iRow
    
          ' Insert Named Ranges:
          For iRow = 1 To cNumRows
             ' Enter the following two lines as one, single line:
             strTmpRange = "R" & iRow & "C" & Format$(1) & ":R" & iRow & "C"
                & Format$(cNumCols)
             ' Enter the following two lines as one, single line:
             objXLsheet.Parent.Names.Add "Range" & Format$(iRow), "=Sheet1!"
                & strTmpRange
          Next iRow
    
          ' Add a ChartObject to the worksheet:
          Set objChart1 = objXLsheet.ChartObjects.Add(100, 100, 200, 200)
    
          ' Assign the Ranges created above as the individual series
          ' for the chart:
          For iRow = 1 To cNumRows
             objChart1.Chart.SeriesCollection.Add "Range" & Format$(iRow)
          Next iRow
    
          ' Make Excel Visible:
          objXLsheet.application.Visible = True
          DoEvents
    
          ' Save the Worksheet to disk. The parent of a WorkSheet is WorkBook.
          objXLsheet.Parent.SaveAs "C:\VB\XLCHART.XLS"
    
          ' Close this instance of Excel:
          objXLsheet.application.Quit
       End Sub
    
    

  4. Press the F5 key to run the program, and click the command button.

At this point, Microsoft Excel starts, and it loads and displays the worksheet with the newly-created chart. If you don't already have a file name as specified on the jXLSheet.Parent.SaveAs line of code, Microsoft Excel saves the file and closes itself down. If you already have a file with the same name, Microsoft Excel brings up a dialog box asking you if you would like to overwrite the existing file.
Keywords          : IAPOLE vb416 VB4WIN vbwin GnrlVb kbprg kbfasttip
Technology        : kbole kbvba
Version           : WINDOWS:4.0
Platform          : WINDOWS
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.