Using automation to add xy scatter chart

[Geographer]

Hi Excel:

I am using a simple macro in Access to create an XY scatter chart in Excel.
It exports out information in a query, puts it in Excel and makes an XY
scatter chart. The problem is that it only charts one axis. When I go into
'Chart Source' in Excel and look at the 'Series' it is only using the Y axis.
But I want it to utilize both columns D and E to symbolize the relationship
between the acres and the price per acre in my data. Any help you can give me
would be greatly appreciated. My code is enclosed.
TIA

Public Function ExportPrice_Acre()

Dim appExcel As Excel.Application
Dim wkbCurr As Excel.Workbook
Dim wksCurr As Excel.Worksheet
Dim chrNew As Excel.Chart

Dim rs As New ADODB.Recordset
Dim lngRows As Long

Set appExcel = New Excel.Application
Set wkbCurr = appExcel.Workbooks.Add
Set wksCurr = wkbCurr.Activesheet
Set chrtnew = appExcel.charts.Add

rs.Open "qryPrice_Acre", CurrentProject.Connection
wksCurr.Name = "Price_Acre"
lngRows = wksCurr.range("a2").copyfromrecordset(rs)
chrtnew.chartwizard wksCurr.range("D2", "E" & lngRows + 1),
gallery:=xlXYScatter, _
HasLegend:=True, Title:="Price per Acre in Cleveland"

appExcel.Visible = True
End Function

 

[K Dales]

When I try this the chartwizard method is seeing the data as 2 series instead
of one paired x-y set of values. Not sure why because when I use the chart
wizard manually it gets it right but done through code it gets it wrong. You
could build the chart manually instead of with the wizard, but I think it is
easy enough to simply fix the chart that the wizard creates as illustrated
below - add this code after the chrtnew.chartwizard line:

chrtnew.SeriesCollection(2).Delete

chrtnew.SeriesCollection(1).XValues = Sheets("Sheet1").Range("D2" &
lngRows + 1)
chrtnew.SeriesCollection(1).Values = Sheets("Sheet1").Range("E2:E" & lngRows
+ 1)

I hope this does it: works on my simple recreation of your code (without the
query) - but if it does not work quite right the solution will be something
similar to this.

 

[Geographer]

Thank you very much.
I took your suggestion and added in the three lines of code. Thank you very
much, I never would have figured this out on my own. Sorry for the tardy
reply.