Win32::OLE - saving content of just one worksheet in Excel

[Woland99]

Howdy,

I need to save content of just one workshhet in Excel to tab-delimited
file.
I tried sth like:

$sheet = $wbook->{Worksheets}->{"$sheet_name"};
if($sheet->Activate()){;}
else
{
print STDERR "Could not access Sheet $sheet_name in the file
$fullname_input_file: $!\n";
exit;
}

my $file_type = -4158; #Constant equivalent for tab delimited file

$wbook->{ActiveSheet}->SaveAs({ FileName => $fullname_output_file,
FileFormat => $file_type })
|| print STDERR "didnt save the sheet $sheet_name of file
$fullname_output_file: $!\n";
$wbook->Close({ FileName => $fullname_output_file});

but SaveAs always fails complaining that $fullname_output_file does not
exists.
Is there sth I am missing?
If I do just:
$wbook->SaveAs({ FileName => $fullname_output_file, FileFormat =>
$file_type })
all works ok except that it save FIRST worksheet in workbook.

 

[Woland99]

Well I can do a clumsy workaround - copy active wsheet to
temp workbook and save it - but that is wasteful if you have to
process hundreds of files.
For example:
my $last_row = $sheet->{UsedRange}->Rows()->Count();
my $last_col = $sheet->{UsedRange}->Columns()->Count();
my $upper_left_cell = $sheet->Cells(1,1);
my $lower_right_cell = $sheet->Cells($last_row,$last_col);
my $wbook_temp;
$excel->{SheetsInNewWorkbook} = 1;
$wbook_temp = $excel->Workbooks->Add;
$sheet->Range($upper_left_cell,$lower_right_cell)->Copy();
$wbook_temp->Worksheets(1)->Paste();
$wbook_temp->SaveAs({ FileName => $fullname_output_file, FileFormat =>
$file_type })
|| print STDERR "didnt save the sheet $sheet_name of file
$fullname_output_file: $!\n";
$wbook_temp->Close({ FileName => $fullname_output_file});

 

[Brian Helterline]

Howdy,

I need to save content of just one workshhet in Excel to tab-delimited
file.
I tried sth like:

$sheet = $wbook->{Worksheets}->{"$sheet_name"};

don't need quotes around "$sheet_name"

if($sheet->Activate()){;} $sheet->Select;

else
{
print STDERR "Could not access Sheet $sheet_name in the file
$fullname_input_file: $!\n";
exit;
}

my $file_type = -4158; #Constant equivalent for tab delimited file

use Win32::OLE::Const 'Microsoft Excel';
# -4158 => xlCurrentPlatformText

$wbook->{ActiveSheet}->SaveAs({ FileName => $fullname_output_file,
FileFormat => $file_type })

FileFormat => xlCurrentPlatformText } )

|| print STDERR "didnt save the sheet $sheet_name of file
$fullname_output_file: $!\n";
$wbook->Close({ FileName => $fullname_output_file});

You just saved the file and this will force it to save again.
$wbook->Close( {SaveChange => 0 } );

 

[Woland99]

Thanks for corrections, Brian.

but the key part:

$sheet->Select;
$wbook->{ActiveSheet}->SaveAs({ FileName => $fullname_output_file,
FileFormat => xlCurrentPlatformText } )

does not work - I keep getting same error
$! contains string "No such file or directory".

$fullname_output_file:
C:/edata/UserData/backup/features/sdata/scripts/test.txt

JT

 

[John Bokma]

Thanks for corrections, Brian.

but the key part:

$sheet->Select;
$wbook->{ActiveSheet}->SaveAs({ FileName => $fullname_output_file,
FileFormat => xlCurrentPlatformText } )

does not work - I keep getting same error
$! contains string "No such file or directory".

$fullname_output_file:
C:/edata/UserData/backup/features/sdata/scripts/test.txt

Thanks for not top posting.

You might want to s{/}{\\} $fullname_output_file

 

[John Bokma]

You might want to s{/}{\\} $fullname_output_file

^ g

 

[Woland99]

$fullname_output_file =~ s{/}{\\}g;

That was my suspicion too but it does not work.
As amatter of fact Win32::OLE is not picky (in my opinion)
about forward/backard slash thing. When it works it even works
with file name that uses mix case of slashes.
The error message did not change:
No such file or directory

 

[Brian Helterline]

Thanks for corrections, Brian.

but the key part:

$sheet->Select;
$wbook->{ActiveSheet}->SaveAs({ FileName => $fullname_output_file,
FileFormat => xlCurrentPlatformText } )

Just save the wbook since you already selected the sheet you want.
It worked for me:

@wbook->SaveAs( { ... } );