Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: save excel as pdf (OLE)

by dasgar (Deacon)
on Aug 31, 2011 at 21:14 UTC ( #923527=note: print w/ replies, xml ) Need Help??


in reply to save excel as pdf (OLE)

Don't remember ever trying to save an Excel file as a PDF from within Excel (manually or via script). However, I suspect that it would behave similar to saving as a CSV file, which only saves the current/active worksheet. If that's the case, you'll probably have to select and save each worksheet seperately.


Comment on Re: save excel as pdf (OLE)
Re^2: save excel as pdf (OLE)
by Anonymous Monk on Sep 07, 2011 at 17:30 UTC
    Wish it were better news, but thank you for explaining. Bill
      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);
        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://923527]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (7)
As of 2014-12-21 03:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (102 votes), past polls