This question has to do with automating Access. I simply want automate Access to run my own VBA function in my database and pass it arguments from a script.
So, let's say I have written a public function called foo() in a module in my Access database. Foo() takes a couple of arguments, param1 and param2, the first a string, the second an int. I want to write a script that automates Access and runs this function in my Access database and passes it values for these parameters. What would that look like?
I have been trying to use Win32::OLE in a Perl script. This is what I have so far. I haven't yet tried the argument passing yet:
Code:
use strict;
use warnings;
use Win32::OLE;
my $oAccess;
my $oDatabase;
my $filename = "C:\\mydb.accdb";
$oAccess = Win32::OLE->GetActiveObject('Access.Application');
$oAccess->OpenCurrentDatabase($filename);
#$oAccess->{DoCmd}->RunCommand(myfunction());
#$oAccess->{DoCmd}->RunCommand("myfunction()");
$oAccess->{DoCmd}->RunCode('myfunction()');
The commented out lines are some of the things I've tried, but I can't get my function to run at all.
It's not clear to me from reading the Win32::OLE documentation at CPAN how to do this. If this is not possible with Win32::OLE, can you tell me how I might accomplish this?
The function I'm attempting to run exports a somewhat complex Excell spreadsheet based on queries on my database. The arguments are variables that I plug into the queries.
Thanks for any help.
Thanks,
John