http://www.perlmonks.org?node_id=825555

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;
s/te/ve/

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

      The respective footnote on the page the OP linked to says

      (3) 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.)

        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

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

    HTH,

    planetscape

        Until now, I did not know about Win32::OLE::Const. ;-) Just remembering from my VB(A) days... ::shiver::

        HTH,

        planetscape