Excel Win32::OLE - copy data problem

[imrags]

Hi Monks,
I checked the tutorial on perlmonks regarding data copying in excel using perl.
However, i did not get the answer i was looking for
Here's what i'm trying to do:
I've an excel workbook with a lot of sheets, i'm trying to get the data from a few of the sheets
to a new sheet within the workgroup. I'm unable to do it..
This is hte code i've written, plz let me know why it's failing

my $Book = $Excel->Workbooks->Open("$input_file") || die "could not op
+en excel file";
my $sheetcnt = $Book->Worksheets->Count();
$sheetcnt++;
my $New_Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book
+->Worksheets->{Count})});
print "Sheetcount = $sheetcnt\n";

####30################################################################
+##############

my $count = 0; ########Counting the sheet number, if first sheet, then
+ take the whole data, else exclude first row

foreach my $Sheet(in $Book->{Worksheets})
{

    my $select_sheet = $Sheet;    
    
       
    if($select_sheet->Range("A1")->{Value} =~ /Step/i)
    {
                        my ($last_row,$last_column) = sub_find_last($S
+heet);
            print "$select_sheet->{Name} $last_row,$last_column\n";
        last if ($count == $Book->Worksheets->{Count});
        if($count==0)
        {            my $copy_range = $select_sheet->Range("A1:$last_c
+ol");
            $select_sheet->copy($copy_range);
            my $paste_range = $New_Sheet->Range('A1');
            $paste_range->paste();
            $count++;
        }
        else
        {
            my ($last2_row,$last2_col) = sub_find_last($New_Sheet);
            print "$last2_row and $last2_col are here\n";
            my $range_needed = $last2_row + 1;
            my $copy_range = $select_sheet->Range("B1:$last_col");
            $select_sheet->copy($copy_range);
            my $paste_range = $New_Sheet->Range("$range_needed");
            $pasted = $paste_range->paste();
#            $Book2->Save();
            $count++;
        }
        
    }
    

}



##########Subroutines###########

####sub find_last########
####Used to find the last row/column in the sheet

sub sub_find_last 
{
    my $sheet_sel = shift;
#    print "$sheet_sel is the sheet selected\n";
    my ($last_rows,$last_col);
    $last_rows = $sheet_sel->UsedRange->Find({What=>"*",
                SearchDirection=>xlPrevious,
                SearchOrder=>xlByRows})->{Row};
    $last_col = $sheet_sel->UsedRange->Find({What=>"*",
                SearchDirection=>xlPrevious,
                SearchOrder=>xlByColumns})->{Column};
    return ($last_rows,$last_col);
}

UPDATE:::
The script works fine if i only try to find the sheets which have "Step" in A1 cell... However, when I try
to process the sheets for copying/pasting the cells to the new sheet (last sheet), it fails...
I get the output (command-line) which is similar to:

Sheetcount = 49
Sheet18 3,6
Sheet20 2,6
Can't use an undefined value as a HASH reference at script.pl

Update2 :::
Solved the problem... A bit of using warnings/strict gave the clue and an old solution on
perlmonks (ironically, provided by me id:781893:(), helped to clear the air...
I've updated the code below:

foreach my $Sheet (in $Book->{Worksheets})
{

    my $select_sheet = $Sheet;
    if($select_sheet->Range("A1")->{Value} =~ /Step/i)
    {
        print "CounT IS $count \n";
        my ($last_row,$last_column) = sub_find_last($select_sheet);
        print "$select_sheet->{Name} $last_row,$last_column\n";
        if ($cnt == $num)
        {
            last;
        }
        elsif($count == 0)
        {
            print "I am here";
            my $rango = "C"."$last_row";
            print "$rango is the rango";
            $select_sheet->range("A1:$rango")->copy();
            $New_Sheet = $Book->Worksheets($sheetcnt);
            $New_Sheet->range("A1")->Select;
            $New_Sheet->paste();
            $Book->Save();
            $count++;
        }
        else
        {
            my ($last2_row,$last2_col) = sub_find_last($New_Sheet);
            my $range_needed = $last2_row + 1;
            my $rango2 = "C"."$last_row";
            my $rango3 = "A"."$range_needed";
            $select_sheet->range("A2:$rango2")->copy();
            $New_Sheet->range("$rango3")->Select;
            $New_Sheet->paste();
            $Book->Save();
            $count++;
        }
        $num++;
    }
    else
    {
        $num++;
    }

}

[roboticus]

imrags:

The error message gives you the information you need: You're trying to treat a variable without a value as a hash reference. Going by your description, I'd guess that $copy_range is undefined. You really ought to do some error checking in your code so you can trap things like this. Also, you should try running your code with the debugger so you can examine variable values at various places.

...roboticus

[imrags]

Well, I did more research on that.. The problem seems to come from this line in the code


my ($last2_row,$last2_col) = sub_find_last($New_Sheet);

This line does not seem to work..Strange, as a similar command works 2 line above :(

[roboticus]

imrags:

I doubt it's that line, as that line doesn't attempt to treat anything as a hash reference.

Run your program with the debugger, it's not hard, and you'll find the problem pretty easily. First, invoke your program under the debugger:

$ perl -d dbg.pl

Loading DB routines from perl5db.pl version 1.3
Editor support available.

Enter h or `h h' for help, or `man perldebug' for more help.
main::(dbg.pl:5):       my $t;
  DB<1>

At this point, perl is waiting to execute the first statement of your program, "my $t;" in this case. Since you have a suspicious line, you can tell perl to execute your program until it enters the sub_find_last subroutine by first setting a breakpoint in the subroutine, and then telling perl to run as normal, like this:

  DB<1> b sub_find_last
  DB<2> r
main::sub_find_last(dbg.pl:31):         my $X = shift;
  DB<2>

Now the debugger is in the sub_find_last routine, ready to execute the first line. You can execute that line (with the 'n' (next) command, and then see what was loaded into $X with the 'p' (print) command, like so:

  DB<2> n
main::sub_find_last(dbg.pl:32):         return if !defined $X;
  DB<2> p $X
Use of uninitialized value $X in print at (eval 5)[/usr/lib/perl5/5.10
+/perl5db.pl:638] line 2.
 at (eval 5)[/usr/lib/perl5/5.10/perl5db.pl:638] line 2
        eval '($@, $!, $^E, $,, $/, $\\, $^W) = @saved;package main; $
+^D = $^D | $DB::db_stop;
print {$DB::OUT}  $X;

;' called at /usr/lib/perl5/5.10/perl5db.pl line 638
        DB::eval called at /usr/lib/perl5/5.10/perl5db.pl line 3436
        DB::DB called at dbg.pl line 32
        main::sub_find_last(undef) called at dbg.pl line 8

  DB<3>

Any time you want, you can use the 'h' command to get a brief overview of the commands available in the debugger interface. If you spend a little time learning how to use the debugger, you'll greatly increase your development speed. Why is that? First, you'll be able to see exactly what's happening, rather than guessing and hoping. Second, when you have a data problem, you can immediately repair the data item and continue execution rather than edit the program restart and work through all the inputs to get back to the same spot. Third (and most importantly, IMHO), you'll start getting a better "feel" for the language, which will help you avoid making many mistakes in the first place.

So go on, give it a go. If you get stuck on something, we'll be glad to help out. I'd first set a breakpoint (the 'b' command) on the line:

if($select_sheet->Range("A1")->{Value} =~ /Step/i)

Then, since you know the first two sheets process normally (I think, based on the output you showed in the OP), you can let perl process the first two sheets normally by using the run ('r') command twice. The first time, it'll process the first sheet, go back to the beginning of the loop, and break. The second 'r' will process the second sheet, go back to the top of the loop and break again. Then you can single-step through the program and find out what beaks. Once you get used to the debugger, you'll be able to find and repair your programs pretty quickly.

Be sure to use the help ('h') command to see how to use the common commands, and read (perldoc perldebug) for more details and information on the less-common commands.

...roboticus