Dear monks, What I have is a program that prints reports using Win32::OLE and MS Excel. The user can select any number of reports that they want to run. The reports are created and printed using Excel. However I can't quite get the print options/behavior that I am looking for.

Once the user presses the <PRINT> button the following code is executed for each report.
my $rpt = GTIMs::Report->new('Schedule::Daily', $args);
$rpt->create;   # populate the workbook with data
$rpt->print;    # print the workbook
$rpt->destroy;  # destroy the workbook

########################
# the report print function simply looks like this
sub print
{
    my ($self, $copies) = @_;
    $copies = 1 unless $copies;
    $self->{WorkBook}->PrintOut(Copies => 1);
}

[download]
Now the issue is, as that printing like this, I can't select the printer to print to and advanced options like double sided printing, resolution, layout type, etc.

Things like paper type and orientation can be set using PageSetup (an example is provided below)
   with ($Sheet->PageSetup,
        LeftHeader => '',
        CenterHeader => '&"Arial,Bold"&16' . $self->{Schedule}{$schedu
+le}{Title} . chr(10) .
                        "&12 $day $self->{Period}{Start}" . chr(10) .
                        "$start - $end",
        RightHeader => '',
        LeftFooter => 'Printed &D &T',
        CenterFooter => '',
        RightFooter => 'Page &P of &N',
        LeftMargin   => $Sheet->Application->InchesToPoints(0.15),
        RightMargin  => $Sheet->Application->InchesToPoints(0.15),
        TopMargin    => $Sheet->Application->InchesToPoints(1.0),
        BottomMargin => $Sheet->Application->InchesToPoints(0.5),
        HeaderMargin => $Sheet->Application->InchesToPoints(0.25),
        FooterMargin => $Sheet->Application->InchesToPoints(0.25),
        PrintHeadings => 0,
        PrintGridlines => 0,
        PrintComments => xlPrintNoComments,
        PrintQuality => 600,
        CenterHorizontally => 1,
        CenterVertically => 0,
        Orientation => xlPortrait,
        Draft => 0,
        PaperSize => xlPaperLetter,
        FirstPageNumber => xlAutomatic,
        Order => xlDownThenOver,
        BlackAndWhite => 0,
        Zoom => Variant(VT_BOOL, 0),
        FitToPagesWide => 1,
        FitToPagesTall => 1,
        PrintErrors => xlPrintErrorsDisplayed,
        PrintTitleRows => '$1:$2',
        PrintTitleColumns => '$A:$D',
    );

[download]
After some searching (I hope the below examples help others in a similar situation) I was able come up with the following code to display the print dialog. 
use constant xlDialogPrint => 8;

sub print
{
    my $self = shift;
    my $dlg = $self->{Excel}->Dialogs(xlDialogPrint);
    my $response = $dlg->Show;
}

[download]
This displays the print dialog, and you can change these settings. Exactly what I want to do. The problem is that it is going to display the print dialog for EVERY report the user wants to print. Which could be potentially be a pretty large number. I want to display the dialog once, and then use the same print settings for each document that I am printing. Does anyone have any insight into this?

In reply to WIN32, OLE, Excel, and Printing by boblawblah

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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:
    & &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.