in reply to Re^4: MS Excel 2007 never dies - or at least the process wont
in thread MS Excel 2007 never dies - or at least the process wont

You don't tell us what you're really trying to do. It appears that you are trying, in this code, to read every file in a directory that has a certain extension (I use 2002 so I have a different extension in a variable) and print its name. I have based the following code, which works for me, on Re^5: Win32::OLE Excel search and replace commas.

use strict; use warnings; use Cwd; use Win32::OLE; my $ext = ".xls"; my $Excel=Win32::OLE->new('Excel.Application'); $Excel->{Visible}=1; $Excel->{DisplayAlerts}=1; #Set to 0 when the code is working, but +keep at 1 while debugging. my $dir = getcwd; $dir =~ s/\//\\\\/g; #Sort out the slashes and backslashes opendir(DIR, $dir) or die "can't opendir $dir: $!"; #Cookbook recipe 9 +.5 while (defined(my $file = readdir(DIR))) { if ((!-d $file) and (substr($file, -length($ext)) eq $ext)) { +#Someone WILL call a directory something.xls. I have the scars. my $Book = $Excel->Workbooks->Open("$dir\\\\$file") or die "Ca +n't open file $dir\\\\$file"; print $Book->FullName . "\n"; $Book->Close; } } closedir(DIR); $Excel->Quit;

The output in my command window is:

Z:\Data\Perl>922394.pl Z:\Data\Perl\WhichMonk.xls Z:\Data\Perl>cd\excel\sudonkey Z:\Excel\Sudonkey>\data\perl\922394.pl Z:\Excel\Sudonkey\SuDonkey2.xls Z:\Excel\Sudonkey\SuDonkey.xls Z:\Excel\Sudonkey\SuDonkey1_0_3.xls Z:\Excel\Sudonkey\InPlay.xls Z:\Excel\Sudonkey>

I am left with no orphan instance of Excel. It works on any directory. Please try this. If it leaves an orphan instance, then it's down to a 2007 parameter and without a copy, I can't help you further.

Regards,

John Davies

Replies are listed 'Best First'.
Re^6: MS Excel 2007 never dies - or at least the process wont
by otism (Initiate) on Aug 25, 2011 at 22:24 UTC

    I did actually - apologies but I'm more than a bit new to the forum posting and my responses aren't coming into alignment properly. b This anonymous post was me:

    "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."

    I'll give that code logic a shot and see what it does for the problem. I'm guessing not much but have also gotten the request submitted to update the perl version in the VDI.

    If that works I'll just include that package installation as a requirement for anyone designated to run the updates.

    Thanks a lot for your help John!