Setting Chart Value Range for Excel2003/2007 - separated Cells

Hi,

I'm currently trying to create charts in Excel via Matlab Code and activeX. The Data I'm using is already written into the Excel-File. I succeeded at creating the first chart which has a simple Range of e.g. D17:D25 as x-Values by using this code:

 Sheet=Workbook.Sheets.Item('Protokoll'); 

 chart=Excel.Charts.Add; 
 chart.ChartType=('xlLineMarkers');
 chart.Name='VGL-Diagramm';
 ChartSheet=Workbook.Sheets.Item('VGL-Diagramm');
 ChartSheet.Move([],Sheet);
 Series = invoke(Excel.ActiveChart,'SeriesCollection',1);
 invoke(Series,'Delete');

 chart.SeriesCollection.NewSeries; 
 chart.SeriesCollection(1).XValues=Sheet.Range(strcat('D17:D',num2str(Index)));
 chart.SeriesCollection(1).Values=Sheet.Range(strcat('I17:I', num2str(Index)));
 chart.SeriesCollection(1).Name='Gasgehalt Sensor';

 chart.SeriesCollection.NewSeries; 
 chart.SeriesCollection(2).XValues=Sheet.Range(strcat('D17:D', num2str(Index)));
 chart.SeriesCollection(2).Values=Sheet.Range(strcat('J17:J', num2str(Index)));
 chart.SeriesCollection(2).Name='Gasgehalt Höhe';

the second chart is supposed to use data that is spread along the sheet, let's say it has to use the values in cells F19 and F22.

I tried to figure out how to set that range, but was not able to.

It tried

chart.SeriesCollection(1).XValues=Sheet.Range('$F$19')+Sheet.Range('$F$22');
and
chart.SeriesCollection(1).XValues=Sheet.Range('$F$19,$F$22);

Also without the $

I also put a ";" in between the cells that didn't help either.

After searching the internet and not finding anything, I hope you can help me out here

Error Code for the 2nd Version (Range separated by ","): ??? Error while evaluating uicontrol Callback

??? Error: Object returned error code: 0x800A03EC

Error in ==> ExcelDatei at 254 chart.SeriesCollection(1).XValues=Sheet.Range('$F$19,$F$22');

Answer 1

[Eric]

You might try the following:

 RangeOBJ = ChartSheet.Range('F19,F22');
 chart.SeriesCollection(1).XValues = RangeOBJ.Areas;

RangeOBJ.Areas returns an Areas collection of Range objects (an area is a contiguous block of cells within a range). I'm not sure if XValues can be set to an Areas collection or not, but it's worth a shot.

Alternatively, you might try

 chart.SeriesCollection(1).XValues = [ChartSheet.Range('F19').Value ChartSheet.Range('F22').Value];

I would think that might work as well. XValues can be set to an array of values rather than a range. Hopefully in this case Matlab can handle passing a two-element array to Excel appropriately.

Good luck,

Eric

[Eric]

So I got curious and tested this out. Setting XValues to an Areas collection failed.

Setting chart.SeriesCollection(1).XValues and chart.SeriesCollection(1).Values arrays was successful.

Of course that's not quite as useful. You get an Excel chart but no traceability to the data (or a chart that changes if you change the data).

I'll have to think some more.

-Eric

[Eric]

Here's something that worked for me. You need to include the sheet name in the range definition.

 RangeOBJ = ChartSheet.Range('F19,F22');
 AreaOBJ = RangeOBJ.Areas;
 RangeStr = sprintf('%s!%s,%s!%s', ChartSheet.Name, AreaOBJ .Item(1).Address, ChartSheet.Name, AreaOBJ .Item(2).Address);
 chart.SeriesCollection(1).XValues = RangeStr;

For my test code RangeStr was the string

 Sheet1!$A$1,Sheet1!$B$2

I could set XValues and Values to this string successfully.

-Eric