FormatConditions for Excel in CVI

[rfttrfgrzfdg]

Hey everone!

I'm currently writing a programm in CVI which reads some data from ASCII files and after some calculations prints them to an Excel spreadsheet. I'm doing this with the Excel Report instrument and it works so far.

Now i want the cells to be formatted, depending on other cells values. Let's say i have cells with the following values in the spreadsheet:

max: 5

min: 3

value: 2

So the condition of the cell, containing the 2 should be: if(bigger than max, or smaller than min) -> red, else green

I found a function in the Microsoft Excel 9.0 Library, named Excel_FormatConditionsAdd but I don't know how to use it.

The function panel is attached.

And there seems to be no help file.

Could anyone give me a hint on how to use this function? I would also appreciate links to useful resources..

Hope there's someone out there...

cheers

Message Edited by rfttrfgrzfdg on 05-13-2010 08:23 AM

[e2m0i0l7]

Hello,

Please take a look at Format Conditions Object :

http://msdn.microsoft.com/en-us/library/bb223834.aspx

You can use the Add method to create a new conditional format with the following parametters:

http://msdn.microsoft.com/en-us/library/bb211780.aspx

Hope you will find the site I have sent you as the base of future searches  in regard to Microsoft Libraries.

Regards,

Emil

[rfttrfgrzfdg]

Hey, thanks for the links.

I'm slowly beginning to figure out how it could work. But there's one thing, I can not set the Properties of some objects as I would like to do. I think I'm searching at the wrong side in some way.

What I have so far:

//this will give me a Range object 

CA_VariantSetCString (&MyCellRangeV, "A1:C5");
Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);

//this will produce a FormatConditions object

Excel_FormatConditionsAdd (ExcelFMs, NULL, ExcelConst_xlCellValue, CA_VariantInt(ExcelConst_xlGreater), MyVariant, CA_DEFAULT_VAL, &ExcelFM);

// and now I would like to set the Property "FormatConditions" of the Range object to my FormatConditions object from above.

Excel_SetProperty(ExcelRangeHandle,NULL,Excel_RangeFormatConditions,CAVT_OBJHANDLE,ExcelFMs);

The problem with the code above is, that the property FormatConditions of Range is not writable. So how can I set this property?

Cheers,

Michael

Message Edited by rfttrfgrzfdg on 05-14-2010 08:31 AM

[rfttrfgrzfdg · Accepted solution]

Wohoo...

I've finally found the solution, I really had to do it the other way round:

//first get the FormatConditions property of the desired range
Excel_GetProperty (ExcelRangeHandle, NULL, Excel_RangeFormatConditions,CAVT_OBJHANDLE, &ExcelFMs);

//THEN add the conditions
Excel_FormatConditionsAdd (ExcelFMs, NULL, ExcelConst_xlCellValue, CA_VariantInt(ExcelConst_xlGreater), MyVariant, CA_DEFAULT_VAL, &ExcelFM);

//Now get the FormatConditionInterior property of the FormatCondition
Excel_GetProperty(ExcelFM,NULL,Excel_FormatConditionInterior,CAVT_OBJHANDLE,&interiorHandle);
//and finally set the InteriorColorIndex property of this thing to some number (3 is red)
Excel_SetProperty(interiorHandle,NULL,Excel_InteriorColorIndex,CAVT_LONG,3);

after this terrible pain in the ass i really deserve a beer now.

thanks again, first i thought this thing would never work.