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

Hi Perl Monks,

I am new to perl and I am trying use perl to automate the process of executing a program, process the the result and present the data in Excel. I ve followed the samples in your site and some other resources, but the win32::ole function didnt work very well with excel in my case:

I can create a workbook, insert data and create a new chart with perl in excel, however, I cannot change the chart type, set the tile, axis title or run a macro.

I use the following code to 

#create chart and title:
    my $Chart = $ex->Charts->Add;

    $Chart->{HasTitle} = 1;
    my($title)="Analysis Result ".$fileIndex;
    $Chart->ChartTitle->{Text} = $title;


#define cahrt type:
    $Chart->{ChartType} = "xlLineMarkers";
    $Chart->Location({Where => "xlLocationAsNewSheet"});


#set axis title:
    my $Xaxes = $Chart->Axes("xlCategory", "xlPrimary");
    $Xaxes->{HasTitle} = 1;
    $Xaxes->{AxisTitle}->{Characters}->{Text} =  "X Title";


    my $Yaxes = $Chart->Axes("xlValue", "xlPrimary");
    $Yaxes->{HasTitle} = 1;
    $Yaxes->{AxisTitle}->{Characters}->{Text} =  "Y Title";

[download]

I ve put all this in the perl program but it has no effect, the chart always diplay as bar charts, and I can use perl to delete and data set in it.

Also If I insert the data first and create chart, as I can remove the data set, it alwasy give me the wrong data set in chart.

what's wrong with the code, I really dont know, are there any setting that I need to set on excel to use this function?

It is a kinda urgent one :(, I would appriciate if anyone could give me some advice.

thanks!

Replies are listed 'Best First'.
Re: Using win32::ole on Excel
by VSarkiss (Monsignor) on Oct 27, 2002 at 23:44 UTC

    Win32::OLE is a strange beast, as you've found. I'd recommend two writeups, in case you haven't seen them yet. The first is cacharbe's tutorial called Using Win32::OLE and Excel - Tips and Tricks, and the other is the writeups on http://www.roth.net. Sounds like you may have found them already, but if not, take a look for some invaluable info.

    One thing I've noticed is that I sometimes have to explicitly call the Value method when setting values. That is, instead of $Chart->{ChartType} = "xlLineMarkers";try using:$Chart->{ChartType}{Value} = "xlLineMarkers";I'm not sure if that's the problem -- in other words, this is a shot in the dark -- but it's worked for me under random circumstances in the past. As far as I've been able to tell, there's no rhyme or reason about when it's needed. (There probably is, I just haven't been able to figure it out....)

    HTH

    Update
    Fixed typo in link to cacharbe's tutorial.

[reply]
[d/l]
[select]

      hi,

      Thanks for the reply, I have try it but it didnt work for my case.

      There are some typing errors in the original post:

      I ve put all this in the perl program but it has no effect, the chart always diplay as bar charts, and I CANT use perl to delete and data set in it.

      Also If I insert the data first and create chart, as I CANT remove the data set, it alwasy give me the wrong data set in chart.

      So bassicaly I cant do anything with excel other than insert data and create chart.

      Thanks

[reply]
Re: Using win32::ole on Excel
by cacharbe (Curate) on Oct 28, 2002 at 18:07 UTC

    All in all, you are looking at an order of operations issue. Add the data and types to the Chart FIRST, and then add your title, and it should all work out just fine

    Make sure to use the Excel Constants WITHOUT quotes. They are constants, after all.

    use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);

$Win32::OLE::Warn = 3; # Die on Errors.

my $vttrue =  Variant(VT_BOOL, 1);
my $vtfalse =  Variant(VT_BOOL, 0);

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit'); 
   $Excel->{DisplayAlerts}=0;   

$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);

my $Range = $Sheet->Range("A2:C7");
$Range->{Value} =
    [['TIME', 'QUANTITY', 'PRICE'],
     ['10:00', 100, 15],
     ['11:00', 150, 10],
     ['12:00', 80, 10],
     ['13:00', 100, 20],
     ['14:00', 120, 15]];
     
my $Chart = $Excel->Charts->Add or die;
   $Chart->{ChartType} =xlLine ;

   my $series = 1;   
   foreach my $col ("A".."C"){
       $Chart->SeriesCollection->Add($Sheet->Range($col."2:".$col."7")
+);
 
$Chart->SeriesCollection($series)->{Name}=$Sheet->Range($col."2")->{Va
+lue};
       $series++;
   }
   
   $Chart->{HasTitle} = $vttrue;
   $Chart->ChartTitle->{Text} = "My Chart Title";

    [download]

    C-.

    ---
    Flex the Geek

[reply]
[d/l]

Back to Seekers of Perl Wisdom