MATLAB Answers

Setting Chart Value Range for Excel2003/2007 - separated Cells

10 views (last 30 days)
Stef
Stef on 6 Jan 2014
Commented: 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');

Answers (1)

Eric
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
Eric
Eric on 6 Jan 2014
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!