Create Excel Chart via COM ?

[Andrew Kennard]

Hi all

I've had a look through MDSN but cannot see how you create an excel chart
via COM ? is it possible ?

I can see many examples on how to create and save a speadsheet file.

Can someone send me a link etc to point me in the right direction

Thanks in advance

Andrew Kennard

 

[Tushar Mehta]

Use XL's macro recorder (Tools | Macro > Record new macro...) to get
the necessary code. Then, stick in a XLObj / workbook (or whatever you
are calling the XL Application object / workbook in your code) in front
of everything.

For example, the XL macro recorder might give you:

Sub Macro1()
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A3")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

and you can convert it into:

Option Explicit

Sub modifiedMacro1()
Dim XLObj As Excel.Application, XLWB As Excel.Workbook, _
aChart As Excel.Chart
Set XLObj = CreateObject("Excel.Application")
XLObj.Visible = True
Set XLWB = XLObj.Workbooks.Add
With XLWB.Worksheets(1).Range("a1")
.Offset(0, 0) = 1: .Offset(1, 0) = 2: .Offset(2, 0) = 3
End With
Set aChart = XLWB.Charts.Add
Set aChart = aChart.Location(xlLocationAsObject, _
XLWB.Worksheets(1).Name)
With aChart
.SetSourceData Source:=XLWB.Worksheets(1).Range("a1:a3")
.ChartType = xlColumnClustered
End With
XLWB.Close False
Set XLWB = Nothing
XLObj.Quit
Set XLObj = Nothing
End Sub