Help Creating a Excel graph in Access with VBA

[Mitch]

I am using Access to create an Excel spreadsheets with graphs related
to rows on the sheet1 to the graph on sheet2. I am using the same data
but different subsets of the data to make different spreadsheets for
different groups. The spreadsheet formats are the same for each of the
different groups. So I am using a loop to requery the data for the
different groups and create a new spreadsheet for each group. The
three graphs on sheet 2 will work the first time through the loop but
when it creates a new spreadsheet for the next group I get these
errors.
"1004 Application-defined or object-defined error"
"1004 Unable to set the Position property of the Legend class"
"1004 "Method 'Worksheets' of object '_Global' failed"

I have to close Access for the graph to work the first time.

Dim myChtObj As Excel.ChartObje ct
Set objSheet = objXL.Worksheet s("Sheet2")
objSheet.Activa te
j = 0
For i = 1 To 3
Set myChtObj = objSheet.ChartO bjects.Add (Left:=10, Width:=750,
Top:=10 + j, Height:=400)
objSheet.ChartO bjects(i).Activ ate
With objXL.ActiveCha rt
.ChartType = xlXYScatterLine s
.SeriesCollecti on.Add
Source:=Workshe ets("Sheet1").R ange("A18:AN20" )
.Legend.Positio n = xlLegendPositio nBottom
End With
j = j + 410
Next i

I would appreciate any help.

Mitch

[Mitch]

I found my problem. After the first spreadsheet is closed there is
still and instance of Excel running and then when the second
spreadsheet opens it starts another instance of Excel. I was wonder if
anyone has a way of using the instance of Excel that is created when it
opens the spreadsheet to write to. I am going to have to inform the
end user that all instances of Excel have to be closed before he starts
to make the reports in the spreadsheets.
Mitch wrote:

I am using Access to create an Excel spreadsheets with graphs related
to rows on the sheet1 to the graph on sheet2. I am using the same data
but different subsets of the data to make different spreadsheets for
different groups. The spreadsheet formats are the same for each of the
different groups. So I am using a loop to requery the data for the
different groups and create a new spreadsheet for each group. The
three graphs on sheet 2 will work the first time through the loop but
when it creates a new spreadsheet for the next group I get these
errors.
"1004 Application-defined or object-defined error"
"1004 Unable to set the Position property of the Legend class"
"1004 "Method 'Worksheets' of object '_Global' failed"

I have to close Access for the graph to work the first time.

Dim myChtObj As Excel.ChartObje ct
Set objSheet = objXL.Worksheet s("Sheet2")
objSheet.Activa te
j = 0
For i = 1 To 3
Set myChtObj = objSheet.ChartO bjects.Add (Left:=10, Width:=750,
Top:=10 + j, Height:=400)
objSheet.ChartO bjects(i).Activ ate
With objXL.ActiveCha rt
.ChartType = xlXYScatterLine s
.SeriesCollecti on.Add
Source:=Workshe ets("Sheet1").R ange("A18:AN20" )
.Legend.Positio n = xlLegendPositio nBottom
End With
j = j + 410
Next i

I would appreciate any help.

Mitch