Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

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 scrutinizing the Monastery: (6)
As of 2017-11-18 13:06 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (277 votes). Check out past polls.