Help with SetSourceData

[EAB1977]

My codes blowing up @ the SetSourceData line and I cannot figure out
why. All I am trying to do is to create a chart via Access to Excel
using automation.

Set dbPrint = CurrentDb
strSQL = "SELECT tblEmployee.UserName, Sum
(tblStandardTracking.StdsCreated) AS NumStdsCreated" _
& " FROM tblStandardTracking INNER JOIN tblEmployee ON
tblStandardTracking.EmpID = " _
& " tblEmployee.EmployeeID2 WHERE DateStdCreated BETWEEN #" &
Forms!frmCriteria!txtStartDate & "# AND #" _
& Forms!frmCriteria!txtEndDate & "# GROUP BY
tblEmployee.UserName ORDER BY" _
& " Sum(tblStandardTracking.StdsCreated) DESC"
Set qdf = dbPrint.QueryDefs("qryNumberofStandards")
qdf.SQL = strSQL

Set qdf = Nothing

Set rsPrint = dbPrint.OpenRecordset("qryNumberofStandards")
Set xl = CreateObject("Excel.Application")

With xl
.Visible = True
.Interactive = True
.ScreenUpdating = True
.DisplayAlerts = True
.Workbooks.Open ("PrintGrading.xlt")
.Sheets("Sheet1").Select
.Range("A2").Select
Do Until rsPrint.EOF
.ActiveCell.Value = rsPrint.Fields(0).Value
.ActiveCell.Offset(0, 1).Value = rsPrint.Fields(1).Value
.ActiveCell.Offset(1, -1).Select
Loop
.Range("A1").Select
Do
If .ActiveCell.Value <> "" Then
.ActiveCell.Offset(1, 0).Select
Else
Exit Do
End If
Loop
.Range("A1:" & .ActiveCell.Offset(0, 1).Address(False,
False)).Select
.Charts.Add
.ActiveChart.ChartType = 51
.ActiveChart.SetSourceData Source:=.Sheets(1).Range("A1:"
& .ActiveCell.Offset(0, 1).Address(False, False)), PlotBy:=2
.ActiveChart.Location Where:=1
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "# of Standards"
.Axes(1, 1).HasTitle = True
.Axes(1, 1).AxisTitle.Characters.Text = "Name"
.Axes(2, 1).HasTitle = True
.Axes(2, 1).AxisTitle.Characters.Text = "Standards"
End With
.Visible = True
.Interactive = True
.ScreenUpdating = True
.DisplayAlerts = True
End With

 

[Joel]

try this

Set dbPrint = CurrentDb
strSQL = "SELECT tblEmployee.UserName, Sum
(tblStandardTracking.StdsCreated) AS NumStdsCreated" _
& " FROM tblStandardTracking INNER JOIN tblEmployee ON
tblStandardTracking.EmpID = " _
& " tblEmployee.EmployeeID2 WHERE DateStdCreated BETWEEN #" &
Forms!frmCriteria!txtStartDate & "# AND #" _
& Forms!frmCriteria!txtEndDate & "# GROUP BY
tblEmployee.UserName ORDER BY" _
& " Sum(tblStandardTracking.StdsCreated) DESC"
Set qdf = dbPrint.QueryDefs("qryNumberofStandards")
qdf.SQL = strSQL

Set qdf = Nothing

Set rsPrint = dbPrint.OpenRecordset("qryNumberofStandards")
Set xl = CreateObject("Excel.Application")

With xl
.Visible = True
.Interactive = True
.ScreenUpdating = True
.DisplayAlerts = True
.Workbooks.Open ("PrintGrading.xlt")
.Sheets("Sheet1").Select
Rowcount = 2
Do Until rsPrint.EOF
.Range("A" & rowcount).Value = rsPrint.Fields(0).Value
.Range("B" & rowCount).Value = rsPrint.Fields(1).Value
RowCount = rowCount + 1
Loop
LastRow = .Range("A1").End(xlDown).Row
Set DataRange = .Range("A1:B" & LastRow)
Set newchart = Charts.Add
newchart.Activate
newchart.ChartType = xlColumnClustered
ActiveChart.SetSourceData DataRange, PlotBy = xlColumns
ActiveChart.Location Where:=1
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "# of Standards"
.Axes(1, 1).HasTitle = True
.Axes(1, 1).AxisTitle.Characters.Text = "Name"
.Axes(2, 1).HasTitle = True
.Axes(2, 1).AxisTitle.Characters.Text = "Standards"
End With
.Visible = True
.Interactive = True
.ScreenUpdating = True
.DisplayAlerts = True
End With


With Sheets("sheet1")

End With