windows – Why does Perl’s Win32::OLE complain about “Invalid index” for an Excel worksheet? – Education Career Blog

by

Code

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

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

# select worksheet number. Default is 1 (you can also select a worksheet by name)
print "worksheet $worksheet\n";
my $Sheet = $Book->Worksheets($worksheet);

where $worksheet is an integer obtained as a value from a hash.

Error - 
Win32::OLE(0.1709) error 0x8002000b: "Invalid index"
    in METHOD/PROPERTYGET "Worksheets" at win32excel.pl

Any suggestions what I might be doing wrong?

,

Yes, $worksheet is probably 0. Because Microsoft’s applications interface language is VB, the first index to Worksheets–as in many MS collections–is 1.

So you can test you range for sanity. 

if ( $worksheet > 0 and $worksheet <= $xl->Worksheets->{Count} ) { 
    ...
}

Jmz’s idea is probably not a bad way to surmount the issue. 

use Win32::OLE qw<in>;

my @sheets = in $book->worksheets;
my $first_sheet = $sheets0; # or shift @sheets.

Thus reducing the cognitive dissonance from VB-ish to Perl.

,

Determine which sheets are available before accessing them:

use Win32::OLE qw(in);
my @sheets = in $Book->Worksheets;

Leave a Comment