reading color information from multiple xls cells

Hi,

i'm trying to read the Interior.ColorIndex value for multiple cells at once.

it works for 1 cell, but not a range of cells. Any idea how to make this work for more that 1 cell?

handles.ExFileName='r:\Magview\source\data\points.xlsx';
h = actxserver('Excel.Application');
workbook = h.Workbooks.Open(handles.ExFileName);
Sheets = h.ActiveWorkBook.Sheets;
Sheets.Item(1).Activate;

c  = h.Range('A1').Interior.ColorIndex          % returns 47
c2 = h.Range('A1:A500').Interior.ColorIndex  % returns a single NaN

invoke(h, 'Quit');
delete(h)

Answer 1

[Walter Roberson]

https://www.mathworks.com/matlabcentral/answers/1622010-how-do-i-select-several-rows-in-excel#answer_868620 shows how to create a union of ranges.

[Harmen]

thanks Walter,

As i understand it the "accepted answer" in your linked post centers around methods to construct the range string. This does not help me as

'A1:A500'

suits me fine, it just does not work.

the other answer in the post using the "Application.Union" command produces the same result, a single NaN:

handles.ExFileName='r:\Magview\source\data\points.xlsx';
h = actxserver('Excel.Application');
workbook = h.Workbooks.Open(handles.ExFileName);
Sheets = h.ActiveWorkBook.Sheets;
Sheets.Item(1).Activate;

c  = h.Range('A1').Interior.ColorIndex          % returns 47

uRnge=h.Application.Union(h.Range('A1'),h.Range('A500'))
c2 = uRnge.Interior.ColorIndex                  % returns single NaN

invoke(h, 'Quit');
delete(h)

[Image Analyst]

Since a range of cells could be shaded a range of different colors, what do you expect it to return? I suspect there is not API function to handle that and you'll have to process your cells one cell at a time to get each color in turn.

[Harmen]

Hi Image Analyst,

I expect it to return an array of index values.

Just before i try to read the color i read the values in the cells. That works fine with multi cell ranges.

I've been reading cells and colors one at a time but it is really slow. So i thought i would rewrite the code to read multiple cells at once. Been succesfull for the values, but not for colors.

handles.ExFileName='r:\Magview\source\data\points.xlsx';
h = actxserver('Excel.Application');
workbook = h.Workbooks.Open(handles.ExFileName);
Sheets = h.ActiveWorkBook.Sheets;
Sheets.Item(1).Activate;

h.Range('A1:AH500').Select;
DataRange = get(h,'Selection');
m = DataRange.Value;            %returns data in 500x34 cell

c  = h.Range('A1').Interior.ColorIndex          % returns 47

c3 = DataRange.Interior.ColorIndex              % returns single NaN

c2  = h.Range('A1:A500').Interior.ColorIndex   % returns single NaN

invoke(h, 'Quit');
delete(h)