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
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;
We can then run this function with a quick routine like:
Sub test_run_script()
Debug.Print run_script("Perl Test")
End Sub
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. | 