Re^4: Save Excel workbooks and close Excel

Thanks very much for your input. I have come across a different problem. if I have 3 files open I get an invalid index error on the third.

my code is as follows


#!/usr/local/bin/perl

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);
use Carp::Heavy;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel::Simple;
$Win32::OLE::Warn = 3;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
            || Win32::OLE->new('Excel.Application', 'Quit');
$count =  $Excel->Workbooks->Count;
print "$count\n";
for my $n (1..$count){
        my $name = $Excel->Workbooks($n)->Name;
        my $path = $Excel->Workbooks($n)->Path;
        my $fn = $path."\\".$name;
        print "$n $fn\n";
        my $state = $Excel->Workbooks($n)->Close(1, $fn, 0);
        print "$state\n";
}

$Excel->Quit();
[download]

Comment on Re^4: Save Excel workbooks and close Excel Download Code

Replies are listed 'Best First'.
Re^5: Save Excel workbooks and close Excel by CountZero (Bishop) on Mar 10, 2009 at 05:54 UTC
There is no problem if you start closing the workbooks "from the top down" instead as "from the bottom up". Just replace `for my $n ( 1 .. $Excel->Workbooks->Count)` [download] by `for my $n ( reverse 1 .. $Excel->Workbooks->Count)` [download] CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply] [d/l] [select]
Re^5: Save Excel workbooks and close Excel by blenkhn (Acolyte) on Mar 09, 2009 at 23:55 UTC
Is it possible that Excel reindexes the files as they are being closed? I opened 14 files and the first time I ran it it closed exactly 1/2 of them and it happened until they were all closed	[reply]
Re^6: Save Excel workbooks and close Excel by blenkhn (Acolyte) on Mar 10, 2009 at 00:38 UTC
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(); [download]	[reply] [d/l]