RE: Perl and Microsoft Excel: Can they play together?

[Wagner-David]

YOu should be able to do all that you want.  The Perl seems a little old,
but maybe someone else can address that.  Here is some code that I am using
to convert a number of excel files to acrobat using Acrobat writer.  This is
being run under win2k and Active Perl Build 623 (5.6.0)

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;

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


my $MyExcelFile = '';
my $WorkingLoc  = 'd:\Tariffs\VK 201-C';  # <--- Put your own directory info
here

chdir("$WorkingLoc") || die "Unable to change to Tariffs Directory
$WorkingLoc: $!";

=head1
$spread->{Visible} = 1; # comment out if you don't want to see what'sgoing
on in Excel

=cut

while ( 1 ) {           
   $MyExcelFile = <*.xls>;
   last if ( ! defined $MyExcelFile );

   my $book = $excel->Workbooks->Open("$MyExcelFile",undef,1);  # the 1 in
third position is to
        
# bypass asking do you want to open
        
# readonly
 }

I removed quite a bit of code, but I just went through about a 900 files
using the above code.

I am also placing two other perl scripts which were put togehter by Jan
Dubois at Active State concerning OLE and Excel.
        Script 1: pie chart
        Script 2: bar chart which rotates the chart on screen.

Hope this is helpful.

Wags ;)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++

Script 1:
#!perl -w

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

my $Excel = Win32::OLE->new('Excel.Application', 'Quit')
    or die Win32::OLE->LastError;

$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->Range("A1:D1")->{'Value'} = [qw(North South East West)];
$Sheet->Range("A2")->{'Value'} = 5.2;
$Sheet->Range("B2")->{'Value'} = 10;
$Sheet->Range("C2")->{'Value'} = 8;
$Sheet->Range("D2")->{'Value'} = 20;

my $Range = $Sheet->Range("A1:D2");
my $Chart = $Book->Charts->Add;
$Chart->ChartWizard($Range,xl3DPie,7,xlRows,1,0,2,"Sales Percentages");

$Book->{Saved} = 1;
print "Press ENTER to continue "; $|=1; <>;
#------------------------------------------------------------------------

Script 2:
#!perl -w

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

my $Excel = Win32::OLE->new('Excel.Application', 'Quit')
    or die Win32::OLE->LastError;

$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add();
my $Range = $Excel->Range("A1:A3");
$Range->{Value} = [3,2,1];
$Range->Select;
my $Chart = $Excel->Charts->Add;
$Chart->{Type} = xl3DColumn;
for (my $i=30; $i <=180; $i+=10) {
    $Chart->{Rotation} = $i;
    sleep(1);
}
$Book->{Saved} = 1;
#------------------------------------------------------------------------

-----Original Message-----
From: Carl Rogers [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 03, 2001 07:25
To: [EMAIL PROTECTED]
Subject: Perl and Microsoft Excel: Can they play together?



>Sorry if this is a dupe... Had error message on previous attempt..
>
>Good day;
>I am trying to open, read and manipulate Excel data using Perl. I am using 
>Perl v5.001.
>
>This is what I've found in various manuals that allows me to open a .xls
file:
>---------------------------------------------------------------------------
----------------------------------------------------
>
>use Win32;
>use OLE;
>
>$exceldoc = CreateObject OLE 'Excel Application' || die;
>$exceldoc ->{Visible} = 1;
>
>$workbook - $exceldoc->Workbooks->Open ('c:\data\myfile.xls');
>
>---------------------------------------------------------------------------
----------------------------------------------------
>The above code opens my spreadsheet!!! (amazing to me!!)
>
>What I'd like to do from here is use Perl to read the column headings and 
>the data under those headings so that I can write scripts to use regular 
>expressions, etc.- all the great things that Perl can do.
>
>Also, I've tried to use a variable in the "Open" object (myfile.xls) so 
>that I can dynamically open each file in the "data" directory. I can't 
>seem to get that to work. Is it possible?
>
>I hope I'm not wasting your time with this question, as I can not find any 
>other source able to assist. Any help would be greatly appreciated. I hope 
>I have given you enough information. If not, please don't hesitate to 
>contact me.
>Sincerely,
>Carl