FormatConditions Error

Topodo has asked for the wisdom of the Perl Monks concerning the following question:

Hello
I´m trying to create from Perl a conditional format in an Excel cell. I´m using the win32::ole All the program works perfect except the formatcondition lines. I have no errors, but the format is not written. Here is an example of the code:

my $Range = $Book->Worksheets('Name')->Range("H1");
$Range->FormatConditions ->Delete;
$Range->FormatConditions->Add("xlCellValue","xlNotEqual","=8");
$Range->FormatConditions(1)->Interior->{ColorIndex} = 3;
$Range->FormatConditions(1)->Font->{ColorIndex} = 2;
$Rango->FormatConditions->Add("xlExpression",,"=H1>G1");
$Rango->FormatConditions(2)->Interior->{ColorIndex} = xlNone;
$Rango->FormatConditions(2)->Font->{ColorIndex} = xlAutomatic;
[download]

The first instruction works, the Delete works fine and delete previous conditional formats. But i´m not able to create the new ones.

Anyone has an idea about where is my error? I´m completely lost about the solution.

Thnak you very much for your help.

20050524 Janitored by Corion: Added formatting

Comment on FormatConditions Error Download Code

Replies are listed 'Best First'.
Solution to Conditional Formatting in Excel by SilverSnitch (Initiate) on Jun 08, 2009 at 08:14 UTC
The OP probably doesn't need this anymore, but since I couldn't find anything on the topic myself, here's what I came up with. There are two major problems with the OP's code, plus a minor typo. Problem 1: `xlCellValue` and `xlExpression` have to be barewords, not strings. For that to work, we need `use Win32::OLE::Const 'Microsoft Excel';`, which was probably set correctly anyway. Problem 2: Multiple parameters for VBA (or OLE, I'm not certain which is correct to say here) functions can't just be listed, we have to use dictionaries with named parameters. The parameter names can be found in the VBA help that comes with the VB editor in MS Office, for example. The first, mandatory, parameter precedes that dictionary (or "can precede", I'm not certain here). So, the working code should look something like the following: use Win32::OLE::Const 'Microsoft Excel'; [...] my $Range = $Book->Worksheets('Name')->Range("H1"); $Range->FormatConditions ->Delete; $Range->FormatConditions->Add(xlTextString, { String => "finished", TextOperator => xlEndsWith }); $Range->FormatConditions(1)->Interior->{ColorIndex} = 3; $Range->FormatConditions(1)->Font->{ColorIndex} = 2; $Range->FormatConditions->Add(xlTextString, { String => "in progress", TextOperator => xlEndsWith }); $Range->FormatConditions(1)->Interior->{ColorIndex} = 3; $Range->FormatConditions(1)->Font->{ColorIndex} = 5; [download] This is not the OP's formatting condition, since I didn't want to look up the parameter names needed for that. It should be fairly simple to adapt this to your needs.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Solution to Conditional Formatting in Excel
by SilverSnitch (Initiate) on Jun 08, 2009 at 08:14 UTC

The OP probably doesn't need this anymore, but since I couldn't find anything on the topic myself, here's what I came up with. There are two major problems with the OP's code, plus a minor typo.

Problem 1: xlCellValue and xlExpression have to be barewords, not strings. For that to work, we need use Win32::OLE::Const 'Microsoft Excel';, which was probably set correctly anyway.

Problem 2: Multiple parameters for VBA (or OLE, I'm not certain which is correct to say here) functions can't just be listed, we have to use dictionaries with named parameters. The parameter names can be found in the VBA help that comes with the VB editor in MS Office, for example. The first, mandatory, parameter precedes that dictionary (or "can precede", I'm not certain here).

So, the working code should look something like the following:

use Win32::OLE::Const 'Microsoft Excel';
[...]
my $Range = $Book->Worksheets('Name')->Range("H1");
$Range->FormatConditions ->Delete;
$Range->FormatConditions->Add(xlTextString,
   { String => "finished",
     TextOperator => xlEndsWith });
$Range->FormatConditions(1)->Interior->{ColorIndex} = 3;
$Range->FormatConditions(1)->Font->{ColorIndex} = 2;
$Range->FormatConditions->Add(xlTextString,
   { String => "in progress",
     TextOperator => xlEndsWith });
$Range->FormatConditions(1)->Interior->{ColorIndex} = 3;
$Range->FormatConditions(1)->Font->{ColorIndex} = 5;
[download]

This is not the OP's formatting condition, since I didn't want to look up the parameter names needed for that. It should be fairly simple to adapt this to your needs.

[reply]
[d/l]
[select]