Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^3: save excel as pdf (OLE)

by Anonymous Monk
on Nov 06, 2012 at 16:14 UTC ( #1002514=note: print w/ replies, xml ) Need Help??


in reply to Re^2: save excel as pdf (OLE)
in thread save excel as pdf (OLE)

Here ist a script, that takes an excel file name and runs the excel function "Save As ..." for the whole work book.

use strict; use warnings; use File::Spec::Functions qw(rel2abs); use Win32::OLE; use Win32::OLE::Variant; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # die on errors... # get excel file name and full path my $filename = shift; $filename = rel2abs($filename); # get PDF file name my $pdffilename = $filename; $pdffilename =~ s/\.xls.*/\.pdf/i; # delete existing PDF file if ( -e $pdffilename ) { print "pdf file exists already. removing file\n"; unlink($pdffilename); } # Open document # Create new MSExcel object and load constants my $MSExcel = Win32::OLE->new( 'Excel.Application', 'Quit' ) or die "Could not load MS Excel"; my $excel = Win32::OLE::Const->Load($MSExcel); my $Book = $MSExcel->Workbooks->Open( { FileName => "$filename" } ); # Run Excel function "Save As ..." $Book->ExportAsFixedFormat( { Type => xlTypePDF, Filename => "$pdffilename", Quality => $excel->{xlQualityStandard}, IncludeDocProperties => $excel->{True}, IgnorePrintAreas => $excel->{False}, OpenAfterPublish => $excel->{False}, } ); # Close document $Book->Close( { SaveChanges => $excel->{xlDoNotSaveChanges} } ); if ( -e $pdffilename ) { print "PDF file created\n"; } exit(0);


Comment on Re^3: save excel as pdf (OLE)
Download Code
Re^4: save excel as pdf (OLE)
by jf200399 (Initiate) on Nov 06, 2012 at 17:03 UTC
    Minor changes
    use strict; use warnings; use File::Spec::Functions qw(rel2abs); use Win32::OLE qw(in with); use Win32::OLE::Variant; use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; # die on errors... print "$0 - Wandle Excel-Datei in PDF um ...\n\n"; # Dateiname der Excel-Datei holen und kompletten Pfad ergänzen my $filename = shift; $filename = rel2abs($filename); print "Ausgangsdatei $filename\n"; # PDF-Dateinamen festlegen my $pdffilename = $filename; $pdffilename =~ s/\.xls.*/\.pdf/i; # Wenn PDF-Datei schon existiert, dann löschen if ( -e $pdffilename ) { print "PDF-Datei $pdffilename existiert schon ... wird geloescht.\ +n"; unlink($pdffilename); } else { print "PDF-Datei $pdffilename\n"; } # Open document # Create new MSExcel object and load constants my $MSExcel = Win32::OLE->new( 'Excel.Application', 'Quit' ) or die "Could not load MS Excel"; my $excel = Win32::OLE::Const->Load($MSExcel); my $Book = $MSExcel->Workbooks->Open( { FileName => "$filename" } ); # Excel-Funktion "Speichern unter ..." aufrufen $Book->ExportAsFixedFormat( { 'Type' => xlTypePDF, 'Filename' => "$pdffilename", 'Quality' => xlQualityStandard, 'IncludeDocProperties' => "True", 'IgnorePrintAreas' => "False", 'OpenAfterPublish' => "False", } ); # Close document $Book->Close( { SaveChanges => $excel->{xlDoNotSaveChanges} } ); if ( -e $pdffilename ) { print "PDF-Datei wurde erzeugt. Programmende.\n"; } else { print "Fehler: Datei $pdffilename wurde nicht erzeugt. Programmend +e.\n"; } exit(0);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1002514]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (15)
As of 2015-07-03 07:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (48 votes), past polls