G
Geographer
Hi:
I am trying to implement some code from an Access Manual I have. The purpose
is to export a query out to Excel and create a line chart out of the
information as a temporal changes - how many people migrate over time. The
problem is that the data ranges are not in the right place. I would like the
time series to be the X axis and the volume of migration to be the Y axis.
But in reality it turns out to be the opposite. I tried to swap the columns
in my query so that when they exported out they would be transposed. But that
does not work. Another problem is that the legend has two series, but I am
only trying to plot out one series. I have had a similar problem with
XYscatter charts but someone from the Excel list offered this solution:
"chrtnew.SeriesCollection(2).Delete
chrtnew.SeriesCollection(1).XValues = Sheets("price_acre").range("D2
" &
lngRows + 1)
chrtnew.SeriesCollection(1).Values = Sheets("price_acre").range("E2:E" &
lngRows + 1)"
The example in the preceding sample was for an XY scatter chart. The code
that follows is for an XlLine
Any help would be appreciated.
Public Function ExportMigrationDate()
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 "qryMigrationDates", CurrentProject.Connection
wksCurr.Name = "Dates"
lngRows = wksCurr.range("a2").copyfromrecordset(rs)
wksCurr.range("A1:A100").NumberFormat = "mm/dd/yy"
chrtnew.chartwizard wksCurr.range("A2", "B" & lngRows + 1), gallery:=xlLine, _
PlotBy:=xlColumns, HasLegend:=Trud, Title:="Migration Temporal Trends"
appExcel.Visible = True
End Function
I am trying to implement some code from an Access Manual I have. The purpose
is to export a query out to Excel and create a line chart out of the
information as a temporal changes - how many people migrate over time. The
problem is that the data ranges are not in the right place. I would like the
time series to be the X axis and the volume of migration to be the Y axis.
But in reality it turns out to be the opposite. I tried to swap the columns
in my query so that when they exported out they would be transposed. But that
does not work. Another problem is that the legend has two series, but I am
only trying to plot out one series. I have had a similar problem with
XYscatter charts but someone from the Excel list offered this solution:
"chrtnew.SeriesCollection(2).Delete
chrtnew.SeriesCollection(1).XValues = Sheets("price_acre").range("D2
" &lngRows + 1)
chrtnew.SeriesCollection(1).Values = Sheets("price_acre").range("E2:E" &
lngRows + 1)"
The example in the preceding sample was for an XY scatter chart. The code
that follows is for an XlLine
Any help would be appreciated.
Public Function ExportMigrationDate()
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 "qryMigrationDates", CurrentProject.Connection
wksCurr.Name = "Dates"
lngRows = wksCurr.range("a2").copyfromrecordset(rs)
wksCurr.range("A1:A100").NumberFormat = "mm/dd/yy"
chrtnew.chartwizard wksCurr.range("A2", "B" & lngRows + 1), gallery:=xlLine, _
PlotBy:=xlColumns, HasLegend:=Trud, Title:="Migration Temporal Trends"
appExcel.Visible = True
End Function