Opening the activeX control "Excel.FormatCondition" class

[drewsali]

I am using activeX controls for excel including the classes Excel._Application, Excel._Workbook, and Excel._Worksheet. I used an open automation refnum to open the application class, then I used property nodes to get to the workbook and worksheet classes. Now I want use the Excel.FormatCondition class. I couldn't get to the class with nodes and methods, so I'm using an open automation refnum as before, but I'm getting "Error 3005 occurred at Automation Open".

I found another post with advice to register the controls, but I read that the excel activeX controls don't need to be registered--they just work in windows.

If I do need to register something, I dont' know what. I'm using LabVIEW 8.6, and Office 2007.

Andrew

[smercurio_fc]

I don't believe that you can directly create a FormatCondition that way. What do you mean by "I couldn't get to the class with nodes and methods"?

[Michael_Munroe]

You are most of the way there.  Before you can format anything, you need to specify the range to which it applies.  From the worksheet use the Invoke node Range.  Specify the range you need, and then you can cascade the FormatCondition to the range output.  For convenience, you can also insert the EntireColumn, EntireRow, UsedRange or CurrentRegion.

You don't mention how you got from Workbook to Worksheet, but you need the Invoke node Item to get from property node Worksheets followed by a Variant to Data with a worksheet constant at the top.

[drewsali]

Here's a picture (scaled-down excerpt of my project) to show how I'm opening the application class and using invoke nodes and property nodes to get to the workbook and worksheet classes. I used a cluster to group the classes that I use later on, so you'll notice that I'm not closing the classes in this exceprt.

I went through all the drop-down menu's in the invoke nodes and property nodes and I couldn't find a way to get to "FormatCondition" so I figured I'd have to use an automation refnum, and select the "Excel.FormatCondition" class, and another automation open (which is what I tried showing earlier). I understand I have to specify a range, but I'm not quite there yet. (Sorry if I mis-led you, Michael, with my first attachment.)

Does that clear up my question?

Andrew

[smercurio_fc]

Where's your Range? FormatConditions applies to a Range, not a Worksheet. Something like this:

[drewsali]

I need to use the "Excel.FormatCondition" class for the "Interior" method, not "Excel.FormatConditions". Here is what I have for range. I know my code is not yet complete, but I can't even open (see probe 5 in attachment) without error.

[smercurio_fc · Accepted solution]

That's not right. As I indicated in the example in the other thread, you have to pull out the format condition from the collection after you've added it by using the Item method. In Excel 2003 that returns a FormatCondition type directly. In Excel 2007 it returns a variant. Thus, you would need to use Variant to Data in between the FormatConditions.Item() method and the FormatCondition.Interior property, as I showed in reply #14, to convert the variant to a FormatCondition type.

[drewsali]

Thanks for your expertise and patience 🙂