PDA

View Full Version : Extract Cell Color with M



Fran McLoughlin
2016-11-28, 03:10 PM
Has anyone had success with extracting the color attribute of an Excel cell.
I have a user that uses cell color for information purposes, yet I need to be able to interpret the cell color as data information (along with the cell string/numeric value of course).
Have found nothing so far on this topic elsewhere.
Prefer not to have to pre-process the incoming data with VBA to extract the cell color information.
Any ideas...?
Thank you

Ken Puls
2016-11-28, 04:40 PM
Afaik, Power Query cannot be used to read the cell's metadata, only the current values in the cell. In fact, Power Query doesn't even read the data types. It makes an educated guess that usually works out close to right. (Think DateTime vs Date when the data is stored in a cell with no decimals.)

I'm fairly certain that the only method you have to deal with this is to pre-process your data to pull out the color info.

Comfy
2016-11-28, 05:27 PM
Hi Fran,

This isn't an extensive example but it may be viable with some work.

This uses code posted by KenR to read the xml parts of the Excel file (http://www.excelandpowerbi.com/?p=155)

Book2 is the sample file.

You will need to update the file path in "Power Query Cell Colours.xlsx"

/Comfy

Fran McLoughlin
2016-11-30, 02:27 AM
Hi Comfy;
Thank you indeed for an in-depth solution...
I hope to absorb this with some research and will post back.
Kind Regards again,
Fran