Using conditional formatting in excel through LabVIEW activeX

[drewsali]

I'm working with excel 2007 and want to automate some formatting through ActiveX controls in LabVIEW 8.6. I've been able to duplicate basic formatting options like autosize and justify columns, text font, and freeze panes, by reverse-engineering excel macros. The macro for condional formatting (pasted below) has proven to be a little more difficult:

 Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B10:P10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$P$10<1.2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

This macro checks the value of the cell of interest (in this case P10), checks it against the condition (? cell val < 1.2), and colors a range of cells in it's row if the condition is true.

Through LabVIEW, I've managed to insert the formula into the cell of interest, but I've not been able to duplicate the macro or find an equivalent activeX method to check the criteria of the condition in order to format accordingly. I've attached some sample VI's from my project if anyone cares to see where I've left off. In this post I'm attaching, "Top Level Excel", and two subVI's, "Set Cell Value", and "Excel Conditional Formatting". Look in the subsequent post for additional needed subVIs.

Thanks,

Andrew

[drewsali]

Here are the additional subVIs needed to run the above "Top Level Excel" program.

Andrew

[drewsali]

Similar topic:

http://forums.ni.com/ni/board/message?board.id=170&message.id=269049&query.id=2180050#M269049 

I just found this thread where Smercurio was able to pull it off, but I can't duplicate what he's done either. The output "Item" on my invoke node"FormatConditions" is purple (variant type?) instead of green (reference type) as in the thread example. Still working on it...

Andrew

[SMeserve]

Hi drew,

It appears smercurio has replied to your message.  You may have better luck asking him.  Perhaps he has the code he can share with you.

Did you hover over the wire with context help on (CTRL+H)?  This will tell you what type the wire is.  Property and invoke nodes can be tricky so I would double check your wiring to make sure you didn't make an error somewhere.