MATLAB Answers

Stef
0

Setting Chart Value Range for Excel2003/2007 - separated Cells

Asked by Stef
on 6 Jan 2014
Latest activity Commented on by Eric
on 6 Jan 2014
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');

  0 Comments

Sign in to comment.

1 Answer

Answer by Eric
on 6 Jan 2014

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

  2 Comments

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
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

Sign in to comment.