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

W

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.
 
W

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});
 
B

Brian Helterline

Woland99 said:
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 } );
 
W

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
 
J

John Bokma

Woland99 said:
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
 
W

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
 
B

Brian Helterline

Woland99 said:
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( { ... } );
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top