Charting question

[Guest]

So I am trying to Chart data from numerous worksheets and I am not sure how
this works. So lets say I have a workbook with 3 worksheets named "Build1",
"Build2", "Build3". Each of these sheets holds the same type of data, but
the data is all related to that specific build. On each sheet, I have
several test names, with performance-related data.

On a separate sheet i want to put a scatter chart, that has the test names
on the X axis, times on the y axis, with each series as a different color.

So far I have this:

myChart = myWorkbook.Charts.Add(missing, missing, missing,
missing) as Excel.Chart;
myExcelApp.ActiveChart.ChartType =
Excel.XlChartType.xlXYScatterLines;

And I have this, but it doesn't seem to do what I want...

myExcelApp.ActiveChart.SetSourceData(myWorksheet.get_Range("A2",
"D12"), Excel.XlRowCol.xlRows);

myExcelApp.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Sheet_Name");

Question:
1. How do I have the chart grab the sourcedata from numerous sheets?

 

[N10]

So I am trying to Chart data from numerous worksheets and I am not sure
how
this works. So lets say I have a workbook with 3 worksheets named
"Build1",
"Build2", "Build3". Each of these sheets holds the same type of data, but
the data is all related to that specific build. On each sheet, I have
several test names, with performance-related data.

On a separate sheet i want to put a scatter chart, that has the test names
on the X axis, times on the y axis, with each series as a different color.

So far I have this:

myChart = myWorkbook.Charts.Add(missing, missing, missing,
missing) as Excel.Chart;
myExcelApp.ActiveChart.ChartType =
Excel.XlChartType.xlXYScatterLines;

And I have this, but it doesn't seem to do what I want...


myExcelApp.ActiveChart.SetSourceData(myWorksheet.get_Range("A2",
"D12"), Excel.XlRowCol.xlRows);

myExcelApp.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject,
"Sheet_Name");

Question:
1. How do I have the chart grab the sourcedata from numerous sheets?

Hi There

I have had to perform a similar task in the past.

My work around involved copying all the data be graphed to a single pair of
ranges ( hide them if you want) and thereafter setting the X and Y data
columns as range variables.

At this point you simply use the range variables to set the approiate
chart series ;

Here is an example I use

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim task As Range
Set task = Selection
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = task

There are probey many more elegant ways to achieve the same out come but as
no one else appeared to answer you I thought Id reply.

Hope it helps

N10

 

[Jon Peltier]

It's entirely possible to chart data from different sheets. Here's the
manual approach:

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

In code you should dispense with SetSourceData, and instead add each series
individually using SeriesCollection.NewSeries, then assigning the ranges to
the .XValues and .Values properties of the new series. For the pure VBA
approach to adding a series, look here:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#addseries

I'm sure you could figure out how to convert it to that .Net stuff.

- Jon