Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

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 (Curate) 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
Discipulus seems a part of the globe very unfrindly with Kawasaki riders..
[thezip]: My doggies love it when it snows
[perldigious]: As long as you are a good "boy scout" stevieb, more power to you... but I like my Jeep, and don't like the idea of rolling it or crashing it in to anything. :-)
[thezip]: Me, not as much.
[perldigious]: Mine too thezip, especially my German Shepherd. He looks like he is about to die of heat exhaustion all summer, so he goes nuts when winter comes and he can play in the snow.
[stevieb]: my main off-road vehicle nowadays is a strengthened mercedes ML320. I'm very hard on it, but I've never had any real issues at all (I love how small and light it is, and it has a very, very good centre of gravity for how I roll)
[perldigious]: "for how I roll"... ahhh, I see what you did there stevieb.

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (14)
As of 2016-12-06 16:52 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (112 votes). Check out past polls.