comment on

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"); }
[download]
```
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();
[download]

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<<<

In reply to Re: Win32::OLE Excel PrintOut() hash params??? by justanyone
in thread Win32::OLE Excel PrintOut() hash params??? by justanyone

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
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, details, 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, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

For: Use:

& &

< <

> >

[ [

] ]
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`