Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Create Excel Chart w/OLE Automation from Visual Basic

View products that this article applies to.


This article illustrates how you can use Automation in a Visual Basic program to create an Excel workbook that contains a chart embedded on a worksheet.

↑ Back to the top

More information

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 oXL As Object        ' Excel application
        Dim oBook As Object      ' Excel workbook
        Dim oSheet As Object     ' Excel Worksheet
        Dim oChart As Object     ' Excel Chart
        Dim iRow As Integer      ' Index variable for the current Row
        Dim iCol As Integer      ' Index variable for the current Row
        Const cNumCols = 10      ' Number of points in each Series
        Const cNumRows = 2       ' Number of Series
        ReDim aTemp(1 To cNumRows, 1 To cNumCols)
        'Start Excel and create a new workbook
        Set oXL = CreateObject("Excel.application")
        Set oBook = oXL.Workbooks.Add
        Set oSheet = oBook.Worksheets.Item(1)
        ' Insert Random data into Cells for the two Series:
        Randomize Now()
        For iRow = 1 To cNumRows
           For iCol = 1 To cNumCols
              aTemp(iRow, iCol) = Int(Rnd * 50) + 1
           Next iCol
        Next iRow
        oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
        'Add a chart object to the first worksheet
        Set oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart
        oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols)
        ' Make Excel Visible:
        oXL.Visible = True
        oXL.UserControl = True
    End Sub
  4. Press the F5 key to run the program, and click the command button.
Results: Microsoft Excel starts, and a new workbook is created. Random data is added to cells A1:J2 and a chart is embedded on the first worksheet. The embedded chart uses the random data for its source.

↑ Back to the top

Keywords: KB142387, kbprogramming, kbhowto

↑ Back to the top

Article Info
Article ID : 142387
Revision : 7
Created on : 6/30/2004
Published on : 6/30/2004
Exists online : False
Views : 446