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++;
}
}