Chart location method fails using sheet name

[deko]

When using automation from Access to create an embedded chart, the chart
appears on the first worksheet in the workbook rather than the sheet
specified with the Location method:

Dim xlapp As Excel.Application
Dim cht As Excel.Chart
Set xlapp = CreateObject("Excel.Application")
Set cht = xlapp.Workbooks(strXlsFile).Charts.Add
Set cht = cht.Location(Where:=xlLocationAsObject, Name:="Sheet3")

The chart appears embedded in Sheet1, not Sheet3.

I understand the Location method creates a new chart object (destroying any
reference to the original chart object), so the return value of the Location
method needs to be assigned to the cht object variable (which can then be
used for setting SourceData and other properties). Also, the Charts.Add
method by default creates the chart on a new sheet - so apparently the
Location method is successfully defining the chart as an embedded chart, but
why is the Name parameter not working?

I tried following a suggestion made earlier using the below code, but got
the same results:

Set cht = xlapp.Workbooks(strXlsFile).Charts.Add
For i = cht.SeriesCollection.Count To 1 Step -1
cht.SeriesCollection(i).Delete
Next i
cht.SetSourceData
Source:=xlapp.Workbooks(strXlsFile).Worksheets("Sheet3").Range("A1:C" & _

xlapp.Workbooks(strXlsFile).Worksheets("Sheet3").Range("C1").End(xlDown).Row
), _
PlotBy:=xlColumns
cht.ChartType = xlLineMarkersStacked
Set cht = cht.Location(Where:=xlLocationAsObject, Name:="Sheet3")

I'm wondering if the Location method parameter "Where:=xlLocationAsObject"
points to a worksheet object that is undefined since I am running this from
an Access module. But how to define it?

Has anyone run into this before?

Thanks in advance.

 

[deko]

mystery solved!

Dim objSheet As Object
Dim objChart As Object

For p = LBound(sn) To UBound(sn) 'array of sheet names
Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p))
Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(p)).Range("C1").End(xlDown).Row), _
PlotBy:=xlColumns
objChart.ChartType = xlLineMarkersStacked
objChart.Axes(xlCategory, xlPrimary).HasTitle = False
objChart.Axes(xlValue, xlPrimary).HasTitle = False
objChart.HasLegend = False
objChart.ChartTitle.Text = sn(p)
objChart.HasTitle = True
Next p

The ChartObjects collection holds embedded Chart objects, which are accessed
through the ChartObject object; the Chart property of the ChartObject object
is used to access the actual chart. When using automation, the Add method
of the ChartObjects object will add an embedded chart, rather than the Add
method of the Charts object. There's an example here:
http://msdn.microsoft.com/library/d...dc_xl2003_ta/html/odc_XL_manipulatecharts.asp but it's not very well documented.

 

[deko]

mystery solved!

Dim objSheet As Object
Dim objChart As Object

For p = LBound(sn) To UBound(sn) 'array of sheet names
Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p))
Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(p)).Range("C1").End(xlDown).Row), _
PlotBy:=xlColumns
objChart.ChartType = xlLineMarkersStacked
objChart.Axes(xlCategory, xlPrimary).HasTitle = False
objChart.Axes(xlValue, xlPrimary).HasTitle = False
objChart.HasLegend = False
objChart.ChartTitle.Text = sn(p)
objChart.HasTitle = True
Next p

The ChartObjects collection holds embedded Chart objects, which are accessed
through the ChartObject object; the Chart property of the ChartObject object
is used to access the actual chart. When using automation, the Add method
of the ChartObjects object will add an embedded chart, rather than the Add
method of the Charts object. There's an example here:

http://msdn.microsoft.com/library/d...dc_xl2003_ta/html/odc_XL_manipulatecharts.asp
but it's not very well documented.

One issue I've run into now, after implementing this solution, is that Excel
does not quit after the function is complete - even after:

Set xlapp = Nothing
xlapp.Quit

Thinking that the problem was the old "global reference to the automated
application" issue (helpfully described by Mr. Mehta at
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/)

I tried this:

For p = LBound(sn) To UBound(sn)
With xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).ChartObjects.Add
_
(Left:=300, Top:=20, Width:=500, Height:=300).Chart
.ChartType = xlLineMarkersStacked
.SetSourceData Source:=xlapp.Workbooks(strXlsFile).Worksheets _
(sn(p)).Range("A1:C" &
xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Range _
("C1").End(xlDown).Row), PlotBy:=xlColumns
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.HasLegend = False
.ChartTitle.Text = sn(p)
.ChartTitle.Font.Bold = True
.HasTitle = True
End With
Next p

But the problem remains. Apparently Access needs a diaper on this one...

 

[Peter T]

One issue I've run into now, after implementing this solution, is that
Excel

does not quit after the function is complete - even after:

Set xlapp = Nothing
xlapp.Quit

Looking at the first example, before releasing xlApp first release the
others in reverse order:
Set objChart = nothing
Set objSheet = nothing
xlapp.Quit
Set xlapp = Nothing

Not sure about the second example.

Regards,
Peter T