Hello MissPerl,

Well I just read your question and I remembered that maybe 5 - 6 years ago that another monk asked the same question writing after parsing.

Take a look on the question but I spend some time and I created a script that it was merging excel files into one here is the link Re^2: writing after parsin.

Let me know if it works, alternatively I can spend some time and debug it.

Update: I tested your code and you have many errors.

Let's fix them:

use warning; # needs to be use warnings;

my $path = '/abc';

my $te = "$abc/texas"; # I assume you mean $path instead of $abc?  
my $mi = "$abc/michigan"; I assume you mean $path instead of $abc?
my $ha = "$abc/hawaii"; I assume you mean $path instead of $abc?
my $fl = "$abc/florida"; I assume you mean $path instead of $abc?
my $ke = "$abc/kentucky"; I assume you mean $path instead of $abc?

my $filename = "path/one.xls"; # needs to be my $filename = "$path/one
+.xls";

my $excl = Spreadsheet::WriteExcel->new($filename)
 or die "Cannot create new Excel file: $!" unless defined $excl; # thi
+s will not work

my $workbook  = Spreadsheet::WriteExcel->new('protected.xls'); # from 
+module documentation (notice the ";" here)
die "Problems creating new Excel file: $!" unless defined $workbook; #
+ from module documentation

# add the following after the part

my $book = $parser->parse($source);

for my $source ( '$te/te.xls', '$mi/mi.xls', # all files need to be de
+fined with double quotes (e.g. "$te/te.xls")
         '$ha/ha.xls', '$fl/fl.xls', # with single quotes will not wor
+k
         '$ke/ke.xls' ){
    my $book = $parser->parse($source);

if ( !defined $book ) {
    die $parser->error(), ".\n";
}
[download]

Update2: I also noticed:

if ($source =~ /1/) {
    $file  = $excl->add_worksheet('Texas');
}
[download]

This will never work, you are invoking a method from Spreadsheet::WriteExcel/add_worksheet($sheetname, $utf_16_be) but the object that you are using is from Spreadsheet::ParseExcel/new(). Change object use the one that is for writing.

After fixing all the errors give it another try.

Hope this helps.

Hi Thanos, thank you for your help! the code is now clean with errors.

However I'm still not getting what I want.

Could you lead me to the right direction?

I have put some updated info in the question.

Thank you !!

Hi,

Can't open test.xls. It may be in use or protected at testing.pl line +43

One possible cause is that a failed run of your code is leaving an open instance of the speadsheet behind.

Should this be true you need to get to get rid of this 'orphan' before proceeding.

J.C.

Hi J.C., thank you for your reply !

The error is now gone!

However I'm still failing to get the desired output.

Could you point me to the right direction?

I have updated some info in the question.

Thank you for your help!

my $source = $book->add_worksheet($source);

This line suggests you are getting confused between the source and the target. Try using more descriptive variable names for each workbook/worksheet. Try

#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;

my $path   = '/abc';
my $output = $path.'/one.xls';
my $wb_target  = Spreadsheet::WriteExcel->new($output) 
  or die "Cannot create new Excel file: $!";;
  
my $parser = new Spreadsheet::ParseExcel;

my @states = qw(texas michigan hawaii florida kentucky);
for my $state ( @states ){
  my $abbr = substr($state,0,2);
  print "Creating sheet $abbr in $output\n";
  my $ws_target = $wb_target->add_worksheet(uc $abbr);
 
  my $source = "$path/$state/$abbr.xls";
  print "Copying $source\n";
  my $wb_source = $parser->parse($source) 
     or die $parser->error();
     
  ws_copy($wb_source->worksheet(0),$ws_target);
}
$wb_target->close();

sub ws_copy {
  my ($source,$target) = @_;
  my ( $row_min, $row_max ) = $source->row_range();
  my ( $col_min, $col_max ) = $source->col_range();

  for my $row ( $row_min .. $row_max ) {
    for my $col ( $col_min .. $col_max ) {
       my $cell = $source->get_cell( $row, $col );
       next unless $cell; 
       $target->write( $row , $col, $cell->unformatted );
    }
  }
}
[download]

poj

and how to get to get rid of this 'orphan'?