How to un-select cells in Excel using Matlab? Using Matlab to create a chart adds graph with value of makred area

5 views (last 30 days)
Kilian Weber
Kilian Weber on 20 Jun 2017
Edited: Kilian Weber on 21 Jun 2017
Excel 2016 remembers the cells you "marked" (selected) last before closing the file and the same asrea will be marked at the next opening of the file.
The only way to not mark an area is an untouched file created by for example Matlab.
How to unmark all areas in Excel using Matlab?
In my program (code below) I access an existing Excel File using Matlab. The code creates a new chart with set range.
Unfortunately there are always created more graphs in the chart and selecet in range. To be more percise thge area slected in an matlab file will be added as a new graph. (when it's empty an empty graph will be added anyway)
here you can see the marked area:
which then after running the code will be added as a graph with exactly the range of the marked are:
original program with code and info for understanding the project:
Original goals:
- 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)
General problems:
- untouched files work
- at every other file additional graphs are added always
Code:
% Start Excel and open workbook
Excel = actxserver('Excel.Application');
WB = Excel.Workbooks.Open('C:\...\test.xlsx');
%the workbook
Excel.visible = 1;
% Add chart
Chart = invoke(WB.Charts,'Add');
% Get Sheet object
SheetObj = Excel.Worksheets.get('Item', 'Tabelle1'); % 'Tabelle1' is german Ecel equation for 'sheet1'
%%FORMATTING %%
% Name chart sheet
Chart.Name = 'TestChart';
% Set chart type
Chart.ChartType = 'xlXYScatterSmoothNoMarkers';
% Set chart title, see https://msdn.microsoft.com/en-us/library/office/ff196832.aspx
Chart.HasTitle = true;
Chart.ChartTitle.Text = 'Test Title';
% Set chart legend, see https://msdn.microsoft.com/en-us/library/office/ff821884.aspx
Chart.HasLegend = true;
%%Set source data range of chart, do X and Y data for each series
columns = 3:7; % set columns 3 to 7 as Y-Values
colnames = {'xdata', 'my series 1', 'my series 2', 'my series 3','my series 4','my series 5','my series 6', 'my series 7', 'my series 8','my series 9'};
for col = columns
%%Get Excel column *letter* from column *number*
colchar = strrep([char(96+floor((col-1)/26)) char(97+rem(col-1,26))],char(96),'');
% Last row of data, see https://msdn.microsoft.com/en-us/library/office/ff839539.aspx
%%Data must be contiguous (no gaps / blank cells)
lastrow = num2str(SheetObj.Range([colchar, '2']).End('xlDown').Row);
%%Y data, creating range strings by concatenation of column character and row number
Srs = Chart.SeriesCollection.Add(SheetObj.Range([colchar, '2:', colchar, lastrow]));
%%X data, same approach is used for last row, but only column = 1 = "A"
Srs.XValues = SheetObj.Range(['A2:A', lastrow]); % set column A as X-Value
%%Set column name
Srs.Name = colnames{col};
end
What the code does:
When it works, a new chart is created, that has column A as X-Value and column 3 to 7 as Y-Values with the `colnames` of the column.
When it doesn't work, it does the same things as it does, when it works but adds more graphs to chart. Tp be exact it adds every column of the file as Y-Value with no X-Value (so 1,2,3,4...), using the first row as their names.
Summary of the question
- is there a way to unmark, unselect areas in Excel using Matlab code?
- do you know where the problem comes from
- any helpful solutions

Answers (0)

Community Treasure Hunt

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

Start Hunting!