Using automation to add xy scatter chart

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

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:D" &
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.
--
- K Dales


"Geographer" wrote:

> 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

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.

"K Dales" wrote:

> 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:D" &
> 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.
> --
> - K Dales
>
>
> "Geographer" wrote:
>
> > 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