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 has been archived. It is offered "as is" and will no longer be updated.
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: 12/04/2015 12:58:19 - 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

  • kbnosurvey kbarchive kbhowto kbprogramming KB142387