Selecting printer in Excel

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

I want to print an existing Excel spreadsheet using perl.
Following help from Monks this week, I found that this can be done using the following Perl

$excel = CreateObject OLE "Excel.Application";
$workbook = $excel -> Workbooks -> Open(file name);
$workbook->PrintOut();
[download]

However, I want to select the printer so that the system's default printer is not used.
I recorded an Excel macro to do this and got the following VBA

Application.ActivePrinter = _
        "Auto HP Deskjet 6500 Series on HOMEDELLTOWER on Ne02:"
    ExecuteExcel4Macro _
        "PRINT(1,,,1,,,,,,,,3,""Auto HP Deskjet 6500 Series on HOMEDEL
+LTOWER on Ne02:"",,TRUE,,FALSE)"
[download]

I guess the first line may in Perl be

Application->ActivePrinter( "Auto HP Deskjet 6500 Series on HOMEDELLTO
+WER on Ne02:");
[download]

But I am not at all sure. I do not know how to start with the second line.
I am using Excel 2007 on an XP Windows Professional PC.
Can any Monk help with this?

Comment on Selecting printer in Excel Select or Download Code

Replies are listed 'Best First'.
Re: Selecting printer in Excel by tilly (Archbishop) on Jan 13, 2011 at 20:45 UTC
Based on http://www.xav.com/perl/faq/Windows/ActivePerl-Winfaq12.html#set_printer I believe that what you need to do is `$workbook->{ActivePrinter} = "Auto HP Deskjet 6500 Series on HOMEDELLT +OWER on Ne02:";` [download] An alternate approach you might try based on Win32::OLE Excel PrintOut() hash params??? is to modify the PrintOut line to be: `$workbook->PrintOut({ActivePrinter => "Auto HP Deskjet 6500 Series on +HOMEDELLTOWER on Ne02:"});` [download] Both of these suggestions are untested as I don't have Windows and have not touched OLE in many years.	[reply] [d/l] [select]
Re: Selecting printer in Excel by Khen1950fx (Canon) on Jan 13, 2011 at 20:52 UTC
I think that you want something like this: `my $pobj = $excel_obj->ActivePrinter; if ($pobj) { print ("Name of default printer => $pobj \n"); }` [download] You can find a complete example here.	[reply] [d/l]
Re^2: Selecting printer in Excel by merrymonk (Hermit) on Jan 14, 2011 at 10:56 UTC
Thank you for both replies – they have let me do what I want The test Perl is below use strict ; use OLE; use Win32::OLE::Const "Microsoft Excel"; my ($excel, $workbook, $sheet, $file_name, $pobj); $file_name = "C:\\Bookxxx.xls"; $excel = CreateObject OLE "Excel.Application"; $workbook = $excel -> Workbooks -> Open($file_name); $pobj = $excel->ActivePrinter; if ($pobj) { print "start - Name of default printer => $pobj \n"; } $excel->{ActivePrinter} = "Auto HP Deskjet 6500 Series on HOMEDELLTOWE +R on Ne02:"; $pobj = $excel->ActivePrinter; if ($pobj) { print "after setting - Name of default printer => $pobj \n"; } $workbook->PrintOut(); [download] Typical output from an MSDOS screen is C:\\test-exprinter.pl start - Name of default printer => deskPDF on DDM: after setting - Name of default printer => Auto HP Deskjet 6500 Series on HOMEDELLTOWER on Ne02: The process is that 1. You set the default printer to the one you want to use 2. Use the Perl 3. Use the output to get the name of the printer 4. Set the default printer to what it should be 5. Add the found printer name into the Perl code You should then be able to choose the printer you want to use	[reply] [d/l]