Excel saving as PDF as 1 page

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

I am using the following Perl to save an Excel file as a PDF (found on the PerlMonks site).
This works well but I want to save it so that it just fits one page.

$Book->ExportAsFixedFormat(
    {
        Type                 => xlTypePDF,
        Filename             => "$pdffilename",
        Quality              => $excel->{xlQualityStandard},
        IncludeDocProperties => $excel->{True},
        IgnorePrintAreas     => $excel->{False},
        OpenAfterPublish     => $excel->{False},
    }
);
[download]

I found the following on another web site

' The next 2 lines will force the PDF to fit the range onto one page
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
[download]

I tried to adapt this for Perl with the following as I thought, if successful, it will be used in the IncludeDocProperties => $excel->{True}, option

$sheet = $Book -> Worksheets("Sheet1");
$sheet -> Activate;
$sheet->PageSetup->FitToPagesWide = 1;
$sheet->PageSetup->FitToPagesTall = 1;
[download]

However, I got the following error
Tk::Error: Can't modify non-lvalue subroutine call at the FitToPagesWide line
I would appreciate help to know:
1. If this is the correct method and if so what corrections to make;
2. If this is not the correct method how do I achieve getting just one page in the PDF file

Comment on Excel saving as PDF as 1 page Select or Download Code

Replies are listed 'Best First'.
Re: Excel saving as PDF as 1 page by soonix (Chancellor) on Oct 24, 2015 at 21:01 UTC
Can't test this now, but ISTR it should be `$sheet->PageSetup->{FitToPagesWide} = 1; $sheet->PageSetup->{FitToPagesTall} = 1;` [download] Otherwise it's interpreted as method call instead of attribute access.	[reply] [d/l]
Re^2: Excel saving as PDF as 1 page by merrymonk (Hermit) on Oct 24, 2015 at 21:34 UTC
Thank you. This did not give any failure messages. Sadly it did not work as the PDF file was written to two pages. Have you any idea how to make it use just one page?	[reply]
Re^3: Excel saving as PDF as 1 page by poj (Abbot) on Oct 25, 2015 at 08:46 UTC
try `use Win32::OLE qw(with); use Win32::OLE::Variant; my $vtfalse = Variant(VT_BOOL, 0); my $vtpages = Variant(VT_I4, 1); with ($sheet->PageSetup, 'Zoom' => $vtfalse, 'FitToPagesWide' => $vtpages, 'FitToPagesTall' => $vtpages );` [download] poj	[reply] [d/l]
Re^4: Excel saving as PDF as 1 page by merrymonk (Hermit) on Oct 25, 2015 at 09:22 UTC
Re^3: Excel saving as PDF as 1 page by soonix (Chancellor) on Oct 25, 2015 at 07:50 UTC
This: http://www.mrexcel.com/forum/excel-questions/311404-visual-basic-applications-page-setup-fit-1-1-a.html says you have to set `->{Zoom}` to False, but in other situations that property is a numeric zoom factor, so I'd set it to zero. As for the why, I surmise the zoom factor has higher priority than the "Fit to Page" setting.	[reply] [d/l]
Re^4: Excel saving as PDF as 1 page by merrymonk (Hermit) on Oct 25, 2015 at 09:24 UTC