in reply to Save Excel workbooks and close Excel

It is easy as this: 
use Win32::OLE;
$Win32::OLE::Warn = 3;
my $file  = "myfile.xls";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') or die 'E
+xcel not running';
for my $n ( 1 .. $Excel->Workbooks->Count) {
    print $Excel->Workbooks($n)->Name , "\n";
    $Excel->Workbooks($n)->Close(1, $file, undef);
}
$Excel->Quit();

[download]
Attention: this will close all workbooks in the running Excel application and save any changes. If the workbook has not been saved yet, it will be saved as whatever is in $file. Multiple unsaved workbooks will thus all get saved under the same name, actually deleting all but the last one (I think, didn't test it).

BTW: Please put your code between <code> ... </code> tags. It is much easier to read and we can then download it.

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

Replies are listed 'Best First'.
Re^2: Save Excel workbooks and close Excel
by blenkhn (Acolyte) on Mar 09, 2009 at 21:56 UTC
    Awesome Thanks very much What is the "undef" for?
[reply]
      I got this straight from the Excel VBA help:
      expression.Close(SaveChanges, Filename, RouteWorkbook)
      By setting the "RouteWorkbook" to undef the WorkBook will not be routed (whatever that may be). Perhaps I could have used "0" or left this out altogether.

      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]
        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]
[reply]
[d/l]

In Section Seekers of Perl Wisdom