Excel Colour properties modified via LabVIEW

[dp_wave]

Hello,

I’ve asked a similar question earlier in the week to which smercurio_fc responded with a great code example. I’d like to change the background colour of a cell in an Excel worksheet if the number is below a certain threshold, as the data is being dumped into the Excel file real-time. I’m almost there, it seems as though I require a little code between the REFNUM of the Open/Create/Replace File VI and the REFNUM of the Property Node VI?

Find block diagram attached. (sorry, I don’t have the proper tools to save as a PNG file)

Thank you greatly in advance for your support!

[albertm]

Hi,

I tried conditional formatting in a spreadsheet and then imported it into an ActiveX control spreadsheet and the contional formatting didn't work.  Does anyone know how to get that to work?

Albert

[albertm]

Hi,

I attached an image of some code I use to set the backgound color.  The top VI calls the bottom one.  I remember not having any luck with just setting the RGB color but in this code I'm basically copying the color from one cell to another.  I just noticed that the color input on the bottom VI is a double and should probably be an int but it has been working this way for a long time.

Albert

[smercurio_fc]

Setting the formatting conditions is similar to the method I had shown for setting the background color. You need to access the Range's FormatConditions collection. The little code snippet below shows a simple example for setting the background color of a cell to yellow if the value is less than 5.

Message Edited by smercurio_fc on 09-04-2007 09:54 AM

[dp_wave]

Smercurio,

Thanks for your continued support. I've worked around the number threshold aspect. What I'm really trying to do is be efficient in the execution of the code. My present code makes use of the Open/Create/Replace File VI to enable the dumping of data. What I was really trying to do was interconnect that Open/Create/Replace File VI with the Property Node VI via the REFNUM of both VIs. If this can be done than this would permit me to change the background color of the cell whilst the data is being dumped into the Excel sheet real-time, and this background color would change according to the value of the number being dumped real-time.

Sincerely.

[smercurio_fc]

I was actually responding to Albert's comment about formatting conditions. With respect to your question, you can't connect the refnum from the Open/Create/Replace function to the Property Node since those are two different objects. If you're using the Open/Create/Replace file it looks like you're just dumping a text file that's formatted in spreadsheet-like form. Since you're accessing the Excel object to modify the cell properties you might as well just write to the Excel cells directly rather than use the write to text file method which you're currently using. Just a thought.

[drewsali]

Smercurio,

Your in-code comment listing the xl Constants was very helpful. You also mentioned checking the documentation for xl colors. Where do you find such documentation? I've searched all over microsoft's website to no avail.

Thanks,

Andrew

[smercurio_fc]

I got the values from the Microsoft Excel Visual Basic Reference help file. It doesn't get installed by default, so you need to go into a custom installation of Excel (I think it's called "advanced" in the installer) and select it. Or, you can download it (the Excel 2003 one) from here: http://www.microsoft.com/downloads/details.aspx?familyid=2204a62e-4877-4563-8e83-4848dda796e4&displa.... From the help file's Table of Contents go to Reference -> Enumerations.

[drewsali]

smercurio,

Can you attach your source code of the Example_BD.png to this thread? I'm trying to duplicate your work, but my outputs "Add" and "Item" on my invoke nodes "FormatConditions" are purple (variant type?) instead of green (reference type) as in your png, which makes it impossible for me to wire to the "FormatCondition" property node to make use of the "Interior" reference type.

Thanks,

Andrew

[smercurio_fc]

You hadn't indicated what version of LabVIEW, so attached is in 8.2.