excel chart setsourcedata gets corrupted

Question

Hello, I'm building a VSTO solution for an Excel template and generating a simple time-series line chart.  The category axis is DateTime and when the scale of the x-axis results in very few, if any, empty data points for multiple series, the chart renders correctly.  But when the scale of the x-axis results in a lot of empty data points, the range that is set in the SetSourceData method gets corrupted.  For example;

The data looks something like this on the spreadsheet for, say 1000 rows.

      A               B            C              D            E

DateTime     Status     Series1     Series2. . .SeriesN

Where the values in each cell are links to the data in another spreadsheet (='DataSheet'!A2).  The range passed to SetSourceData looks like;

$A$2:$A$1002,$C$2:$D$1002

when there are only 2 series.  Selecting the DateTime column as the category axis and the values in Series1 and Series2.  And this works fine when there is data for each series for "most" of the datetime stamps, but when the values in the DateTime column are, for example, in 5 minute intervals and Series2 has valid data for each 5 minute stamp. . .but Series1 only has data for each hour. . . .

Then, for some reason, the data that gets selected looks something like this;

='ChartSheet'!$A$2:$B$1002,'ChartSheet'!$D$2:$D$1002

The category axis is now corrupted because it includes the Status column AND we've lost Series1.  Can anyone speak to what the Excel charting engine may be doing in the background here?  Any assistance would be greatly appreciated.  Thanks!

Answer 1

Doug -

As a matter of fact, I almost always add the series to my charts individually. I never am surprised this way.

Answer 2

Thanks everyone, for the tips.  I've tried the approach that Jon mentioned above, but the source range seemed to consistently get changed/corrupted.  I eventually decided to add the series directly and that, so far, has fixed our issues.  Thanks again for the help!!

Answer 3

If you want to ensure that Excel gets the range the way you want, include the header row in your SetSourceData range, but clear the cell above the X values, that is, the cell that says "DateTime". 

The blank cell in conjunction with the otherwise filled first row and column simply helps Excel parse your data range.

You can restore the label after the chart knows the source data.

Answer 4

You may find something here that's useful

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