in reply to Re: Creating Excel Macros from Perl
in thread Creating Excel Macros from Perl

Thanks for your reply spivey49, but again that's not really what I'm looking for. I'm looking to use Perl to actually create that macro, not to run it.

Also, that's a slightly different type of TOC from what I'm looking to do -- see my posted code for an example.

I did find this which is exactly what I want -- except it doesn't work. Might be because it was written for Excel 97, I'm not entirely sure (It throws a bareword error on vbextFileTypeModule, apparently it's not included in the constant libraries they're using).

Replies are listed 'Best First'.
Re^3: Creating Excel Macros from Perl
by spivey49 (Monk) on Jul 30, 2008 at 19:49 UTC

    Try changing "vbextFileTypeModule" to 1 or find the correct constant, possibly vbext_ct_StdModule.

    You'll need to set your macro security settings to low and probably trust access to the project as well.

    Update:

    This works on Excel 2003 after setting macro security settings to low and trusting access to the project: 

    
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE::Const "Microsoft Visual Basic for Applications Extensi
+bility";


my $xls = Win32::OLE->new('Excel.Application');
$xls->{Visible} = -1;
my $wb = $xls->Workbooks->Add;
my $mod = $wb->VBProject->VBComponents->Add(1);
$mod->{Name}="NewMod";
$mod->CodeModule->AddFromString ( <<MODTEXT);
Sub Message
    dim s as string
    s = "Hello, World"
    MsgBox s
End Sub
MODTEXT

my $sheet = $xls->Sheets('Sheet1');
my $button = $sheet->Buttons->Add(71.25, 18.75, 88.5, 57);
$button->{OnAction} = 'Message';

    [download]

[reply]
[d/l]
      Thanks spivey49, that did it. Trusting access to project was the key.
[reply]
      adding the Sub to Codemodule and calling it from perl worked fine {with below code}. How do I call this Sub again from Excel, when i open vb editor I did not see any module with the Sub created from perl. Please help! 
      my $code = <<'END_CODE';
Sub dosomething(say As String)
For Each cell In ActiveSheet.Range("A1:A10")
cell.Value = say
Next cell
End Sub

END_CODE

my $codemodule = $mod->Codemodule;
$codemodule->AddFromString($code);

$xlApp->Run(
'dosomething',
'test...'
);

      [download]
[reply]
[d/l]

        Did you save the Excel sheet?

        Maybe the VB editor does not update its view on the defined subroutines and modules and needs to close and reopen the file to see the changes.

[reply]

In Section Seekers of Perl Wisdom