Select the product you need help with

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

Article ID: 142387 - View products that this article applies to.

This article was previously published under Q142387

SUMMARY

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 | Give Feedback

MORE INFORMATION

Steps to Create Example Program

Start a new project in Visual Basic. Form1 is created by default.
Add a command button (Command1) to Form1.

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

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 | Give Feedback