MatLab: Iterating dynamically through Cells with Excel COM Add-In

Question

First of all thanks a lot, for very good answers that I have found here in other topics in the past.

Now to a new challenge:

I am currently working with the COM Add-In in Matlab, i.e. I am reading a Excel Workbook and extracting the Color Property:

excelapp = actxserver('Excel.Application');   %connect to excel
workbook = excelapp.Workbooks.Open('Solutions.xls');        
worksheet = workbook.Sheets.Item(1);                        
ColorValue_Solutions=worksheet.Range('N2').Interior.Color;

Now, I want to do this for cells in the Range A1 up to J222, for which I would like to dynmaically loop through the Range property, letting the programm read each cell individually and then taking out the color proerty. For example:

Columns = {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'};

for j = 1:length(Columns)          
   for i = 1:222

worksheet.(char( strcat('Range(''',Columns(j), num2str(i), ''')') )).Interior.Color

   end
end

This, however, results in an error:

Undefined function or variable 'Range('A1')'.

I guess the problem is in the combination of interpreting a string with an included string, i.e. Range('A1').

Any help is much appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:18

Some time ago I asked a similar question. Check it out, maybe you will find it helpful.

The following code should do what you want:

You can see how the Interior.Color property is extracted from every cell by first getting a Cell object, then accessing the Interior object of that Cell and finally getting the Color property of that Interior object. The Color property is a color integer defined by Microsoft (you can learn more here and here), I store that value in a matrix (M). In order to repeat this process through the specified range of cells, I use a nested loop, like you were doing already. Once the process is finished, I display the contents of M.

excelapp = actxserver('Excel.Application');    % Start Excel as ActiveX server.
workbook = excelapp.Workbooks.Open('Solutions.xls');    % Open Excel workbook.
worksheet = workbook.Sheets.Item(1);           % Get the sheet object (sheet #1).

ncols = 10;             % From column A to J.
nrows = 222;            % From row 1 to 222.
M(nrows, ncols) = 0;    % Preallocate matrix M.

for col = 1:ncols                              % Loop through every column.
    for row = 1:nrows                          % Loop through every row.
        cell = get(worksheet, 'Cells', row, col);   % Get the cell object.
        interior = cell.Interior;              % Get the interior object.
        colorint = get(interior, 'Color');     % Get the color integer property.
        M(row, col) = colorint;                % Store color integer in matrix M.
    end
end

disp(M);    % Display matrix M with the color integer information.

Remember to close the connection when you are finished. You can learn how to do it here and here.

MatLab: Iterating dynamically through Cells with Excel COM Add-In

1 Answers1