in reply to MS Excel 2007 never dies - or at least the process wont

First of all, it puzzles me greatly why you are ordering Excel to Quit(), and then waiting 5 seconds (I suppose) for it to have time to do so.   Once Excel has been started, knowing this to be an expensive process, you want it to stay active for the duration.

But, “OLE knows how to do that already.”   The OLE subsystem, not your requesting process, is the party that should be made solely responsible for starting the service process (Excel, in this case), and for ending it.   This is done in part, I think, because of clients that otherwise would, as your program does, constantly ask the hosting program to quit.

I don’t see any evidence that you actually defined a Quit() destructor ... and this, technically, is probably the reason why it isn’t quitting ... but my point is that you don’t want it to quit every time, as you are now praying for it to do.

By far the easiest way to approach OLE is at the document level, not the application level.   (It is my crystalline opinion that this is what the architects had in mind.)   Unless you specifically need to control the visibility of the app to the user, you want to be talking to Excel.Spreadsheet, or what-have-you, not the Excel.Application that, you happen to know, is what drives “spreadsheets.”   Ask for what you want to manipulate, and address your supplications to those things, not the application itself.   OLE can, and will, determine and launch and manage those application instances on your behalf, and it will do so much more efficiently than you are, in the present case, attempting to do.

Replies are listed 'Best First'.
Re^2: MS Excel 2007 never dies - or at least the process wont
by Anonymous Monk on Aug 25, 2011 at 15:42 UTC
    The wait was just a futile troubleshooting attempt. Thanks to all who provided input regarding structure of the code - rather than the placement of files. The structure as it stands is designed to read more than one file if applicable - so the loop you may be referring to is a loop that should indeed close each worksheet prior to beginning anew. I don't experience this problem on my pc - leading me to believe that the recommendations regarding updating perl surely would help. Unfortunately don't have the option of a blanket update of the various user VDI environments where the code will need to function - or rather the time to accomplish that task. I'll try revising the sequencing of application and workbook calls to see if that helps. I really dont want to manipulate any values within the spreadsheet, only read them and then use ODBC to write to and update DB records.
[reply]

      Closing the file within the loop makes sense. Closing the application within the loop doesn't make sense to me. If there's a reason, I'd be very interested in it.

      Regards,

      John Davies

[reply]

        I've narrowed the sample code down to a more minimally functional set. With any old .xlsx file in the existing work directory, this should demonstrate the conditions of problem I'm experiencing without going more indepth into the code.

        This script - in the environment in question - leaves behind a stray process named "EXCEL.EXE" in task manager.

        From what I have been able to glean from the documentation I've seen - the destructor is defined here new('Excel.Application','Quit')

        use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use FindBin qw($Bin);
$Win32::OLE::Warn = 3;
my $Excel = Win32::OLE->new('Excel.Application','Quit');
my $curpath = "$Bin/";
undef my $j;
 for $j (0 .. length($curpath)){
   if(substr($curpath,$j,1)eq '/'){substr($curpath,$j,1) = '\\';}
 }
undef my $listfile;
$listfile = "filelist.txt";
my $syscmd = "dir \/b *.xlsx > ".$listfile; 
system($syscmd);
close(STDERR);
open(STDERR, ">load_error_log.txt");
open(RDR0, $listfile);
undef my $listread;
while ($listread = <RDR0>){
    chomp($listread);
    my $ExcelFile = $curpath.$listread;
    print "*Loading $ExcelFile*\n";
    my $Book = $Excel->Workbooks->Open($ExcelFile) || die("Unable to o
+pen $ExcelFile ", Win32::OLE->LastError());
    $Book->Close(0);
    undef $Book;
}
$Excel->Quit();
undef $Excel;
close (RDR0);
close (STDERR);

        [download]
[reply]
[d/l]
[select]
        Absolutely with you on that one - I've tried placing the app quit statement within and outside the loop and the problem remains. If anyone has any helpful pointers toward the syntax for creating and calling a destructor that does sound like a promising option. I haven't worked much with OLE and the docs and tutorials I've uncovered have not proven to be very insightful.
[reply]

In Section Seekers of Perl Wisdom