Is it possible to detect color of sheet tab with actxserver?
3 views (last 30 days)
Show older comments
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.
0 Comments
Accepted Answer
Guillaume
on 9 Feb 2018
Edited: Guillaume
on 9 Feb 2018
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'})
3 Comments
Guillaume
on 9 Feb 2018
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))
More Answers (1)
Walter Roberson
on 8 Feb 2018
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
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!