Excel chart: xlLocationAsObject not behaving 1

[kndavies]

Hi,

Struggling with this one for hours...

Excel 2000/Access 2000/Windows 2000
Running Excel from Access

I'm adding a chart and need it to be within an existing sheet so:

with xlsApp.application
<code working>
.Charts.Add
.ActiveChart.Location Where:=xlLocationAsObject, "Event Chart"
<code working>

However it refuses to add the chart to the Sheet ("Event Chart") and instead puts it into the first sheet of the workbook.

All help appreciated.

Ken

 

[sfm6s524]

I think you need to use the ChartObjects collection to embed a chart onto a worksheet, instead of the Chart object. Charts.Add is like adding a new 'Chart Sheet' to your workbook. If objXL is your Excel.Application object:

Dim chrtobj as Object
Dim objSht as Object

Set objSht = objXL.Activeworkbook.Sheets("Event Chart")
' Numbers are Left, Top, Width, and Height in points.
Set chrtobj = objSht.ChartObjects.Add(100, 100, 200, 200)
With chrtobj.Chart
.FormatChart blah blah
End With

Ken

 

[kndavies]

Ken,

That's exactly what I needed. Many thanks and have a star

Ken