itipu
03-31-2010, 01:00 AM
So.. I have a little Access database with a button to generate Excel workbook with a chart.
Basically I open Excel, copy records from a query to a sheet called "Data" and add a Chart
The format for records in "Data" is as follows:
Column A, A1 always SampleDate (column name), A2 onwards - list of dates... these will always be XValues for all series.
Column B, B1 always column name and Name of 1st Series; B2 onwards - list of values always YValues for 1st series...
Now there might be Column C, Column D and Column E... all of them the same format as Column B (i.e. first cell - name of the series, 2 and down YValues for that series)...
No the following code seems to work, and it sets series correctly but I have problems setting .Values and .XValues
Dim appExcel As Excel.Application
Dim wBook As Workbook
Dim rs As DAO.Recordset
Dim i As Long
Dim rngDataSource As Range
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer
Dim iSrsIx As Integer
Dim srsNew As Series
'Open Excel
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wBook = appExcel.Workbooks.Add
With wBook
.Sheets("Sheet1").Select
.Sheets("Sheet1").Name = "Data"
'Copy Records from qryDataExport_Crosstab to Sheet "Data"
Set rs = CurrentDb.OpenRecordset("qryDataExport_Crosstab")
rs.MoveLast
rs.MoveFirst
For i = 0 To rs.Fields.Count - 1
.Sheets("Data").Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Sheets("Data").Range("A2").CopyFromRecordset rs
Set rs = Nothing
Set rngDataSource = wBook.ActiveSheet.UsedRange
With rngDataSource
iDataRowsCt = .Rows.Count
iDataColsCt = .Columns.Count
End With
'Delete Sheets
.Application.DisplayAlerts = False
.Sheets("Sheet2").Delete
.Sheets("Sheet3").Delete
.Application.DisplayAlerts = True
'Setup Chart
.Charts.Add
.ActiveChart.ChartType = xlXYScatter
.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graph"
.Application.ActiveWindow.Zoom = 100
.ActiveChart.ChartArea.Select
End With
With wBook.ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iSrsIx = 1 To iDataColsCt - 1 Step 1
'' Add each series
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = rngDataSource.Cells(1, iSrsIx + 1)
.Values = rngDataSource.Cells(iDataRowsCt, iSrsIx + 1)
.XValues = rngDataSource.Cells(iDataRowsCt, 1)
End With
Next
End With
I keep on getting Run-time error '1004'; Unable to set XValues property of the Series class.
Also how can you make sure that instead of Excel scale for X and Y, it will just use the exact values from the source range?
I attached Access db just in case, you'll need to complere selection on the Main Form, click Search which will enable Send to Excel button... this button will create Excel sheet and run through the code as above..
Basically I open Excel, copy records from a query to a sheet called "Data" and add a Chart
The format for records in "Data" is as follows:
Column A, A1 always SampleDate (column name), A2 onwards - list of dates... these will always be XValues for all series.
Column B, B1 always column name and Name of 1st Series; B2 onwards - list of values always YValues for 1st series...
Now there might be Column C, Column D and Column E... all of them the same format as Column B (i.e. first cell - name of the series, 2 and down YValues for that series)...
No the following code seems to work, and it sets series correctly but I have problems setting .Values and .XValues
Dim appExcel As Excel.Application
Dim wBook As Workbook
Dim rs As DAO.Recordset
Dim i As Long
Dim rngDataSource As Range
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer
Dim iSrsIx As Integer
Dim srsNew As Series
'Open Excel
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wBook = appExcel.Workbooks.Add
With wBook
.Sheets("Sheet1").Select
.Sheets("Sheet1").Name = "Data"
'Copy Records from qryDataExport_Crosstab to Sheet "Data"
Set rs = CurrentDb.OpenRecordset("qryDataExport_Crosstab")
rs.MoveLast
rs.MoveFirst
For i = 0 To rs.Fields.Count - 1
.Sheets("Data").Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.Sheets("Data").Range("A2").CopyFromRecordset rs
Set rs = Nothing
Set rngDataSource = wBook.ActiveSheet.UsedRange
With rngDataSource
iDataRowsCt = .Rows.Count
iDataColsCt = .Columns.Count
End With
'Delete Sheets
.Application.DisplayAlerts = False
.Sheets("Sheet2").Delete
.Sheets("Sheet3").Delete
.Application.DisplayAlerts = True
'Setup Chart
.Charts.Add
.ActiveChart.ChartType = xlXYScatter
.ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graph"
.Application.ActiveWindow.Zoom = 100
.ActiveChart.ChartArea.Select
End With
With wBook.ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iSrsIx = 1 To iDataColsCt - 1 Step 1
'' Add each series
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = rngDataSource.Cells(1, iSrsIx + 1)
.Values = rngDataSource.Cells(iDataRowsCt, iSrsIx + 1)
.XValues = rngDataSource.Cells(iDataRowsCt, 1)
End With
Next
End With
I keep on getting Run-time error '1004'; Unable to set XValues property of the Series class.
Also how can you make sure that instead of Excel scale for X and Y, it will just use the exact values from the source range?
I attached Access db just in case, you'll need to complere selection on the Main Form, click Search which will enable Send to Excel button... this button will create Excel sheet and run through the code as above..