Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SetSourceData
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 Whe=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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SetSourceData
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 Whe=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 "EAB1977" wrote: 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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set Chart SetSourceData in code..? | Excel Programming | |||
I think the problem is in .SetSourceData ?? | Excel Programming | |||
setSourcedata | Excel Programming | |||
SetSourceData for Chart | Excel Programming |