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

bsacheri
2019-12-18, 01:18 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

@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
2019-12-18, 02:16 PM
@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

Id
Type
Target

Within Workbook.xml.rels


name
sheetId
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

bsacheri
2020-01-03, 02:48 PM
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?

9536

9535

9537

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

9538