Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
deko
 
Posts: n/a
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Plotting different length series in a chart Big Red Charts and Charting in Excel 2 May 5th 05 02:00 PM
Dynamic series in Chart Jeff Charts and Charting in Excel 2 February 24th 05 12:54 PM
Help! I'm losing one series of data in an overlay chart. Charts are Us Charts and Charting in Excel 1 January 4th 05 06:57 PM
graphing two series on x axis Ladislav Ligart Charts and Charting in Excel 2 December 6th 04 07:50 PM
How do I force a series in a Bar-Line Chart to be a Bar? Excel Challenged Charts and Charting in Excel 1 December 6th 04 05:16 AM


All times are GMT +1. The time now is 12:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"