Perl and Win32 OLE Excel scripting problem/question... simple? 
Author Message
 Perl and Win32 OLE Excel scripting problem/question... simple?

Hi all,

can someone please answer this?
I'm tryin to extract info from specific cells in a spreadsheet. I've
placed the cell ID pairs into an array.
The call to extract the data ( $Sheet->Cells($row,$col)->{'Value'} )
seems to object to the $ROW,$COL assignments from an array... I keep
getting an error message that says:

Quote:
>> Win32::OLE(0.1601) error 0x800a03ec
>> in METHOD/PROPERTYGET "Cells" at C:\perl_dev\b.pl line 18

yet, the same $ROW,$COL assignments from a FOREACH statement works
fine... (?!?)

HERE IT IS:
--------------------------------------------------------------------
#!perl -w

#
# A simple script to extract and print information from
# specific cells in an Excel spreadsheet (cells 4,2 5,2 3,8 2,8 13,9)
#

use Win32::OLE qw(in with);                             # open WIN32 OLE
use Win32::OLE::Const 'Microsoft Excel';                # handle EXCEL files
$Win32::OLE::Warn = 3;                                  # die on errors

$Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application','Quit');
$Book = $Excel->Workbooks->Open("c:\\perl_dev\\test2.xls");
$Sheet = $Book->Worksheets(1);

# -- HERE IS WHERE THE PROBLEMS START! -- #

for ($i = 0; $i <= 8; $i += 2) {
        $row=$L[$i];
        $col=$L[$i+1];                                  

        # skip empty cells, or print cells containing information              
        next unless defined $Sheet->Cells($row,$col)->{'Value'};
        printf "ROW %s, COL %d, VALUE
%s\n",$row,$col,$Sheet->Cells($row,$col)->{'Text'};

Quote:
}

$Book->Close;

# #################################################
# The above code DOES NOT work, yet the $ROW, $COL
# assignments in this very similar code works fine(!?)
#
#       foreach my $row (1..10)
#       {
#               foreach my $col (1..10)
#               {
#               next unless defined $Sheet->Cells($row,$col)->{'Value'};
#               printf "ROW %s, COL %d, VALUE %s\n",
#                       $Sheet->Cells($row,$col)->{'Value'},
#                       $Sheet->Cells($row,$col)->{'Formula'};        
#               }
#       }
#
#

THANKS!!!!!
Jeff



Tue, 22 Nov 2005 17:49:21 GMT  
 Perl and Win32 OLE Excel scripting problem/question... simple?

: The call to extract the data ( $Sheet->Cells($row,$col)->{'Value'} )
: seems to object to the $ROW,$COL assignments from an array... I keep
: getting an error message that says:
:
: >> Win32::OLE(0.1601) error 0x800a03ec
: >> in METHOD/PROPERTYGET "Cells" at C:\perl_dev\b.pl line 18

[snip]

: for ($i = 0; $i <= 8; $i += 2) {
:       $row=$L[$i];
:       $col=$L[$i+1];                                  
:       # skip empty cells, or print cells containing information              
:       next unless defined $Sheet->Cells($row,$col)->{'Value'};
:       printf "ROW %s, COL %d, VALUE %s\n",$row,$col,$Sheet->Cells($row,$col)->{'Text'};
: }

Looks like something is getting flummoxed by the distinction between
strings and numbers.  That is, the list created by qw() are strings
(with an external appearance of numbers), but they are being handed
off to the Cells() method without getting numified.

Changing it to

    $Sheet->Cells(0 + $row, 0 + $col)->{'Value'}

brings things back into line.  Or you could force numification at the
array assignment like


Nicely prepared test program, btw.  Not too much, not too little.
My only misgiving about it was its lack of "use strict;" .



Wed, 23 Nov 2005 00:03:14 GMT  
 Perl and Win32 OLE Excel scripting problem/question... simple?
THANKS!

Of course! I can't believe I overlooked that!
I get so used to Perl not really caring too much about the distinction
between nums and strings that I got lazy when it comes to other
languages being so sensitive!

[ snip ]

Quote:



Clever! the "map $_+0" was not something I was aware of. I'll use that
A LOT in the future!

Thanks again!



Wed, 23 Nov 2005 23:14:53 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Problem publishing MS Excel worksheet to HTML using perl Win32::OLE

2. Win32::OLE Excel question.

3. Problem with Win32::OLE, Excel and ChartWizard

4. Problems with Win32:OLE and Excel

5. Win32::OLE Excel FileFormat Problem

6. Problem reading multiple rows and columns from Excel spreadsheet with Win32::OLE

7. Win32::OLE and MS Excel Problem

8. Win32::OLE, use active excel file from perl

9. ANN: Perl tut # 6: Win32::OLE with MS Excel

10. Perl, Win32::OLE, and Excel

11. Perl, OLE and Excel on Win32

12. Perl-Win32 OLE and Excel Flakey ?

 

 
Powered by phpBB® Forum Software