Can't you just display the xlDialogPrint once and use it to set the defaults, or copy the values set from it? If the dialog doesn't set the defaults, you have to make up the list of default values, invoke the print dialog for the first file, copy the values set through the print dialog, and then feed the printing values from your program instead of the dialog every time.

But as you don't show the loop in which you actually ask the user and then print the things, and as I don't have Excel (or the time to play with it) on this machine, it's hard to know for sure unless you provide more information or try it yourself.

Thank you for your response Corion. And I wish it were (is it?) that simple. The dialog doesn't set the defaults for each workbook (report). If I call the dialog for only the first workbook/report, the settings are applied to only that workbook. The remaining workbooks are not affected. I would love to be able to get the values from the print dialog if anyone knows how to do this. The other issue would be *when/if* i get these values, is passing them to the next workbook/report. I see no functions/properties that allow me to change things like single/double sided, layout type, resolution, etc. The OLE function PrintOut only allows you to set the following properties (Taken from MS Object Browser):

Sub PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [Prin
+tToFile], [Collate], [PrToFileName])
[download]

As for the actual loop that prints the reports, see below.

#   Print the report
sub on_winReports_btnPrint_clicked
{
    my ($btn, $win) = @_;
    my $self = $win->{GUI};
    
    my $tree  = $self->get_widget('winReports_treeView');
    my $model = $tree->get_model;
    
    my $period = $self->get_period;
    return unless $period;
    
    my $criteria = $self->get_criteria;
    my $dialog = 1;
       
    $model->foreach
    (
      sub
      {
        my ($model, $path, $iter) = @_;
        my $class  = $model->get($iter, CLASS_COL);
        my $active = $model->get($iter, ACTIVE_COL);
        my $header = $model->get($iter, HEADER_COL);
        if ($active && !$header)
        {
            my $options  = $self->get_options($class);
            my $report = GTIMs::Report->new($class, {Period => $period
+, Criteria => $criteria, Options => $options});
            $report->create;
            $report->print($dialog);
            $report->destroy;
            $dialog = 0;
        }
      }
    );
}


#### HERE IS THE NEW PRINT FUNCTION IN GTIMs::Report #####
sub print
{
    my ($self, $dialog) = @_;
    if ($dialog)
    {
        my $dlg = $self->{Excel}->Dialogs(xlDialogPrint);
        my $result = $dlg->show;
    }
    else
    {
        $self->{Document}->PrintOut();
    }
}
[download]

As you can see the dialog variable determines whether or not the print dialog will be displayed. Another note that may be important, is that the excel xlDialogPrint object actually prints the active workbook (or doesn't, depending on the user's response) so no call to PrintOut is required. A subsequent call to PrintOut on the same workbook keeps the setting set by xlDialogPrint.

A workaround to my problem (which is a path I would rather not go down) is that each report could use the same workbook. So the program would create a workbook, create the first report, prompt the use for the print settings, then delete each worksheet in the book, create the next report, and issue the PrintOut command, and then delete each worksheet, etx.. for all subsequent reports. Reasons I do not want to do this is

1) It exposes the underlying interface to the report modules

2) It relies on the user to interact with underlying interface. The user would have to create the Excel and Workbook objects, and pass them to each report. Besides wanting this to be transparent to the user, if I decide that I don't want to use Excel anymore, and instead write my reports in HTML, old code would break (or would be throwing around OLE objects for no good reason).

3) The seems as though this should be trivial - and *hopefully* the only reason it is not is due to my ignorance of programming Microsoft applications compounded by the idiosyncrasies programming Microsoft applications.

From a cursory browsing of the various Excel dialogs, maybe dialog 9, xlDialogPrinterSetup can help. I think there is/was a way to clone the printer settings from one document to the next, but I don't find the code I think I had to do that, and the Googles fail me as well.

Have you considered the idea of writing a macro (or several macros) to change the printer settings?

From Win32::OLE you should be able to execute it before printing.

I'm pretty familiar with Excel but I haven't done the sort of thing you're attempting with OLE so this answer is on a purely conceptual level.

Once you have created the first workbook, popped up the printer dialog and collected the user's print preferences, they are then set for that workbook, correct? You then print, destroy the workbook and create a new one without the print settings you want.

Why not clear out your data from the workbook rather than destroying it? You could then load up new data, set new title, etc. and print the "same" workbook again using the same print settings.