Hello Monks!

This is one that I'm sure people have faced before. And the answer may be within the monastery but I don't know exactly what to search for. The program below is iterating through a list of files, opening them, modifying a column or two, then doing a SaveAs. The problem, I think, is that it is iterating through the list too fast. That is, I think that Excel may not be fully closed and the process closed out before the next open is issued and thus the program is stepping on itself.

I have come across hints on what to do for undefined inputs and runaway leaks but nothing about program flow control. In one example, I got the following error on the terminal window:

 

D:\Quarterly Analyses\Scripts>perl File_rename_sw.pl   
Working on D:\Downloads\CI_Radar_watch\KeywordExposure-Box May SW.xls 
+  
Working on D:\Downloads\CI_Radar_watch\KeywordExposure-Catalogic May S
+W.xls 
Can't call method "Activesheet" on an undefined value at File_rename_s
+w.pl line 130.
[download]

the output above - I interpret as follows:

program invoked

First file processed

Second file processed

Third file failed

I presume because the program stepped on itself. It seems inelegant to put a hardcoded delay into the program. How do I get this thing to pace itself?

The full program is below. I set it to call OLE as a sub routine at line 83 and it is in that loop where I think the problem is happening. Thoughts?

#!/usr/bin/perl

use strict;
use warnings;
use Time::Local;
use File::Copy;
use Win32::OLE::Const 'Microsoft Excel';
use File::Basename;

my $sec;
my $min;
my $hour;
my $mday;
my $mon;
my $year;
my $wday;
my $yday;
my $isdst;
my $index;
my $file;
my @files;
my $new_file;


my @sw_competitors = (
    "Alfresco",
    "Amazon",
    "Box",
    "Catalogic",
    "Cohesity",
    "CommVault",
    "Delphix",
    "EMC",
    "Google",
    "Hyland",
    "IBM",
    "Lexmark",
    "Microsoft",
    "NetApp",
    "OpenText",
    "Primary",
    "Unitrends",
    "Veeam",
    "Zerto",);
my @self = qw(EMC);


#     0    1    2     3     4    5     6     7     8
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time
+);

# All list elements are numeric and come straight out of the C `struct
+ tm'. $sec , $min , and $hour are the seconds, minutes, and hours of 
+the specified time.
# $mday is the day of the month and $mon the month in the range 0..11 
+, with 0 indicating January and 11 indicating December. This makes it
+ easy to get a month name from a list:

my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
# $mon=9, $mday=18 gives "Oct 18"

# $year contains the number of years since 1900. To get a 4-digit year
+ write:
$year += 1900;

my $lastmon = $mon - 1;

if ($mon == 0) {
    $lastmon   = 12;
    $year -= 1;
}

# construct the filename(s) to change

# KeywordExposure-<competitor> (Competitor)-$mon $year*
# KeywordExposure-<EMC> (Owner)-$mon $year*
# WeightedMarketExposure-AllKeywords-$mon $year*

foreach $index (@sw_competitors)
{
  @files = glob ("'D:\\Downloads\\CI_Radar_exports\\KeywordExposure-'*
+${index}*.xls");

  foreach $file (@files) 
  {
    if (-e $file) 
    {
    $new_file =  'D:\\Downloads\\CI_Radar_watch\\KeywordExposure-' . $
+index . " " . $months[$lastmon] . " SW.xls";
    move ($file, $new_file);
    Excel($new_file);    
    } else  {
      print "$index file not found: $@ \n";
      }
  }
}
 
$index = "All";
@files = glob ("'D:\\Downloads\\CI_Radar_exports\\WeightedMarketExposu
+re-'*${index}*.xls");
  foreach $file (@files) {
    if (-e $file) 
    {
    move ($file,'D:\\Downloads\\CI_Radar_watch\\WeightedMarketExposure
+-' . $index . " " . $months[$lastmon] . " SW.xls"); 
    Excel($file);   
    } else  {
      print "$index file not found: $@ \n";
      }
         }
         
sub Excel {
my $dir;
my $file;
my $new_file;
my @files;
my $Excel;
my @list_Sheet;
my $workbook;
my $name;
my $path;
my $suffix;
my $fullname;
my $suffixlist;
my $last_col;
my $last_row;
my $Sheet;

($file) =  @_;
$Win32::OLE::Warn = 3; # die on errors
$dir = 'D:\\Downloads\\CI_Radar_watch\\';

print ("Working on $file \n");
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application');

$workbook = $Excel->Workbooks->Open($file);
# @list_Sheet = map { $_->{'Name'} } (in $workbook->{Worksheets});
# $Sheet = $workbook->Worksheets($list_Sheet[0]);
$Sheet = $workbook->Activesheet;

$last_col = $Sheet -> UsedRange -> Find({What => "*", SearchDirection 
+=> xlPrevious, SearchOrder => xlByColumns}) -> {Column};
$last_row = $Sheet -> UsedRange -> Find({What => "*", SearchDirection 
+=> xlPrevious, SearchOrder => xlByRows})    -> {Row};

$Sheet->Cells(1, $last_col + 1) -> {Value} = "Month";

  ($name, $path) = fileparse($file);
  $new_file = $path . "new " . $name;   
$workbook->saveAs($new_file,{FileFormat => 51}); #xlWorkbookDefault
$workbook->Close();
     
$Excel->Quit();

} 

sub DESTROY {
  my $s = shift;
  if (exists $s->{Ex}) {
     print "# closing connection\n";
     $s->{Ex}->Quit;
  return undef;
            }
        }
[download]