Microsoft KB Archive/142387

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

Article ID: 142387

Article Last Modified on 6/30/2004

-

APPLIES TO


 * 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 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition

-



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.



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.

Keywords: kbhowto kbprogramming KB142387

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.