Hi John,

I tried to run the code given by you. I am able to change the colour of different sectors in the chart. But am unable to change the size of the chart(Area). While running your code a bigger size chart flashes for 1 sec and it becomes normal size. Can you please suggest me how can i increase the chart area.

This one's strange, as it seems in part to be machine dependent. It works on the machine I was using yesterday, but on a different machine it fails as you describe. However, there's certainly something I didn't expect going on in the guts of the chart engine. Please try the following code:

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->Location ({Where=>xlLocationAsObject, Name=>$sht->Name});
$cht = $sht->ChartObjects(1)->Chart;
$cht->{ChartType} = xlPie;
$cht->SetSourceData ({Source=>$sht->Range("A1:B4"), PlotBy=>xlColumns}
+);
$cht->{AutoScaling} = 0;
$cht->PlotArea->{Width} = 190;
$cht->PlotArea->{Height} = 191;
$cht->SeriesCollection(1)->Points(1)->Interior->{Color} = 255; #vbRed 
+- NOT an Excel constant, but a VBA constant
[download]

As you will see, it's very close to the code I wrote yesterday. The main difference is that the Location has been moved much earlier in the code. My guess as to what's happening is that the Location command actually defines a new chart and that not all the parameters are copied, which is why you saw what you wanted followed by an autosized graph. This might make sense if the size exceeds the area that can be drawn in the new position. Again, this might make sense of the machine dependency if what can be done on one display can't be done on another AND the dimensions on a "graph sheet" do not work in the same way as those on an embedded object, which would not be unlike Bill Gates. Certainly, the reassignment to $cht is essential, even in VBA.

You may also notice that the width and height are not the same. This isn't some personal whim of my own, but the values I get from this machine's copy of Excel when resizing manually. I therefore suggest that you play around on your own machine to find out what you get when you do something manually that looks right. Using the macro recorder is probably the easiest way, but the immediate pane of the VBA IDE can also be used.

Regards,

John Davies

Thanks a lot. The code really worked me. Am able to change the size of pie chart. But when i tried to use the same code for increasing size of a bar graph. I got some error





Below is the code i was trying to run for bar graphs





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

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 0;
my $wb = $xl->Workbooks->Add;

$bar_data = [
            [ "Sam",22],
            [ "Tom",22],
            [ "Teddy",22],
            [ "Tommy",22],
            [ "Simon",22],
            
            
            ];
                                
    $sheet    = $wb -> Worksheets(1);
    $sheet -> Activate;
    
    $range_of_data = $wb->ActiveSheet->Range("A1:B5");

$chart = $wb->Charts->Add;

$chart->SetSourceData($range_of_data, 2);
$chart->{ChartType} = 96;
$chart->Location(2, "Sheet2");
$chart->{AutoScaling} = 0;
$chart->SeriesCollection(1)->Points(1)->Interior->{Color} = 255; #vbRe
+d 
$chart->SeriesCollection(1)->Points(2)->Interior->{Color} = 255; #vbRe
+d 
$chart->PlotArea->{Width} = 1000;
$chart->PlotArea->{Height} = 1000;

$wb->SaveAs("D:\\Output.xlsx");

$xl-> {DisplayAlerts} = 0; 
$xl->Quit;
$wb = 0;
$xl = 0;
[download]





Below is the error am getting




Can't call method "Points" on an undefined value
[download]





Please help me to increase the size of the bar graph