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

I already have a xlsx file which contains a pie chart with 3 legends. The requirement is that I have to change the colors of those 3 legends.

I can create another excel file which contains a macro & can run the macro for the xlsx where I want to change the pie chart color . This is working fine.

But, now the requirent is that there should should not be any extra excel file in addition to the xlsx where the pie chart is. So, I have to change the colors from my perl script only.

So, I have written the following code .

 
use Win32::OLE::Variant ;
use Win32::OLE qw(in with) ;
use Win32::OLE::Const 'Microsoft Excel' ;
 
my $file = "c:/sample.xlsx" ;
 
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')||Win32::O
+LE->new('Excel.Application','Quit');
my $Book = $Excel->Workbooks->Open( "$file") ;
my $Sheet = $Book->Worksheets(1) ;
my $chart = $Sheet->ChartObjects("Results");
$chart->Activate() ;
$chart->SeriesCollection(1)->Points(1)->Interior->{ColorIndex} = 4 ;
$chart->SeriesCollection(1)->Points(2)->Interior->{ColorIndex} = 3 ;
$chart->SeriesCollection(1)->Points(3)->Interior->{ColorIndex} = 6 ;
$Book->Save ;
$Book->Close ;

[download]

But it's not working, with error like Win32::OLE(0.1709) error 0x80020003: "Member not found" in METHOD/PROPERTYGET "" at c:\sample.xlsx

Can anybody help me please ?

Replies are listed 'Best First'.
Re: pie chart color -- Win32::OLE
by davies (Monsignor) on Oct 08, 2012 at 14:47 UTC

    I can't reproduce your error, which I would expect to include a line number anyway. But the following code, based on my replies to Prepare charts using Win32::ole, works on Excel 2002. VBA hasn't changed in ways that should make it fail on later versions. If you comment out the last three lines, you should see different colours in the charts.

    use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);
$sht->Range("A1")->{Value} = "'PMD";
$sht->Range("A2")->{Value} = "'SOPW";
$sht->Range("A3")->{Value} = "'Meds";
$sht->Range("A4")->{Value} = "'CUFP";
$sht->Range("B1")->{Value} = "3";
$sht->Range("B2")->{Value} = "40";
$sht->Range("B3")->{Value} = "2";
$sht->Range("B4")->{Value} = "1";
my $cht = $wb->Charts->Add;
$cht->{ChartType} = xlPie;
$cht->SetSourceData ({Source=>$sht->Range("A1:B4"), PlotBy=>xlColumns}
+);

$cht->SeriesCollection(1)->Points(1)->Interior->{Color} = 255;      #v
+bRed
$cht->SeriesCollection(1)->Points(2)->Interior->{Color} = 16711680; #v
+bBlue
$cht->SeriesCollection(1)->Points(3)->Interior->{Color} = 65280;    #v
+bGreen

    [download]

    Regards,

    John Davies

[reply]
[d/l]
Re: pie chart color -- Win32::OLE
by Anonymous Monk on Oct 08, 2012 at 11:28 UTC
[reply]
Re: pie chart color -- Win32::OLE
by Anonymous Monk on Apr 10, 2013 at 03:49 UTC
    Hi Rajesh, I have similar error like you were having. Can you please share the solution for this ? Thank you,
[reply]

Back to Seekers of Perl Wisdom