Just for fun (and because I might be able to use this to save my keister someday) I extended this to run a Perl script contained wholly in a field in an MS Access table (and therefore as part of an Access "application"). For my example, the table is simply 'perl_scripts', with fields 'script_name' (text field), 'script' (memo field). I then built a form with a really big box for the script memo field and that becomes a mini-editor (I'm sure with proper patience one could work that form field into a decent enough text editor-- but more importantly this allows a cut & paste of code from a real editor, like emacs).

Function run_script(q As String) As String
    ' To use this sub/function you must have
    ' Perlscript installed* and then register the
    '   Microsoft Script Control 1.0 (MSSCRIPT.OCX)
    ' which you must register from the Tools-References
    ' menu of the VBA editor.
    '  * standard in ActiveState's ActivePerl distribution
    
    'requires table named "perl_scripts" with fields
    'script_name (text)
    'script (memo)
    
    On Error GoTo run_script_error
    
    Dim db As Database
    Set db = CurrentDb
    
    Dim scripts As Recordset
    Set scripts = db.OpenRecordset("perl_scripts", dbOpenSnapshot)
    
    Dim finder As String
    finder = "script_name='" & q & "'"
    
    scripts.MoveFirst
    scripts.FindFirst (finder)
    If scripts.NoMatch Then
        Debug.Print "Script " & q & " not found."
        Err.Raise 448
    End If
   
    Dim ps As String
   
    ps = scripts("script")
        
    Dim perl As New ScriptControl
    perl.Language = "PerlScript"
    run_script = perl.Eval(ps)
    
    Exit Function
    
run_script_error:
    MsgBox ("Error running Perl script: " + Error$)
    run_script = False
    
End Function
[download]

Here is the sample Perl code I ran. Note that it allows strict, and at this point does not accept any input from the VBA code. I think to do that the best way would be to run perl.ExecuteStatement("$globalSetting = '" & theSetting & "'"'s before the perl.Eval command. But then we might have to turn off strict. Also note that this code is required to return a value as it's last statement (and that VBA treats it as a string).

use strict;

sub foo {
  my $name = shift;
  return "Hello, $name.\n";
}

sub bar {
  my $name = shift;
  return "Good-bye, $name!\n";
}

my $return = foo("World") . bar("Cruel World");
return $return;
[download]

We can then run this function with a quick routine like:

Sub test_run_script()
    Debug.Print run_script("Perl Test")
End Sub
[download]

Either way, thanks for the heads up on the ability to run Perl from VBA. This is what I've been saying would make my life easier for a long time... next to figure out if there's a good way to get Perl talking to Access/VBA objects themselves and to return more than a simple scalar. Any idea if/where The Fine Manuals on this would be? I'll be looking myself and posting links if I find anything.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.