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


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.

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.

Artikelnummer: 142387 – Letzte Überarbeitung: 23.03.2009 – Revision: 1