Error while choosing range for Excel chart using m

2019-06-11 22:53发布

问题:

I have an Excel file with to sheets. First about 30 columns and 20.000 rows. 2nd about 10 columns and 2 rows. Each with a header.

No, there are less than 66.000 rows in the file I work with and I use .xlsx files, so it's not that problem. this might be related but didn't help for me

The goals for the script were:

  • Automaticly creating a Chart in an already Existing .xlsx file using Matlab
  • Easy way to choose Range, X- and Y-Values (by sheet and column) freely
  • applicable to all sizes on .xlsx files. (up to 20.000 rows and 50 columns and several sheets)

In this Code range is set to the following: X -> column B , Y -> column H - P , the sheet is 'sheet1' called 'Tabelle1'

%%%%% general Code to insert a Chart in Excel using Matlab %%%%%
%% start Excel and open Workbook
excel = actxserver('Excel.Application');
wb = excel.Workbooks.Open('C:\...\Test.xlsx');
%% makes the created sheet visible
excel.Visible = true;
%% add 1. Chart
chart1 = wb.Charts.Add;
%% set source data 
chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P')); % 'Tabelle1' is the german equal to sheet1, my excel is german
%% Name chart sheet
chart1.Name = '1. TestChart';
%% Set chart title,  see https://msdn.microsoft.com/en-us/library/office/ff196832.aspx
chart1.HasTitle = true;
chart1.ChartTitle.Text = 'Test Title';
%% Set chart types,  see https://msdn.microsoft.com/en-us/library/office/ff837417.aspx
chart1.ChartType = 'xlXYScatterSmoothNoMarkers';
%% Set chart legend, see https://msdn.microsoft.com/en-us/library/office/ff821884.aspx
chart1.HasLegend = true;
%% Set Axes Titles
chart1.Axes(1).HasTitle = true;
chart1.Axes(1).AxisTitle.Text = 'Time [s]'; % XAxes
chart1.Axes(2).HasTitle = true;
chart1.Axes(2).AxisTitle.Text = 'Temperature[°C]'; %YAxes
%% add 2nd chart
chart2 = wb.Charts.Add([], chart1); %place after chart1
chart2.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $Q:$Q'));
% ... same procedure as above

%% use to quit all open Excel processes
% excel.Quit;

this Error occurs:

Error using Interface.000208D8_0000_0000_C000_000000000046/Range

Error: Object returned error code: 0x800A03EC

Error in CodeTestmy (line 13) chart1.SetSourceData(wb.Worksheets.Item('Tabelle1').Range('$B:$B, $H:$P'));

I'm pretty sure, that this code works just fine and I can't explain the error.


Can you please help me to find out, were the error comes from to finaly solve the problem.

Here are the download Links (Google Drive) to the .m script and .xlsx file used

Thanks

Kilian Weber