How to add series with VBA?
 

How can I add multiple series to an existing Excel graph using automation
from Access 2000? I'm trying to set up a loop that will define the Xvalue
and YValue for each series, but not sure how.

I'm creating up to 100 Excel worksheets at a shot with data from Access
2000, and each worksheet needs to have a graph with multiple series. The
worksheet creation loop looks like this:

For i = 1 to sn.Count
db.Execute "SELECT * INTO [Excel 8.0;Database=" & strXlsPath & "].[" & _
strSheetName & "] FROM tblExcelData", dbFailOnError
Next

Then I create an embedded chart like this:

Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
Set objChart = objSheet.ChartObjects.Add(Left:=170, Top:=12, Width:=500,
Height:=300).Chart
With objChart
.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range("B1").End(xlD own).Row), _
PlotBy:=xlColumns
.ChartType = xlLineMarkersStacked
.Parent.Name = sn(p) & "_Chart1"
.HasLegend = False
.HasTitle = True
.ChartTitle.Text = sn(i)
.ChartTitle.Font.Bold = True
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabels.Font.Size = 10
.Axes(xlCategory).TickLabels.Orientation = 90
End With

Now I need to add several more series - here's where I'm lost.

Do While s < col.Count
With objChart.SeriesCollection.NewSeries
.Name = "s" & s
.Values = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("E" & s
& ":E" & lr)
.XValues = xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("E" &
s & ":E" & lr)
End With
Loop

How do I set the Value and XValue? Could this be done with a string using
the R1C1 reference style? How? Do I need a separate loop to add the
additional series, or can it be done while setting up the first series?

Thanks in advance.