Sending data from Access to Excel Graph

[Colvic]

He,

I try to make a Graph in Excel with this VBA running from Access.
It is OK the first time but the next I run the code I recive the error:
Run-time error '1004': Metod 'Sheets' of object'_Global' Failed

When I cancel the Code, and start it again then it is OK the first time but then I get the same error the nest time I run the code.

Private Sub Command10_Click()
'Denne prosedyre gir denne feilen på hverannet forsøk :
'Run-time error '1004': Metod 'Sheets' of object'_Global' Failed

Dim objExcelApp As Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelSheet As Excel.Worksheet
Dim objRecordset As Recordset

Set objRecordset = Application.CurrentDb.OpenRecordset("Qry_Graf-10 - OnsiteBackloggFeilPrGrp")

Set objExcelApp = CreateObject("Excel.Application")
Set objExcelWorkbook = objExcelApp.Workbooks.Add
Set objExcelSheet = objExcelWorkbook.Worksheets.Add

objExcelApp.Visible = True

With objExcelSheet
.Range("A2").CopyFromRecordset objRecordset
.Name = "Chart"
objExcelApp.Charts.Add
objExcelApp.ActiveChart.ChartType = xlColumnClustered
objExcelApp.ActiveChart.SetSourceData Source:=Sheets("Chart").Range("A2:C13"), PlotBy:=xlColumns
objExcelApp.ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
End With


Set objExcelApp = Nothing
Set objExcelWorkbook = Nothing
Set objExcelSheet = Nothing
End Sub


I try to chande the code to this (acording to Microsoft XL2000: Automation Doesn't Release Excel Object from Memory Article ID : 199219)

objExcelSheet.Range("A2").CopyFromRecordset objRecordset
objExcelSheet.Name = "Chart"
objExcelApp.Charts.Add
objExcelApp.ActiveChart.ChartType = xlColumnClustered
objExcelApp.ActiveChart.SetSourceData Source:=Sheets("Chart").Range("A2:C13"), PlotBy:=xlColumns
objExcelApp.ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

But the error is the same.

Can you help me?

 

[SkipVought]

Hi,

An Excel Chart Object has as its parent a WORKBOOK object and NOT the Application Object. I'd change that.

On what statement do you get the error?

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[PHV]

Replace this:
:=Sheets("Chart").Range("A2:C13"), PlotBy
with this:
:=objExcelSheet.Range("A2:C13"), PlotBy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886