Beefy Boxes and Bandwidth Generously Provided by pair Networks DiBona
There's more than one way to do things
 
PerlMonks  

Win32::OLE Excel PrintOut() hash params???

( #185754=categorized question: print w/ replies, xml ) Need Help??
Contributed by justanyone on Jul 27, 2002 at 15:49 UTC
Q&A  > programs and processes


Description:

Howdy! I want to print out an Excel spreadsheet using Win32::OLE. I want to set the printout to print (a) the entire workbook and (b) select a non-default printer. I need to know the names of the hash elements to the ActiveSheet.PrintOut() function to tell it to select a specific printer, and to 'print entire workbook'. Any Clues are VERY welcome!

So far, I have the following code:

use Win32::OLE qw(in); use Win32::OLE::Const; my $Class = "Excel.Application"; my $File = "c:\\web\\apache\\cgi-bin\\testfile1.xls"; my $Excel = Win32::OLE->GetActiveObject( $Class ); if (!($Excel)) { $Excel = new Win32::OLE($Class, \&QuitApp ) || die "Could not cretate an OLE '$Class' object"; } $Excel->{Visible} = 1; my $Book = $Excel->Workbooks->Open( $File ); # open the file my $count = $Book->Worksheets()->{Count}; print("There's $count worksheets in $File.\n\n"); $Book->ActiveSheet->Activate(); my @thing = $Excel->ActiveSheet->PrintOut( { PrintToFile => 1, PrToFileName => "c:\\temp\\ps_output\\test1.file", Copies => 1 }); $Book->{Parent}->Quit();
PLEASE if you know where to find the clues to solve this Microsoft-induced trauma, please post them?

cordially, -- Kevin Rice

Answer: Win32::OLE Excel PrintOut() hash params???
contributed by justanyone

The Answer was found in the O'Reilly Text, "Writing Excel Macros with VBA".

The solution turned out to be:

  • to invoke PrintOut as a method of the entire workbook, as in $Book->PrintOut(). this prints all the sheets. If you want to print just the current sheet, invoke $Book->ActiveSheet->PrintOut().
  • To print to a file, as above, note that the two params above work as advertised, but will NOT work with Excel 97, only Excel 2000 and Excel 2002 (in Office XP). Further, in Excel 2000, the file it produces (viewed as PDF using free tool ghostscript) shows one or more pages printed sideways UGGGGG as landscape. Excel XP / Excel 2002 knows better when printing to file and prints the whole thing, including the image, right side up as we would expect and hope.
  • To set the active printer from Excel PrintOut hash, the hash element is ActivePrinter. To find the active printer's description text (important because you won't guess it unless you've seen a couple of them), invoke:
    my $pobj = $excel_obj->ActivePrinter; if ($pobj) { print("Name of default printer =>>$pobj<<\n"); }
    then to print to a non-default printer, use the hash key of "ActivePrinter" and the value of a string you retrieved above.
  • Note that if you want postscript output, you have to print to a postscript printer. That doesn't mean that you have to own such a printer, just that you have it defined as a valid print queue (use Start->Settings->Printers->add printer to add one - I like HP Laserjet 5/5m PS, it's color). Then Excel knows what kind of printer to produce a byte stream for (which is postscript, so everyone's happy).
  • The whole thing comes out to be as follows:
    print("Starting to convert...\n"); my $classname = "Excel.Application"; my $excel_obj = Win32::OLE->GetActiveObject( $classname ); if (!($excel_obj)) { print("no active object, creating new one...\n"); $excel_obj = new Win32::OLE($classname, \&QuitApp ) || die "Could not cretate an OLE '$classname' object"; print("Created.\n"); } else { print("Already existed.\n"); } if (!($excel_obj)) { print("Error during get/create of Excel object. returning 0."); return 0; } print("Proceeding...\n"); $excel_obj->{Visible} = 0; # EnableEvents=0 (on by default) disables (hopefully) # dialog boxes that # are waiting on user input, which is good for this program running # unattended as it hopefully will. $excel_obj->{EnableEvents} = 0; # note that open() takes params like: # open(filename, UpdateLinks, ReadOnly...) # See the O'reilly Excel with VBA book on this. my $Book = $excel_obj->Workbooks->Open( $infilename, 0, 1 ); # above line opens the file print("Opened file $infilename...\n"); if (!($Book)) { print("Error during open, filen=$infilename. returning 0."); return 0; } my $count = $Book->Worksheets()->{Count} || 0; print("$count worksheets are in Excel spreadsheet $infilename.\n\n"); my $pobj = $excel_obj->ActivePrinter; if ($pobj) { print("Name of default printer =>>$pobj<<\n"); } my @thing = $Book->PrintOut( { PrintToFile => 1, PrToFileName => $outfilename, ActivePrinter => "PS_FILE_OUTPUT on Ne00:", Copies => 1 }); ########################################### # note: following works on my Office 2000 and # Office XP, but not Excel 97. # PrintToFile => 1, # PrToFileName => $outfilename, ############################################## # Note: if you want a black and white PDF, # print to a black and white printer. ############################################# # printer options: # black and white: ActivePrinter => '\\NETPRINTSRV\p21 on Ne01:', # color: ActivePrinter => "PS_FILE_OUTPUT on Ne00:", # default printer: ActivePrinter => $pobj, # Kevin Rice's home: ActivePrinter => "PrinterPS5m on LPT2:", ########################################### $Book->Close(0); #$Book->{Parent}->Quit();
    I'm going to be releasing this as a perl module. I'm writing this as of July 30, 2002. If you don't see the above code in a module by Sept.1, 2002, feel free to create a module yourself. Enjoy! -- Kevin Rice kevin "at" -justanyone 'dot' >>>com<<<

Please (register and) log in if you wish to add an answer



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others surveying the Monastery: (7)
    As of 2014-04-17 08:32 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      April first is:







      Results (441 votes), past polls