By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Help Creating a Excel graph in Access with VBA

P: n/a
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.ChartObject
Set objSheet = objXL.Worksheets("Sheet2")
objSheet.Activate
j = 0
For i = 1 To 3
Set myChtObj = objSheet.ChartObjects.Add (Left:=10, Width:=750,
Top:=10 + j, Height:=400)
objSheet.ChartObjects(i).Activate
With objXL.ActiveChart
.ChartType = xlXYScatterLines
.SeriesCollection.Add
Source:=Worksheets("Sheet1").Range("A18:AN20")
.Legend.Position = xlLegendPositionBottom
End With
j = j + 410
Next i

I would appreciate any help.

Mitch

Oct 15 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.ChartObject
Set objSheet = objXL.Worksheets("Sheet2")
objSheet.Activate
j = 0
For i = 1 To 3
Set myChtObj = objSheet.ChartObjects.Add (Left:=10, Width:=750,
Top:=10 + j, Height:=400)
objSheet.ChartObjects(i).Activate
With objXL.ActiveChart
.ChartType = xlXYScatterLines
.SeriesCollection.Add
Source:=Worksheets("Sheet1").Range("A18:AN20")
.Legend.Position = xlLegendPositionBottom
End With
j = j + 410
Next i

I would appreciate any help.

Mitch
Oct 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.