Setting Chart Value Range for Excel2003/2007 - separated Cells
4 次查看(过去 30 天)
显示 更早的评论
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 个评论
回答(1 个)
Eric
2014-1-6
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 个评论
Eric
2014-1-6
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
2014-1-6
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
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!