Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using automation to add xy scatter chart
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using automation to add xy scatter chart
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a mix of column and scatter chart on one chart. | Charts and Charting in Excel | |||
Scatter chart displays as line chart | Charts and Charting in Excel | |||
Chart Automation | Charts and Charting in Excel | |||
scatter chart insists on plotting as a line chart | New Users to Excel | |||
vc++ automation: opening chart as chart window and setting scale | Excel Programming |