How to un-select cells in Excel? Using Matlab to c

问题:

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

回答1:

I found myself a solution. Or more of a way around this problem.

So again: The problem is, that the preselected cells will automacly show in your chart even before you selected your own data and X- and Y-Values.

Solution:

clear the chart between the steps of automatic graph creation and selection of your own data

to do so you just nee to place this one-liner after SheetObj = Excel.Worksheets.get('Item', 'Tabelle1');:

%% Clear content 
Chart.ChartArea.ClearContents;

so your chart will be empty before you select the data to fill it.

This worked for me... Hope it helps you, too.