Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Re^3: Creating Excel Macros from Perl

by spivey49 (Monk)
on Jul 30, 2008 at 19:49 UTC ( #701249=note: print w/replies, xml ) Need Help??

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

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.


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';

Replies are listed 'Best First'.
Re^4: Creating Excel Macros from Perl
by ChumpChief (Initiate) on Jul 30, 2008 at 20:10 UTC
    Thanks spivey49, that did it. Trusting access to project was the key.
Re^4: Creating Excel Macros from Perl
by Anonymous Monk on Jan 08, 2014 at 12:38 UTC
    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...' );

      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.

        yes, tried reopening. Did not work

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://701249]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (6)
As of 2019-12-05 16:49 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (151 votes). Check out past polls.