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.

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



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

-- 
brian

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

Brian Helterline wrote:
> Woland99 wrote:
> > 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 } );
> 
> -- 
> brian

"Woland99" <woland99@gmail.com> wrote:

> 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                Experienced Perl programmer: http://castleamber.com/

          Perl help, tutorials, and examples: http://johnbokma.com/perl/

John Bokma <john@castleamber.com> wrote:
 
> You might want to s{/}{\\} $fullname_output_file

                            ^ g

-- 
John                Experienced Perl programmer: http://castleamber.com/

          Perl help, tutorials, and examples: http://johnbokma.com/perl/

$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

John Bokma wrote:
> John Bokma <john@castleamber.com> wrote:
>
> > You might want to s{/}{\\} $fullname_output_file
>
>                             ^ g
>
> --
> John                Experienced Perl programmer: http://castleamber.com/
>
>           Perl help, tutorials, and examples: http://johnbokma.com/perl/

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

-- 
brian

"Woland99" <woland99@gmail.com> wrote:
: $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.

I agree, WinXP and later (possibly earlier as well) both change
forward slashes to backslashes.

Windows runs into difficulties when spaces 0x20 are inserted
into path and filenames. The way to get around this problem
involves enquoting the string in double-quotes (apostrophes
might work as well, but I've always used quotation marks).
There might be some other special characters that require the
quotation marks as well.

For instance, if the resulting path ends up as...

C:\Program Files\My App\My Data\My Spreadsheet.xls

and that gets placed inside a string variable, $MyExcelFile,
then encapsulate the FQFN in quotation marks.

$QM = "\"";
$MyExcelFile = $QM . $MyExcelFile . $QM;

Hope this helps.

-- 
Jim Carlock
Post replies to the group.