Extract Cell Color with M

Fran McLoughlin

New member
Joined
Nov 26, 2016
Messages
4
Reaction score
1
Points
0
Excel Version(s)
MS Office 365
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
 
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.
 
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
 

Attachments

  • Power Query Cell Colours.xlsx
    29.5 KB · Views: 644
  • Book2.xlsx
    8.4 KB · Views: 439
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
 
Read Excel cell color with Power Query

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

@Comfy

This is an excellent example. Thank you for sharing. Three years later you've made an positive impact on me. I was able to put this to use very quickly.

In my case I had a large spreadsheet where multiple users had highlighted cells that they had modified. Each change needed to be pushed into a database. I used your example to build a table of all of the highlighted cells. I then used the INDIRECT() function to extract the cell data and build a SQL Update statement for each cell. With a few more functions I was also able to grab the field name from the column heading, and the primary key of each row from another column. I The result was creating over 3,800 Update statements for the colored cells. It was a successful moment. Kudos to you.

An improvement I'd like to see is the ability to know the worksheet name for each cell instead of the behind the scenes name "xl/worksheets/sheet1.xml". I only had to process one sheet so I was able to hard-code the display name into my formulas.

Have you written up more about it or made an improved version? I'm posting a Stack Overflow answer pointing to your forum post but would like to know if there is a more formal write-up I can link to.

https://stackoverflow.com/questions/39347073/read-excel-cell-colour-into-power-bi


Ben
 
@Comfy

This is an excellent example. Thank you for sharing. Three years later you've made an positive impact on me. I was able to put this to use very quickly.

In my case I had a large spreadsheet where multiple users had highlighted cells that they had modified. Each change needed to be pushed into a database. I used your example to build a table of all of the highlighted cells. I then used the INDIRECT() function to extract the cell data and build a SQL Update statement for each cell. With a few more functions I was also able to grab the field name from the column heading, and the primary key of each row from another column. I The result was creating over 3,800 Update statements for the colored cells. It was a successful moment. Kudos to you.

An improvement I'd like to see is the ability to know the worksheet name for each cell instead of the behind the scenes name "xl/worksheets/sheet1.xml". I only had to process one sheet so I was able to hard-code the display name into my formulas.

Have you written up more about it or made an improved version? I'm posting a Stack Overflow answer pointing to your forum post but would like to know if there is a more formal write-up I can link to.

https://stackoverflow.com/questions/39347073/read-excel-cell-colour-into-power-bi


Ben

Hi Ben,

I'm happy it helped you find a solution and I've attached some revised files that should help you to identify sheet names, see output on Sheet 7. This does have a pretty big caveat as I have done no testing.

The Query relies on data within the XML files being in a specific order, that order being:

Within Workbook.xml
  1. Id
  2. Type
  3. Target
Within Workbook.xml.rels

  1. name
  2. sheetId
  3. id

If this isn't consistent then the pivoting of data will break.

There's no formal write up. But do post a link if you decide to create one :)

/Comfy
 

Attachments

  • Book2.xlsx
    8.8 KB · Views: 84
  • Power Query Cell Colours.xlsx
    36.8 KB · Views: 123
I'm seeing an odd issue with your query that retrieves the worksheet name. After changing the local paths in your queries, I can refresh and all of the queries work. But then if I open the Book2.xlsx, save it with Excel 2016 (O365), and close it, the Power Query refresh fails in query WorkbookXml. It seems like the xml is in a different format after the file is saved. Is there another way to associate the displayed sheet name with its underlying sheet name?

2020-01-03_9-02-38.png

2020-01-03_9-06-45.png

2020-01-03_9-20-34.png

I have attached a copy of your Book2.xlsx file that I saved on my computer.

View attachment Book2_2016.xlsx
 
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
Hi Comfy, the link is no longer accessible, could you please revert if youre still active? thanks heaps!!
 
Back
Top