Update excel spreadsheet ???? 1

[linkl01]

I want to update a few fields in a spreadsheet using OLE. Any input will be appreciated.
Thank you in advance.

 

[tsdragon]

Check out CPAN (

http://www.cpan.org)

for libraries to interface with OLE and/or Excel. Tracy Dryden
tracy@bydisn.com

http://www.bydisn.com

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[goBoating]

I can't immediately put you onto exactly how to update a specific cell. However, the following illustrates opening an excel file and pulling information from a block of cells. When we were working with this stuff, we had to mix the Perl documentation with the M$ documentation. We could not find a Perl related description of the methods that were available via OLE. So, we opened Excel, started the VB macro editor and used the 'HELP' from that to identify methods available via the Perl OLE hooks. It was tedious, but, with a little hunting we were able to do some useful stuff.

HTH

#!perl -w
#
# Name:
#    ole-xl-demo1.pl
#
# Purpose:
#    Test Win32::OLE with Excel.

use strict;
use Win32::OLE;

# --------------------------------------------------------

my $ex;
my $xlfile = 'd:\some\path\to\an\Excel\file\file.xls';

eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
    die "Excel not installed" if $@;
unless (defined $ex) 
    {
    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
        or die "Oops, cannot start Excel";
    }

my($book) = $ex->Workbooks->Open({FileName => $xlfile, ReadOnly=>1});
my($sheet) = $book->Worksheets(1);

# Get block D8:E9
my(@array) = $sheet->Range("D8:E9")->{'Value'};
my($i, $j);

print "Range is based on (D8:E9). \n";
print "Size of \@array: ", $#array + 1, ". \n";

for $i (0 .. $#array)
    {
    print "Size of \$#{\$array[\$i]}: ", $#{$array[$i]} + 1, ". \n";
    &report("\$array[$i]", $array[$i]);

    for $j (0 .. $#{$array[$i]})
        {
        &report("\$array[$i][$j]", $array[$i][$j]);
        print &quot;Live data @ ($i, $j): <&quot;, join(&quot;>\t<&quot;, 
                @{$array[$i][$j]}), &quot;>. \n&quot;;
        }
    }

$book -> Close();
$ex -> Quit();

print &quot;Success \n&quot;;
# exit(0);

# --------------------------------------------------------

sub report
{
    my($name, $thing) = @_;

    print &quot;ref($name): &quot;, ref($thing), &quot;. \n&quot;    if (ref($thing) );
    print &quot;$name is not a ref. \n&quot;            if (! ref($thing) );

}    # End of report;

Note: this example was retreived from a project dir that I don't remember very well. I ran it and it works, but, I don't remember if we found better mechanism to do some of tricks or not. I hope it helps.


keep the rudder amid ship and beware the odd typo