Accessing ConditionalFormats collection of Excel using VFP automation

[banswara]

Can anybody help me understand how to sucessfully access the ConditionalFormats collection from my VFP form?

I'm using the following code but I keep getting the error that "the parameter is incorrect".

lcLastCell = oExcel.ActiveCell.SpecialCells(xlLastCell).Address()
oExcel.Range("B9:" + lcLastCell).Select

oExcel.Selection.FormatConditions.Delete
oExcel.Selection.FormatConditions.Add(xlExpression,1,"=RIGHT($B9,5)='Total'&quot

 

[Mike Gagnon]

banswara

Can you specify which line is causing the problem.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[rgbean]

I believe that "xlLastCell" and " xlExpression" are VBA defined constants that VFP doesn't know about.

#DEFINE xlLastCell  11 
#DEFINE xlExpression  2

One place to get some of these constants are at

http://home.att.net/~john.harvey/vfpstuff.htm

- "Office97 constants - From Devcon98 - Bazian". This ZIP file includes a .PRG to "*-- Creates a .h file from an exported typelib".

Rick

 

[banswara]

Hi Mike, thanks for taking a look. As a matter of fact, I just sat down to write the answer to my own question.

The line in question is as follows.
oExcel.Selection.FormatConditions.Add(xlExpression,1,"=RIGHT($B9,5)='Total'&quot


One thing I was doing wrong was passing a second parameter to the method. It should be blank when you are passing an xlExpression. This was really just because I had tried so many things and wasn't making much headway, so tried that.

The real problem turned out to be the use of "double quotes" and 'single quotes'.

It turns out that Excel has to have "double quotes" in the passed expression/formula, so the corrected call to the method was as follows:

oExcel.Selection.FormatConditions.Add(xlExpression,,'=RIGHT($B9,5)="Total"')

Again, thanks for taking the time to look into this for me.