Win32-OLE, excel, and empties.

[Richard S Beckett]

Hello World!

I'm trying to find the first empty row in a spreadsheet.

If I use this:

my $EmptyRow = $sheet->UsedRange->rows->count;
$EmptyRow ++;

It works, UNLESS the spreadsheet is empty, where the first line returns 1,
and I end up writing to row 2 instead of row 1.

If I use this:

my $EmptyRow = $sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows})->{Row};
my $err = Win32::OLE::LastError();
unless ($err eq 0) {$EmptyRow = 0;}
$EmptyRow ++;

This also works, unless the spreadsheet is empty, where I get the following
error:

Can't use an undefined value as a HASH reference at D:\Script.pl line 43,
<FILE> line 167.

Line 43 is:

my $LastRow = $sheet->UsedRange->Find({What=>"*",

and I only have 127 lines.

Can someone help me out, please?

Thanks.

R.

 

[Jay Tilton]

: I'm trying to find the first empty row in a spreadsheet.

: If I use this:
:
: my $EmptyRow = $sheet->UsedRange->Find({What=>"*",
: SearchDirection=>xlPrevious,
: SearchOrder=>xlByRows})->{Row};
: my $err = Win32::OLE::LastError();
: unless ($err eq 0) {$EmptyRow = 0;}
: $EmptyRow ++;
:
: This also works, unless the spreadsheet is empty, where I get the following
: error:
:
: Can't use an undefined value as a HASH reference at D:\Script.pl line 43,
: <FILE> line 167.

Just make sure the Find() method returns a defined value before trying
to use it as a hash reference.

my $search = $sheet->UsedRange
->Find({
What=>"*",
SearchDirection=>xlPrevious,
SearchOrder=>xlByRows
});
my $EmptyRow = 1 + (defined $search && $search->{Row});