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.

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.