Re: Need help converting Excel color scale conditional formatting macro to perl

#!perl
use strict;
use Win32::OLE::Const 'Microsoft Excel';
Win32::OLE->Option(Warn => 3);

my $ex = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');
        
$ex->{Visible}=1;
my $wb = $ex->Workbooks->Open('c:\\temp\\ole.xlsx') ;
my $ws = $wb->sheets(1);

# select range
my $sel = $ws->Range("A1:A3");

# add conditional formatting
my $fc = $sel->{'FormatConditions'};
$fc->AddColorScale({ColorScaleType=>3});
$fc->{$fc->{'Count'}}->SetFirstPriority;

# set color scale criteria
my $csc = $fc->{1}->{'ColorScaleCriteria'};
$csc->{1}->{'Type'} = xlConditionValueLowestValue;
$csc->{1}->{'FormatColor'}->{'Color'}=8109667;
$csc->{1}->{'FormatColor'}->{'TintAndShade'}=0;

$csc->{2}->{'Type'} = xlConditionValuePercentile;
$csc->{2}->{'Value'} = 50;
$csc->{2}->{'FormatColor'}->{'Color'}=8711167;
$csc->{2}->{'FormatColor'}->{'TintAndShade'}=0;

$csc->{3}->{'Type'} = xlConditionValueHighestValue;
$csc->{3}->{'FormatColor'}->{'Color'}=7039480;
$csc->{3}->{'FormatColor'}->{'TintAndShade'}=0;
[download]

poj

Comment on Re: Need help converting Excel color scale conditional formatting macro to perl Download Code

Replies are listed 'Best First'.
Re^2: Need help converting Excel color scale conditional formatting macro to perl by WheelbarrowRacers (Initiate) on Feb 19, 2015 at 20:27 UTC
Thank you all and special thanks to Monk Poj. I printed the original VB and your Perl code and will study them side-by-side. I did review the Excel object model through Excel and on the MSDN site, but that was new to me also. So, having your Perl code will go a long, long way in helping understand how to translate between the two languages. I'm so very grateful to have the whole picture to get me on my journey.	[reply]
Re^2: Need help converting Excel color scale conditional formatting macro to perl by WheelbarrowRacers (Initiate) on Feb 20, 2015 at 00:51 UTC
Poj, I did have to make a couple of changes for the example you so wonderfully provided to work on my machine. For the lines setting the Type, like this one: `$csc->{1}->{'Type'} = xlConditionValueLowestValue;` I needed to put quotes around the value like this: `$csc->{1}->{'Type'} = 'xlConditionValueLowestValue';` When I used this line to start Excel: `my $excel = Win32::OLE->GetActiveObject('Excel.Application') \|\| Win32::OLE->new('Excel.Application', 'Quit');` I got this error: `Win32::OLE(0.1709) error 0x80020005: "Type mismatch" in PROPERTYPUT "Type" at OleExcelTipsTricks3.pl line 42.` [download] However, no other code changes were needed if I launched Excel this way: `my $excel = CreateObject OLE "Excel.Application";` No clue yet, why the difference in launching Excel mattered in my case, but just wanted to share that in case it matters for someone else.	[reply] [d/l] [select]