#!/Perl

# from : http://aspn.activestate.com/ASPN/docs/ActivePerl/5.8/faq/Windows/ActivePerl-Winfaq12.html

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

my $File = "C:\\tmp\\tpj\\data.xls";
my $Range = "A1:E5";

my ($LOG);
my $write_log = 0;
my $outfile = 'tempauto.txt';
if ( open( $LOG, ">$outfile" ) ) {
    $write_log = 1;
} else {
    $write_log = 0;
    prt( "WARNING: Unable to open $outfile LOG ...\n" );
}

$Win32::OLE::Warn = 3;                                # die on errors...

prt("Starting Excel ...\n");

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');  # open new
##my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
##    || Win32::OLE->new('Excel.Application', 'Quit');  # get already active Excel
                                                      # application or open new

my $Book = $Excel->Workbooks->Open($File); # open Excel file

my $Sheet = $Book->Worksheets(1);                     # select worksheet number 1

my $Text = $Sheet->Range($Range)->{'Text'}; # DOES NOT APPEAR TO WORK???

my $array = $Sheet->Range($Range)->{'Value'};        # get the contents


$Book->Close;

prt("Closed excell ... print array, if any ...\n");
foreach my $ref_array (@$array) {                     # loop through the array
                                                      # referenced by $array
    foreach my $scalar (@$ref_array) {
        prt( "$scalar\t" );
    }
    prt( "\n" );
} 

prt("Done 'value' ...\n");

prt("Now 'text' ... if ANY - this seems to FAIL???\n");
foreach my $ref_array (@$Text) {                     # loop through the array
                                                      # referenced by $array
    foreach my $scalar (@$ref_array) {
        prt( "$scalar\t" );
    }
    prt( "\n" );
} 
prt("All done ...\n");

# To retrieve the formatted value of a cell you should use the {'Text'} 
# property instead of the {'Value'} property. This returns exactly what is being 
# displayed on the screen though! If the column is not wide enough, you get a value of '######':
# my $array = $Sheet->Range("A8:B9")->{'Text'};

################################
### output and log file
sub wlog {
	my $ml = shift;
	print $LOG $ml;
}

sub prt {
	my $m = shift;
	if ($write_log) {
		wlog($m);
	}
	print $m;
}

sub mydie {
	my $msg = shift;
	if ($write_log) {
		wlog($msg);
	}
	die $msg;
}

# eof - autoexcel02.pl

