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

In reply to Re^3: Prepare charts using Win32::ole by davies
in thread Prepare charts using Win32::ole by soumyapanda

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.