You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q142387
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.

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.

Article ID: 142387 - Last Review: 06/30/2004 21:16:46 - Revision: 4.3

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • kbhowto kbprogramming KB142387