Hi there, This is the first time I've asked a question here so bare with me. I have a perl script that opens an excel spreasheet and runs a macro. The macro works in Excel but not when I run it from Perl. Can someone tell me why? Here is the code I am using :

#!/usr/bin/perl -w

use strict;
use warnings;
use Cwd;
use File::Find;
use File::Copy;
use Win32::OLE;

# Switch off warnings and pop-ups, I hope.
#
$Win32::OLE::Warn = 0;


my $dir ='C:\Documents and Settings\cwj001\Desktop\dataFiles';
my $CWD = ' ';

$CWD = $dir;  # just like chdir($dir)!
     print "$CWD \n";   # prints the current working directory

my $file_counter = 0;
my $filesUpdated = 0;
my $match_counter = 0;
##my $macro = 'PERSONAL.XLSB!TestAddRow1';
my $macro = 'TestMacro1';


######################################################################
+#
## Input your search string: Use (?i) to search case insensitive.
##  Follow this immediately by your<seacrh string>
######################################################################
+#
##my $search_pattern=$ARGV[0];
my $search_pattern='(?i)Agent District Code';

######################################################################
+#
## Input a particular file extensions you want to search.
######################################################################
+#
##my $file_pattern  =$ARGV[1];
my $file_pattern  ='xls';



# Start Excel
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
  || Win32::OLE->new('Excel.Application', 'Quit');
$excel->{'Visible'} = 1;
$excel->{'DisplayAlerts'} = 0; 
  
# Open Macro Workbook. 
##my $macros = $Excel->Workbooks->Open('C:\Documents and Settings\cwj0
+01\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB'); 

# Loop through directory and execute the macro on each applicable file
find(\&d, $CWD);

#Cleanup
##$macros->Close();
$Excel->Quit();

print "\nTotal files searched: $file_counter\n";
print "Total matches found : $match_counter\n";
print "Total files updated : $filesUpdated\n";
exit 0;


#--This routine searches within each file for the search pattern.
sub d {
  my $file = $File::Find::name;

  $file =~ s,/,\\,g;
   
  #Get the a file name
  return unless -f $file;
  return unless $file =~ (/$file_pattern/);
  $file_counter++;
  $file = "'$file'";
  
  # Execute the macro
  &runExcelMacro($file);
}



sub runExcelMacro {
    # Start Excel
    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');  

    # Open Macro Workbook. 
    my $macros = $Excel->Workbooks->Open('C:\Documents and Settings\cw
+j001\Application Data\Microsoft\Excel\XLSTART\PERSONAL.XLSB'); 
    
    # Open the Spreadsheet
    my $Book = $Excel->Workbooks->Open("$_");
    
    if ($Book != null) {
       printf "\nSuccessful open of spreadsheet : $_\n";
       
       #Find the correct workbook page
       my $Sheet = $Book->Worksheets("Payment");
       
       if ($Sheet != null) {
          printf "Successful open of workbook page: Payment\n";
          
          # Run the macro
          $Excel->Run('PERSONAL.XLSB!TestMacro1'); 
          print "Ran the macro... \n";
          
          $filesUpdated++;
             
             # clean up after ourselves
             $Book->Save; 
             $Book->Close; 
       }
       else {
          printf "Unsuccessful find of workbook page: $Sheet\n";
       }
    }
    else {
       printf "Unsuccessful open of spreadsheet : $Book\n";
        }
    
     # Close Excel
    $Excel->Quit();
}
[download]