how to run VBA macro from perl code

Question

I am new to perl & i am trying to write a module which would run a excel macro on a already open excel sheet. there is a code sniplet that describes how to run a macro from another excel sheet but i want the macro code as a subroutine in the same file. How to implement that? Can any one help?

use strict;
use warnings;
use Win32::OLE;
my $excel= Win32::OLE->new('Excel.Application')or die "Could not create Excel.Application!\n;
$excel->Workbooks->open( 'C:\Users\Me\Documents\Book1.xlsx' );
$excel->run( 'Book1!Macro1' );

# Here i want that Macro1 as sub in this file itself & not from book1

$excel->quit;

I tried so many things but unable to add macro to excel.Each time it gives an error : Win32::OLE(0.1704) error 0x80020003: "Member not found" in METHOD/PROPERTYGET # my $mod = $sheet->VBProject->VBComponents->Add(vbextFileTypeModule); # even tried using vbext_ct_StdModule & 1 but none is working my $mod = $sheet->VBProject->VBComponents->Item( 'ThisWorkbook' )->CodeModule; $mod->AddFromString( <<"MODTEXT" ); Public Sub Display MsgBox "Hello"; End Sub MODTEXT $showButton->{OnAction} = 'Display'; Could Anyone please provide a resolution for the same. — sarora
– sarora, Commented Mar 1, 2012 at 8:22

Axeman · Accepted Answer · 2012-02-29 15:27:28Z

1

I don't really think you can do this. You would need to access

$workbook->VBProject->VBComponents

but then the typical way to pass create a macro on the fly is to call the VBComponents collection's AddFile, AddFromTemplate or Import method which all require paths to files that Excel will read itself. It's not like it's an extension of Perl and will accept an open file stream as well.

Of course, you can always write the machinery to take a in-script string, dump it out to a temporary file and send that file name to Excel. However, since Microsoft has greatly stepped up its paranoia, I wonder how many security hurdles you will need to clear to get Excel to run a macro from a temp file directory.

After you get this loaded it's simply a matter of $xl->run( 'Bookname!Macro' ). But I think the protections against attacks are bound to hinder your doing this.

Update:

Yeah, I just tried something along these lines and got "Programmatic access to Visual Basic Project is not trusted". Like I said, expect a lot of hurdles, if not complete failure.

However, you can work around that with this advice.

Actually, it turns out I was wrong, the code below allows you to add behavior to a code module.

my $prj = $wb->VBProject;
my $mod = $prj->VBComponents->Item( 'ThisWorkbook' )->CodeModule;
$mod->addFromString( <<"END_VB" );
Public Sub Doodad
    MsgBox( "I am Doodad! Hear me roar!" )
End Sub
END_VB

However when I did this:

$excel->Run( $wb->Name . '!Doodad' );

I got this:

Cannot run the macro 'Book1!Doodad'. The macro may not be available in this
workbook or all macros may be disabled.

edited Feb 29, 2012 at 15:27

answered Feb 29, 2012 at 14:23

Axeman

29.9k2 gold badges50 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tai Paul Over a year ago

In this line $excel->Workbooks->open( 'C:\Users\Me\Documents\Book1.xlsx' ); you reference the workbook with extension "xlsx". This is not a macro enabled workbook which is I believe is why you got the "Cannot run the macro...." message. To overcome that issue the file is to be saved as a Excel Macro-Enabled Workbook which has the extension xlsm.

Collectives™ on Stack Overflow

how to run VBA macro from perl code

1 Answer 1

Update:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related