I have solved the problem.
Workbooks(index).close() reindexes the files that are open so if you have 14 files open it closes 7 then has a index issue. naturaly it would if it reindexes the files as they are being closed. The best way to programatically close all open Excel files is like this
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::Enum;
use Win32::OLE::NLS qw(:LOCALE :DATE);
$Win32::OLE::Warn = 3;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$count = $Excel->Workbooks->Count;
print "$count\n";
my @list = ();
# get all names and paths and put them in an array
for my $n (1..$count){
my $name = $Excel->Workbooks($n)->Name;
my $path = $Excel->Workbooks($n)->Path;
push @list, [$path, $name];
}
# loop through the list and close each file by name
for my $n (0..$#list){
my $fn = join("\\", @{$list[$n]});
$Excel->Workbooks($list[$n][1])->Close(1, $fn, 0);
}
$Excel->Quit();
|