Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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

by justanyone (Acolyte)
on Jul 27, 2002 at 15:49 UTC ( [id://185754]=perlquestion: print w/replies, xml ) Need Help??

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

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

Originally posted as a Categorized Question.

Replies are listed 'Best First'.
Re: Win32::OLE Excel PrintOut() hash params???
by justanyone (Acolyte) on Jul 30, 2002 at 14:56 UTC
    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<<<

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://185754]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (5)
As of 2024-03-29 14:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found