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

Hi!, I am dealing with running macros through perl. i have following script to do the same which is working fine.Now what i have to do is to make one .xlsm (macro-enabled file)which will contain all the needed macros. and i need to call that macros from .xlsm file in other workbooks. I am trying to give path in that RUN() rather than macro name but it is not working. Can anybody tell me,how to do this? 

#!/usr/bin/perl
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;


my $filename = 'F:\perl\report.xlsx';

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

my $Book = $Excel->Workbooks->Open( $filename );

$Excel->Run("Deletebrows"); # This is macro name
$Excel->Run("FillEmptyCellWithZero"); # This is macroname
$Book->Close;

[download]
In advance thanks.

Replies are listed 'Best First'.
Re: Calling macro from another workbook using perl
by dasgar (Priest) on Apr 14, 2012 at 16:53 UTC

    Personally, I've never been a big user of macros. Even when manually using Excel, I'm not sure that I've ever tried using a macros that is from workbook A within workbook B.

    I've got two thoughts/suggestions.

    First, you mention that you "need to call that macros from .xlsm file in other workbooks". However, you only opened one Excel file. Unless you're opening both files in the same Excel session, I don't think you'll see much success.

    Secondly, forget about Perl and your script for a moment. Can you open Excel and manually do the task that you're trying to do? If you can, try to record a macro that does it and then work on converting that code (VB) into Perl. If you can't do the task manually, you may need to look into copying the macros to add it to the other files and then using the macros that is local to the file.

[reply]
      Thanks for your reply. here is my modified code. and yes,as per your suggestion i tried to do it manually and its working.But,same i am trying same to do with script,its not working. 
      use Win32::OLE::Variant;
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')||Win32::O
+LE->new('Excel.Application', 'Quit'); 

my $Book = $Excel->Workbooks->Open( "F:\\project\\report1.xlsx" ); 

my $Book = $Excel->Workbooks->Open( "F:\\project\\report1.xlsm" ); # O
+pen Workbook needing the macro: Macro1

my $Sheet = $Book->Worksheets(1); #work on sheet1

$Excel->Run("report1.xlsm!Deletebrows"); #Calling on the Macro from ma
+cro workbook.
$Excel->Run("report1.xlsm!FillEmptyCellWithZero");

$Book->Save; 
$Book->Close;

      [download]
[reply]
[d/l]
Re: Calling macro from another workbook using perl
by aartist (Pilgrim) on Apr 14, 2012 at 12:12 UTC
    Deletebrows
    Could 'B' be the mistake? A possible typo?
[reply]
      No its not the case. its "Deletebrows" only.
[reply]

Back to Seekers of Perl Wisdom