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

Hi Monks, I've recently learned a little bit about creating Excel spreadsheets with Win32::OLE but I've run into a few problems that I can't figure out: I can't change the scaling of my worksheet nor can I print it. To set the page scaling to 1x1 I have tried: 
use Win32::OLE;

my $application = Win32::OLE -> new "Excel.Application";
my $workbook = $application -> Workbooks -> Add;
my $worksheet = $workbook -> Worksheets(1);

$worksheet -> PageSetup -> {FitToPagesWide} = 1;
$worksheet -> PageSetup -> {FitToPagesTall} = 1;

[download]
but that doesn't work; may you please let me know what I'm doing wrong and how it may be fixed? And I don't even know where to start for printing; I have Adobe Professional and would like to print my worksheet as a PDF, so in addition to selecting my printer how would I state what I want the "hardcopy" to be named? I would really like to use Win32::OLE rather than Spreadsheet::WriteExcel, and any help that you can provide will be greatly appreciated. Thank you!

Replies are listed 'Best First'.
Re: Win32::OLE, Excel, & page scaling and printing
by Anonymous Monk on Jan 19, 2010 at 01:14 UTC
[reply]
      There is no error but the page is not scaled properly; when looking in the Page Setup of my file after executing my script, I see that the scaling remains at the default "Adjust to 100%".
[reply]
Re: Win32::OLE, Excel, & page scaling and printing
by Sinistral (Monsignor) on Jan 19, 2010 at 14:09 UTC
    As the Anonymous Monk said, the MSDN Excel reference can be quite useful. I found this VB code in reference to the PageSetup.FitToPagesTall Property: 
    With Worksheets("Sheet1").PageSetup
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With

    [download]

    It lookslike you need to make sure that the PageSetup.Zoom property is false. The proper way to do this is defined in the node: Using Win32::OLE and Excel - Tips and Tricks as already referenced by Anonymous Monk. Note that the example in that node does exactly what you want, setting the page scaling to 1 page exactly.

[reply]
[d/l]
      Making zoom equal to false, or true, and then setting the page scaling also has not worked for me. Is there some quirk about Perl that I need to use a different hash value for FitToPagesTall and FitToPagesWide, or make them both equal to something other than 1? For example, to set the page orientation to landscape in Perl you must write 
         $worksheet -> PageSetup -> {Orientation} = 2;

      [download]
      but in VB you'd set the Orientation to "x1Landscape".
[reply]
[d/l]
[reply]

Back to Seekers of Perl Wisdom