Save Excel worksheets as PDF

slloyd has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to save each worksheet(tab) in an Excel worksheet as a PDF or PNG. Here is what I have so far (doesn't work though). Can anyone help?

Note: I found the Excel constants at http://www.convert-files.com/SII/Convert-XLS/English/WebHelp/command_line_manual/file_type_constants/rhid_excel_conversion_file_type_constants.htm so I tried -1 as the FileFormat but it did not work.

Update: If this is not possible, is there a way to save the entire xlx file as PDF?

#!c:\perl\bin
use strict;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
       Win32::OLE->new('Excel.Application');
$Excel->{'Visible'} = 1;        #0 is hidden, 1 is visible
$Excel->{DisplayAlerts}=1;    #0 is hide alerts

# Open File and Worksheet
my $Book = $Excel->Workbooks->Open('C:\temp\XLS\test.xls');
my $cnt=0;
foreach my $Worksheet (in $Book->{Worksheets}){
    $cnt++;
    my $filename="C:\temp\XLS\test2\_$cnt.pdf";
    print "saving $filename\n";
    #HOW DO I SAVE THIS worksheet as a PDF or a PNG???
    $Worksheet->SaveAs({Filename =>$filename,FileFormat => -1});
    }

# Save as Excel
$Book->Close();
$Excel->Quit();
exit;
[download]

s/te/ve/

Comment on Save Excel worksheets as PDF Download Code

Replies are listed 'Best First'.
Re: Save Excel worksheets as PDF by CountZero (Bishop) on Feb 26, 2010 at 19:08 UTC
Last time I checked my copy of Excel 2003 it did not have an option to save a workbook or worksheet as a PDF or PNG file. Open Office can save a worksheet as a PDF file and can read Excel files. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re^2: Save Excel worksheets as PDF by almut (Canon) on Feb 26, 2010 at 19:23 UTC
The respective footnote on the page the OP linked to says ⁽³⁾ Versions of Excel 2007 and up will support this file format. PDF and XPS require the “Microsoft Save as PDF or XPS” add in. Go to Microsoft’s web site and search for “Microsoft Save as PDF or XPS add in”. (Not sure if the OP has verified the add in is installed, though.)	[reply]
Re^3: Save Excel worksheets as PDF by CountZero (Bishop) on Feb 27, 2010 at 07:37 UTC
I totally missed that the OP might be using Excel 2007. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re: Save Excel worksheets as PDF by planetscape (Chancellor) on Feb 27, 2010 at 21:09 UTC
Note: I found the Excel constants at ... For future reference... You can also find such things using West Wind Technology's GetConstants utility, which allows you to look at all those cryptic constants MS Office uses. (A short snippet of output is below.) Note that they do shuffle the links around occasionally, so it may be necessary to do some searching. :-) `*** Constant Group: WdTableFieldSeparator #define wdSeparateByParagraphs 0 #define wdSeparateByTabs 1 #define wdSeparateByCommas 2 #define wdSeparateByDefaultListSeparator 3` [download] HTH, planetscape	[reply] [d/l]
Re^2: Save Excel worksheets as PDF by Corion (Patriarch) on Feb 27, 2010 at 21:12 UTC
What advantage does it have over Win32::OLE::Const?	[reply]
Re^3: Save Excel worksheets as PDF by planetscape (Chancellor) on Feb 27, 2010 at 21:19 UTC
Until now, I did not know about Win32::OLE::Const. ;-) Just remembering from my VB(A) days... ::shiver:: HTH, planetscape	[reply]