Move chart in Excel from Access

[marle35]

I have code that creates a chart in Excel and moves that chart to a certain sheet. This code works in my home Access 2003 (and Excel 2003), but not from Access 2000 (and Excel 2000), which I need it to work from. It just creates and leaves it in the existing "Sheet1." I want it to be in the existing "ReportCharts." What could be wrong?

The line in question is
.ActiveChart.Location Whe=xlLocationAsObject, Name:="ReportCharts"

The library references seem to be intact in both versions. This is the full section of code:

With objExcel
.Charts.Add
.ActiveChart.SetSourceData Source:=.Sheets("Chart Data").Range("A" & intTitleRow & _
":M" & intCurYearRow), _
PlotBy:=xlRows

.ActiveChart.Location Whe=xlLocationAsObject, Name:="ReportCharts"
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Years(i)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "MONTH"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "COST"
.HasDataTable = False
End With
End With

 

[HarleyQuinn]

Should the line in question not read:

.ActiveChart.Location Whe[red]re[/red]=xlLocationAsObject, Name:="ReportCharts"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[marle35]

I don't know why the "re" got cut off in my post, but that's in the code.

 

[ck1999]

On your work computer

Use the macro recorder and create a chart and make it an object in the reportcharts sheet and then look at the code and see if it is different.

I tested your code but like you I have access and excel 2003.


ck1999

 

[ck1999]

I also found this
XL2000: Error Appears When Changing Chart Location with a Macro
url:

http://support.microsoft.com/kb/211436

ck1999