Win32-OLE, excel, and empties.

Discussion in 'Perl Misc' started by Richard S Beckett, Jul 11, 2003.

  1. 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.
    Richard S Beckett, Jul 11, 2003
    #1
    1. Advertising

  2. Richard S Beckett

    Jay Tilton Guest

    "Richard S Beckett" <> wrote:

    : 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});
    Jay Tilton, Jul 11, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. M. David Allen

    Win32::OLE, Excel, and coloring

    M. David Allen, Jun 24, 2003, in forum: Perl
    Replies:
    1
    Views:
    5,375
    Jay Tilton
    Jun 25, 2003
  2. M. David Allen

    Win32::OLE, Excel, and coloring

    M. David Allen, Jun 24, 2003, in forum: Perl Misc
    Replies:
    1
    Views:
    182
    Jay Tilton
    Jun 25, 2003
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    228
    Lance Hoffmeyer
    Nov 17, 2003
  4. Domenico Discepola

    Win32::OLE and creation of pivot table in Excel

    Domenico Discepola, Dec 3, 2003, in forum: Perl Misc
    Replies:
    4
    Views:
    541
    Domenico Discepola
    Dec 4, 2003
  5. Replies:
    1
    Views:
    803
Loading...

Share This Page