Is it possible to detect color of sheet tab with actxserver?

I have got excel data files and would like to read data only from the spreadsheets having Green color. So in total i would be having data from only Green labelled spreadsheets from all the excel files.

Answer 1

[Guillaume]

I'd use Tab.Color instead of Tab.ColorIndex otherwise you'd also have to query the actual palette used by excel.

Completely untested code written on the fly, there may be typos / mistakes. Use MSDN to check the syntax of excel methods.

filepath = 'C:\somewhere\somefile.xlsx';

excel = actxserver('Excel.Application');
workbook = excel.Workbooks.Open(filepath);
numsheet = worbook.Worksheets.Count;
namergb = cell(numsheet, 2);
for sheetidx = 1:numsheet
   sheet = workbook.Worksheets.Item(sheetidx)
   namergb{sheetidx, 1} = sheet.Name;
   bgr = sheet.Tab.Color;
   namergb{sheetidx, 2} = uint8([mod(bgr, 256), floor(mod(bgr/256, 256)), floor(bgr/65536)]);  
end
workbook.Close;
excel.Quit;

cell2table(namergb, 'VariableNames', {'SheetName', 'RGB'})

[Ghenji]

Thanks a lot Guillaume. Never new this was possible.

But since am trying to look for particular sheet having color R = 0, G = 255, B = 1. And with current structure of table its difficult to read for this color.

How can this output table be made bit clear to look for this color? else it could also be possible to code it in such a way to look for this colored tab(R = 0, G = 255, B = 1) since I want to read data from this sheets only.

[Guillaume]

You don't have to use a table. This is just for pretty display. You can use the cell array or any other container you wish, (or none at all if you do the comparison in the loop).

Saying that it's trivial to search the table (or cell array, or ...):

t = cell2table(namergb, 'VariableNames', {'SheetName', 'RGB'})
wantedsheet = t.SheetName(all(t.RGB == uint8([0 255 1]), 2))

[Ghenji]

Got it. Works perfectly.

Answer 2

[Walter Roberson]

It does appear to be possible. https://analysistabs.com/excel-vba/change-color-sheet-tabs/ You would test each sheet in turn, accessing the sheets().Tab.ColorIndex property, and looking for value 4

[Ghenji]

I have got bit idea about actxserver but how do i pass above code?