Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

save excel as pdf (OLE)

by Anonymous Monk
on Aug 31, 2011 at 19:53 UTC ( #923491=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, Using win32::OLE, this code saves an excel spreadsheet as a pdf file

SaveAs($pdf_file_name, 57);

But it only saves one sheet. How do I modify it to save an entire workbook into pdf?



Replies are listed 'Best First'.
Re: save excel as pdf (OLE)
by dasgar (Priest) on Aug 31, 2011 at 21:14 UTC

    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.

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://923491]
Approved by planetscape
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2018-02-22 06:27 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (288 votes). Check out past polls.