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

Hi,

The following code prints fine for me.

$file = "/Users/me/Desktop/test.txt";
$printer = "HP_DESKJET_930C";
system ("lp -d $printer $file");

[download]

When I ask to print an Excel file with the following code, I get a command line message indicating that a print request has been made; however, I get nothing out of the printer.

$file = "/Users/me/Desktop/test.xls";
$printer = "HP_DESKJET_930C";
system ("lp -d $printer $file");

[download]

Without opening (running) the Excel application first, but rather merely selecting that test.xls file and then "Print" from Menu seems to require Excel to open first before the file can print. (In fact, on this Mac, a print dialog is opened, too, and I have to click a "Print" button -- as if my first request wasn't good enough. At least under Windows one request is adequate.)

If I drop another line into my code (below), hoping to open Excel and test.xls before printing it, the code exits without a command line indication that a print request has been made. (Presumably, my 'if' statement is returning false.) After exiting, the operating system finally gets around to opening Excel and my file, but -- too late! -- no print command is issued.

$file = "/Users/me/Desktop/test.xls";
$printer = "HP_DESKJET_930C";
if (system ("open $file")) {
 system ("lp -d $printer $file"); }

[download]

How do I get my program to hang out and twittle thumbs till Excel might be ready to accept a print call?

Thanks. --cypress

Replies are listed 'Best First'.
Re: need program to hang out awhile?
by JavaFan (Canon) on Dec 02, 2009 at 18:49 UTC
    What happens if you run lp -d HP_DESKJET_930C /Users/me/Desktop/test.xls from the command line? Does it appear in the queue? (lpstat/lpq). Are you sure your print server is set up to be able to deal with raw xls files?

    It doesn't seem you have a Perl problem.

[reply]
[d/l]
Re: need program to hang out awhile?
by Marshall (Canon) on Dec 03, 2009 at 00:13 UTC
    Unfortunately there doesn't appear to be a simple -print command line option for Excel. Somehow you need to control Excel and tell it to print (there will be "print areas" and such to be dealt with which I don't think a simple thing looking at a .XLS file will be able to do).

    I found this .vbs script that can be used like: wscript "C:\Data\My Scripts\printXLS.vbs" "C:\Data\My Reports\Daily Analyser Excursion Report.xls". There are Perl modules that can control Excel and Word via OLE environment, but I don't know how to use them. Anyway check this out for at least one idea of how to proceed...If all else fails, you could call this thing from Perl. I haven't run this code myself but Poster claims that it works and it does seem applicable to what you want to do. Good Luck!

    http://blog.idxonline.com/2009/08/scheduling-printing-of-excel-file-which.html

[reply]
Re: need program to hang out awhile?
by ww (Archbishop) on Dec 02, 2009 at 20:38 UTC
    ..."Print" from Menu seems to require Excel to open first before the file can print

    So, (Not the course suggested by your question, but following up on JavaFan's observation re the printer's capabilities) how about using one of the Spreadsheet::... modules to get at the data, and (if necessary), expand your script to print that data from memory; or to convert it to a tsv which seems likely to be readily printable?

[reply]
Re: need program to hang out awhile?
by tokpela (Chaplain) on Dec 03, 2009 at 10:01 UTC

    Use Win32::OLE to control Excel and print from within Excel.

    I used the code below several years ago to print out an Excel sheet every day. Please note - this code is a snippet and is untested as is.

    Hopefully, you can get the idea and adapt to your situation. 

    use strict;
use warnings;

use Win32::OLE qw(in with);
use Win32::OLE::Variant;

use Win32::OLE::Const 'Microsoft Excel';

$Win32::OLE::Warn = 3;

my $excel          = Win32::OLE->GetActiveObject('Excel.Application') 
                               || Win32::OLE->new('Excel.Application',
+ 'Quit');

my $book           = $excel->Workbooks->Open("I:\\Path\\To\\File\\file
+.xls");

my $sheet         = $excel->Worksheets(1);

#Sub PrintOut([From], [To], [Copies], [Preview], [ActivePrinter], [Pri
+ntToFile], [Collate])
$sheet->PrintOut(1,1,1,0,"HP LaserJet IIIP",0,0);

$book->Save;
$book->Close;
  
print "Done\n";

    [download]
[reply]
[d/l]

      Hi,

      OLE might be a promising trail to follow, but unfortunately I'm on a Mac for this project.

      For a related Windows project, however, I'd like to see your PrintOut sub definition in full.

      Continued thanks,

      cypress

[reply]

        The PrintOut() method is defined somewhere inside MS Excel :-)

        Jenda
        Enoch was right!
        Enjoy the last years of Rome.

[reply]

Back to Seekers of Perl Wisdom