See Using Win32::OLE and Excel - Tips and Tricks, especially Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)

According to my exprience the following approach is the easiest/fastest:

• create the Excel-Sheet by hand, especially all the formatting like (chart-)colors, sizes .... use dummy-data instead of the real values.
• use perl just to overwrite the dummy-data with the real values

Your code doesn't run. It's far easier for me, at least, to help you if I know what it is you are doing. The sort of problem I have is with your graph data. You have ony one numeric - 22 - and a pie chart with only one value isn't going to be very interesting. If there's other data that gets transformed somehow, it's missing and I can't guess at what it might be, while if you are trying to have the second slice as "TOM" or "AGE", you are destined for disappointment. Compare my code, which you can run from scratch.

I generally, like you, use constant values rather than named Excel constants because I can be consistent about them and VBA constants. I have deliberately deviated in this code both to show you how you might, if you choose, use named Excel constants and how I believe everyone should use "magic number" constants. You will see that when I use 255, I follow it with a comment naming the constant that I am using. Where you use, for example, 70, I have no means of knowing if that might be a typo.

Don't activate sheets unless you really need to. See Excel’s Select and Activate considered harmful.

I have changed only one colour, but I'm sure you can work out from what I have done how to change the rest. However, colours come in two forms in Excel (like everything else), 24 bit colour and the palette (see Re: handling excel conditional formating > color scale). If you want to use the palette, and there are good reasons for doing so if you have users who have set the palette to handle, say, colour blindness, you will need a slightly different incantation. If you need to know how to do this, please reply saying so.

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->PlotArea->{Width} = 350;
$cht->PlotArea->{Height} = 350;
$cht->SeriesCollection(1)->Points(1)->Interior->{Color} = 255; #vbRed 
+- NOT an Excel constant, but a VBA constant
$cht->Location ({Where=>xlLocationAsObject, Name=>$sht->Name});
[download]

Regards,

John Davies

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