Create Excel Chart via COM ?

  • Thread starter Thread starter Andrew Kennard
  • Start date Start date
A

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
 
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
 
Back
Top