Excel process created with Win32::OLE doesn't quit when expected

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

I run an Excel process as a scheduled task on a W7 PC. I think that during a recent MicroSoft Office update there was a change to Excel. I used to be able to open a new Excel process with:

use Win32::OLE qw(in with);
my ($ex, $book, %WS);

my $Exname="$ENV{USERPROFILE}\\Documents\\ABC.xls";

# Always use a new instance of Excel so as to not interfere with anyth
+ing open on the desktop

$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
                    or die "Oops, cannot start Excel";
# Set Application Visibility; 0 = Not Visible, 1 = Visible
$ex->{'Visible'} = 0; # 'Not Visible' is the default, but set it anywa
+y

$ex->{DisplayAlerts}=0; 
$book = $ex->Workbooks->Open($Exname); 

$WS{$book->Worksheets(1)->{name}}=$book->Worksheets(1);

#############################
#                           #
#    Work happens here      #
#                           #
#############################

$book->Close;
Win32::OLE->Uninitialize;
undef $book;
undef $ex;
[download]

After a recent Windows Update, the Open($Exname) fails unless

$ex->{'Visible'}=1;

I changed the 'Visible' state to 1 and also added these two lines before the Open($Exname):

use constant xlMinimized => -4140;
$ex->{'WindowState'} = xlMinimized; # the window will be momentarily v
+isible anyway
[download]

This works and is almost satisfactory (the Excel window flashes the screen very briefly), but now sometimes the Excel process remains as an orphan process after the Perl script has ended. I tried putting a substantial sleep time after the

$book->Close;

but it didn't help. Has anyone else seen this? Any other suggestions to try? This is perl, v5.8.9 built for MSWin32-x64-multi-thread

Thanks

Comment on Excel process created with Win32::OLE doesn't quit when expected Select or Download Code

Replies are listed 'Best First'.
Re: Excel process created with Win32::OLE doesn't quit when expected by davies (Monsignor) on Mar 04, 2016 at 12:57 UTC
Unless you're doing something that needs the memory after your `$book->Close`, I would advise replacing your four lines `$book->Close; Win32::OLE->Uninitialize; undef $book; undef $ex;` [download] with `$ex->Quit`. I've never encountered the "Uninitialize" method and don't know what it does. But quitting Excel will close the book automatically, while your objects will automatically be undefined when the Perl programme exits. I think that will solve your problem. Two more tips, though. If you add `use Win32::OLE::Const 'Microsoft Excel';` to your use statements, you will get xlVisible, xlMinimised and all the rest. I've never seen the `sub {$_[0]->Quit;}` construct before. I've seen things very like it, but I've never used them myself as I've never run into situations where they have seemed necessary. It's unlikely to cause a problem if you know why it's there, but unless it solves a problem (and you're dying on the same line, which should cope), you might prefer to take it out. Regards, John Davies	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Excel process created with Win32::OLE doesn't quit when expected
by davies (Monsignor) on Mar 04, 2016 at 12:57 UTC

Unless you're doing something that needs the memory after your $book->Close, I would advise replacing your four lines

$book->Close;
Win32::OLE->Uninitialize;
undef $book;
undef $ex;
[download]

with $ex->Quit. I've never encountered the "Uninitialize" method and don't know what it does. But quitting Excel will close the book automatically, while your objects will automatically be undefined when the Perl programme exits.

I think that will solve your problem. Two more tips, though. If you add use Win32::OLE::Const 'Microsoft Excel'; to your use statements, you will get xlVisible, xlMinimised and all the rest.

I've never seen the sub {$_[0]->Quit;} construct before. I've seen things very like it, but I've never used them myself as I've never run into situations where they have seemed necessary. It's unlikely to cause a problem if you know why it's there, but unless it solves a problem (and you're dying on the same line, which should cope), you might prefer to take it out.

Regards,

John Davies

[reply]
[d/l]
[select]