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

All i want to be able to do is see if #Excel is open and if so save all open workbooks and close Excel.

The code I have 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;
$Win32::OLE::Warn = 3;
my $dir = "G:\\directory\\";
my $file ="myfile.xls";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');
$count = $Excel->Workbooks->Count."\n";
for my $n (1..$count){
print $Excel->Workbooks($n)->Name."\n";
if ($file eq $Excel->Workbooks($n)->Name){
my $book = $Excel->Workbooks($n);
$book->Workbook->Activate();
$Excel->Workbooks($n)->Close();
}

}

Replies are listed 'Best First'.
Re: Save Excel workbooks and close Excel
by CountZero (Bishop) on Mar 09, 2009 at 21:42 UTC
    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

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

Back to Seekers of Perl Wisdom