Win32::OLE SaveAs Problem

Sasuke300 has asked for the wisdom of the Perl Monks concerning the following question:

Hey guys, In the folder where my perl script is saved there is another folder called "Template" where I want so save the Excelsheet I manipulated with Win32::OLE. Also the name of the Excelsheet that I want to save should depend on a variable called $input. But I have no idea how to do that. I tried this but it didn't work

$pathfile3 = Win32::GetFullPathName("Template\\$input.xlsx");
$Book2->SaveAs("$pathfile3");
[download]

Comment on Win32::OLE SaveAs Problem Download Code

Replies are listed 'Best First'.
Re: Win32::OLE SaveAs Problem by vr (Curate) on Feb 25, 2019 at 16:53 UTC
With preemptive strike i.e. use of Win32::LongPath and switching of the default to ensure proper Unicode handling if paths use characters outside your ANSI codepage: `use Win32::LongPath 'getcwdL'; use File::Spec::Functions 'catfile'; use Win32::OLE; Win32::OLE-> Option( CP => Win32::OLE::CP_UTF8 ); # ... $pathfile3 = catfile( getcwdL, 'Template', "$input.xlsx"); $Book2-> SaveAs( $pathfile3 );` [download] Edit. It occurred to me, you may want to run the script while in another directory: `use FindBin; use File::Spec::Functions 'catfile'; use Win32::OLE; Win32::OLE-> Option( CP => Win32::OLE::CP_UTF8 ); # ... $pathfile3 = catfile( $FindBin::Bin, 'Template', "$input.xlsx"); $Book2-> SaveAs( $pathfile3 );` [download] There is a regression above, in that I don't assume you plan to work with non-ascii file paths (while, still, it's OK to pass Unicode strings to Excel - which I think is not uncommon even for ascii-people). The reason is there seem to be ugly details not covered by `Win32::LongPath` or other modules I know -- both `$0` and `$FindBin::Bin` contain encoded (ANSI-CP) strings, so there is a mess not only with decoding, but testing what happens if path to perl script has non-ANSI-CP characters in relative path.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Win32::OLE SaveAs Problem
by vr (Curate) on Feb 25, 2019 at 16:53 UTC

With preemptive strike i.e. use of Win32::LongPath and switching of the default to ensure proper Unicode handling if paths use characters outside your ANSI codepage:

use Win32::LongPath 'getcwdL';
use File::Spec::Functions 'catfile';

use Win32::OLE;
Win32::OLE-> Option( CP => Win32::OLE::CP_UTF8 );
# ...

$pathfile3 = catfile( getcwdL, 'Template', "$input.xlsx");
$Book2-> SaveAs( $pathfile3 );
[download]

Edit. It occurred to me, you may want to run the script while in another directory:

use FindBin;
use File::Spec::Functions 'catfile';

use Win32::OLE;
Win32::OLE-> Option( CP => Win32::OLE::CP_UTF8 );
# ...

$pathfile3 = catfile( $FindBin::Bin, 'Template', "$input.xlsx");
$Book2-> SaveAs( $pathfile3 );
[download]

There is a regression above, in that I don't assume you plan to work with non-ascii file paths (while, still, it's OK to pass Unicode strings to Excel - which I think is not uncommon even for ascii-people). The reason is there seem to be ugly details not covered by Win32::LongPath or other modules I know -- both $0 and $FindBin::Bin contain encoded (ANSI-CP) strings, so there is a mess not only with decoding, but testing what happens if path to perl script has non-ANSI-CP characters in relative path.

[reply]
[d/l]
[select]