Unable to automate excel chart from access

[trevorC via AccessMonster.com]

Here is my code, copied from excel macro.
This code fails on ' ActiveChart.ChartType = xlColumnClustered '

All other code i have used like this to automate excel has worked ok, but not
this chart.
I think it's the reference to the object that is not correct.
Please help if you can.
regards TrevorC


Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Worksheet_Name
Dim Excel_File
r = Format(Time, "hh-mm")
Excel_File = "c:\Graph Output" & Format(Date, "dd-mm-yy") & " " & r & ".xls"
DoCmd.TransferSpreadsheet 1, 8, "00000 Weekly Repairs Received", Excel_File,
True
DoCmd.TransferSpreadsheet 1, 8, "00000 Weekly Repairs completed", Excel_File,
True
DoCmd.TransferSpreadsheet 1, 8, "00000 Weekly Repairs Dispatched", Excel_File,
True
Set Excel_Workbook = GetObject(Excel_File)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "={""Week Number""}"
ActiveChart.SeriesCollection(1).Values = "='00000_Weekly_Repairs_Received'!
R2C3:R19C3"
ActiveChart.SeriesCollection(1).Name = "='Repairs Received'!R1C3"
ActiveChart.SeriesCollection(2).XValues = "={""Week Number""}"
ActiveChart.SeriesCollection(2).Values = "='00000_Weekly_Repairs_completed'!
R2C3:R19C3"
ActiveChart.SeriesCollection(2).Name = "='Repairs Completed'!R1C3"
ActiveChart.SeriesCollection(3).XValues = "={""Week Number""}"
ActiveChart.SeriesCollection(3).Values = "='00000_Weekly_Repairs_Dispatched'!
R2C3:R19C3"
ActiveChart.SeriesCollection(3).Name = "='Repairs Dispatched'!R1C3"
ActiveChart.Location Where:=xlLocationAsNewSheet
Excel_Workbook.Close
Excel_Application.Quit

 

[Mark Andrews]

Perhaps these links will help:
http://support.microsoft.com/kb/142387
http://forums.aspfree.com/visual-ba...to-launch-excel-charts-from-access-34466.html
http://support.microsoft.com/kb/202169
http://msmvps.com/blogs/nateoliver/archive/2010/03/23/let-s-make-an-excel-chart-from-access.aspx