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

I am attempting to setup a Data Warehouse reporting piece that works on the web and writes to an Excel Spreadsheet (for formatting reasons). I have a script that works wonderfully EXCEPT it refuses to allow me to change any PageSetup items like Header, Footer, etc. The script runs fine on my WinXP IIS web, but NOT on my Win2000 web. If I remove the following section, the script runs. If I add it, I get this error: Unable to set the FitToPagesWide property of the PageSetup class 
Win32::OLE(0.1502) error 0x80020009: "Exception occurred"
    in PROPERTYPUT "FitToPagesWide" at D:/Perl/site/lib/Win32/OLE/Lite
+.pm line 201

[download]
Here's the actual code snippet -- fairly straightforward OLE code,I think. Thoughts anyone? Even for a Win32 heretic? 
with ($Sheet->PageSetup, 
    'FitToPagesWide'=>1, 
    'PrintGridlines'=>0,
    'LeftHeader'=> "REPORT #DW2009A\rSummary Place of  Service",
    'CenterHeader' => "Empty",
    'RightHeader' => "Test Report",    
    'LeftFooter' => "Done",
    'CenterFooter' => "Now",
    'Orientation' => xlLandscape,
    'RightFooter' => "OK",
    'PrintHeadings'=>0,
    'FirstPageNumber'=> xlAutomatic,
    'PrintTitleRows' => "1:1");

[download]

Replies are listed 'Best First'.
Re: Win32::OLE and Excel and the Web
by jmcnamara (Monsignor) on Apr 29, 2002 at 22:22 UTC

    It also ran fine on Win98 with Perl 5.005 and Excel 97. Which probably isn't good news. :-|

    That type of error usually occurs when the wrong variable type is being assigned to an object property. Something like this might placate the Win2000 COM. 

        use Win32::OLE::Variant;
    ...
    'FitToPagesTall'  => Variant(VT_I2, 1),

    [download]

    As an aside, even if you clear this error FitToPagesWide probably won't have an effect unless you also set Zoom = False since these two properties are mutually exclusive.

    --
    John.

[reply]
[d/l]
Re: Win32::OLE and Excel and the Web
by cacharbe (Curate) on Apr 30, 2002 at 14:08 UTC
    I ran your code on my Win2000adv without any problems either. Jmcnamara has the right of it, though. You will need to cast some of the properties as Variants to insure that you will get what you expect, especially the 'Zoom' property in this case.

    I'll add an example of variant use like this to my tutorial -- Must have slipped by me.

    my $vtfalse =  Variant(VT_BOOL, 0);  
my $vtpages =  Variant(VT_I4, 1);  
with ($Sheet->PageSetup, 
    'FitToPagesWide'=>$vtpages, 
    'FitToPagesTall'=>$vtpages,
    'Zoom'=>$vtfalse,     
    'PrintGridlines'=>0,
    'LeftHeader'=> "REPORT #DW2009A\rSummary Place of  Service",
    'CenterHeader' => "Empty",
    'RightHeader' => "Test Report",    
    'LeftFooter' => "Done",
    'CenterFooter' => "Now",
    'Orientation' => xlLandscape,
    'RightFooter' => "OK",
    'PrintHeadings'=>0,
    'FirstPageNumber'=> xlAutomatic,
    'PrintTitleRows' => "1:1");

    [download]

    C-.

    Update: Upon further investigation, I found that I *had* include an example of fitting a sheet to one page in the tutorial (including the 'Zoom'), but *hadn't* used VT_I4 types in the FitToPages(Wide|Tall) properties.

[reply]
[d/l]
      Ok, here's me being thickheaded. I want to make sure that you ran that code through a web-server. The code runs fine from the command line -- but it dies going through IIS. If I remove this section, it runs fine. However, on XP, using IIS 5.1, it runs fine. I'll definitely try using the variants, thank you both for your suggestions!
[reply]
        yup.

        I have an application that does what you asked and then some. I changed some settings in my test environment to match your question, and processed an (.xls) doc COMPLETELY through a web interface.

        C-.

[reply]

Back to Seekers of Perl Wisdom