Read the Color Index Property of Excel cell

[earth1111]

Report Generation Toolkit - Version 1.0.1
Windows XP - SP2
Microsoft Excel 2002 SP1

Am using the Report Generation toolkit for Microsoft Office. I set the value of a cell in the Excel file. The cell is condionally formatted to change backgournd color (Red/Green) depending on the value. I use the Range-->Interior-->Color Index to read the Color of the cell. It gives me a -4142. If I read a cell that has been colored without using Conditional Format, then it works fine.

[KileenC]

You'll want to make sure that you convert the hexadecimal representation of LabVIEW color to Excel color. The hexadecimal representation of color is different in LabVIEW than it is for Excel: the hexadecimal value 0xFF0000 represents red in LabVIEW and blue in Excel. Also, you'll want to convert the hexadecimal integer representation to a Variant datatype that Excel recognizes using the "To Variant.vi".

I went ahead and attached a sample program to this post that sets the background color of Excel cells. Hope this helps!

Kileen C.
NI

[earth1111]

Hi Kileen
Thanks for the response. Apparently, the color index of cells in Excel, when the cell is conditionally formatted cannot be retreived using the Color Index property. There is a metod called Condition Format and using this, we need to find out which Format Condition has been applied to the cell and the use the color index property to get the Color Index. Let me know if you need more info.
Thanks

[KileenC]

I apologize. I think I misinterpreted your question previously. I actually have not used the Condition Format method before and in using the Range.FormatConditions method, I'm seeing a couple errors on my side and not the same ones that you were seeing. Do you think you could post your code and I could take a look at it to debug?

Thanks. 🙂

Kileen C.

[id]

I had the same problem as the NI Engr...I cant seem to get this method of format condition to work.  I get an error with no explanation from the item property.  Has anyone been successful in resolving this?  I need to retrieve the colors of worksheet cells when I read them into Labview because they indicate system faults.

[RavensFan]

Do you get an error number?

I think your problem is that you are breaking your execution of the Excel workbook into two paths and wind up closing the Excel workbook in the upper path before you have a chance to get all the properties in the lower path.  Fix your error wires so that you don't execute the Close workbook until after the interior color index property node is read.  You should close all the references along that string of functions as well in the reverse order of the way they were created.  I'm not sure if the Close workbook and closing the reference right after it will automatically close all the other references you used in the lower string.

[id]

Error Code is now -2146827284. 

The VI reads the worksheet usedrange cell value2 values correct, but the actual usedrange>formatconditions>item>interiors>color returns an error.  I suspect that I may be something to do with the item selection, but I have tried 1-3 for it and it stil returns this error.  I am still perplexed. 

[smercurio_fc]

You are getting the error because you are trying to read the information from the conditional formats on the range, and there are none. The background color is being set simply by the cell's format, not through a conditional format.

See attached mod. I change the property being read to ColorIndex just for demonstration.

[id]

Thanks, This is good for returning the color of a single cell.  I am looking to return all colors of all used range cells.  Is this at all possible without iterating thru each cell? 

[smercurio_fc]

The Excel object model does not return that value as an array so you have to iterate for each cell in the range.