generating macro with perl

[Alex Lee]

HI all:

Is there a way to save macro codes for example

$macroCode = 'code';

and basically launch this macro during an excel session?
It would be nice for example, if I can just save a list of macros that
I recorded/modify and launching it whenever I want.

Thanks in advance.

 

[Jay Tilton]

(e-mail address removed) (Alex Lee) wrote:

: Is there a way to save macro codes for example
:
: $macroCode = 'code';
:
: and basically launch this macro during an excel session?
: It would be nice for example, if I can just save a list of macros that
: I recorded/modify and launching it whenever I want.

Tricky.

After much digging through the object libraries, this is what I came
up with.

#!perl
use warnings;
use strict;
use Win32::OLE;

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

# The Excel VBA code, as a string.
my $code = <<'END_CODE';
Sub dosomething(say As String)
For Each cell In ActiveSheet.Range("A1:A100")
cell.Value = say
Next cell
End Sub

END_CODE

my $workbook = $excel->Workbooks->Add;
# Add a module to the workbook.
my $component =
$workbook
->VBProject
->VBComponents
->Add(1);
# 1 = vbext_ct_StdModule. I just didn't feel like
# importing the constants.
$component->{Name} = 'my_macros';
# Add our subroutine to the module.
my $codemodule = $component->Codemodule;
$codemodule->AddFromString($code);
# Run the subroutine.
$excel->Run(
'dosomething',
'No TV and no beer make Homer go crazy.'
);

You might try looking for a better solution in an OLE-oriented
newsgroup, like say microsoft.public.vb.ole.automation.

 

[Alex Lee]

Hi thanks for your help!
However when I try to run your code: I keep getting this error message
when I try to add the macro to the workbook.

$workbook->VBProject->VBComponents->Add(1) or die print "$! nope";

error: Can't call method "VBComponents" on an undefined value


Any thoughts on it? Thanks again.
al

 

[Alex Lee]

Thanks for the reply. I actually figured out what went wrong.
Supposedly it s security thingy that macros cannot post other macros,
unless you set your security for the macros to be low.

anways, your code works really good and is exactly what I need. thanks
again.
al

 

[Jay Tilton]

(e-mail address removed) (Alex Lee) wrote:

: Thanks for the reply. I actually figured out what went wrong.
: Supposedly it s security thingy that macros cannot post other macros,
: unless you set your security for the macros to be low.

That figures. Thanks for sharing the results of your investigation.

: anways, your code works really good and is exactly what I need. thanks
: again.

Glad to hear it.

It was an interesting problem. In the past people have asked how to
run existing Excel macros from Perl, and how to convert macros into
Perl, but as far as I can remember nobody has asked how to manipulate
a workbook's code.