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]

In reply to Perl script runs excel macro but spreadsheet doesn't change by cwj001

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.