Results 1 to 7 of 7

Thread: Extract Cell Color with M

  1. #1
    Neophyte Fran McLoughlin's Avatar
    Join Date
    Nov 2016
    Posts
    4
    Articles
    0
    Excel Version
    MS Office 365

    Extract Cell Color with M



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,355
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019
    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
    Attached Files Attached Files

  4. #4
    Neophyte Fran McLoughlin's Avatar
    Join Date
    Nov 2016
    Posts
    4
    Articles
    0
    Excel Version
    MS Office 365
    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

  5. #5
    Neophyte bsacheri's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Read Excel cell color with Power Query

    Quote Originally Posted by Comfy View Post
    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/...-into-power-bi


    Ben

  6. #6
    Acolyte Comfy's Avatar
    Join Date
    Oct 2016
    Posts
    61
    Articles
    0
    Excel Version
    2019

    Smile

    Quote Originally Posted by bsacheri View Post
    @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/...-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
    Attached Files Attached Files

  7. #7
    Neophyte bsacheri's Avatar
    Join Date
    Dec 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    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?

    Click image for larger version. 

Name:	2020-01-03_9-02-38.png 
Views:	26 
Size:	68.1 KB 
ID:	9536

    Click image for larger version. 

Name:	2020-01-03_9-06-45.png 
Views:	19 
Size:	97.3 KB 
ID:	9535

    Click image for larger version. 

Name:	2020-01-03_9-20-34.png 
Views:	17 
Size:	50.4 KB 
ID:	9537

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

    Book2_2016.xlsx

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •