code sample for executing a VB function in Excel using perl
 

I have an existing perl script that opens an excel sheet and writes stuff in
it.

but i cannot make it run a VB function that is inside it.

can you send me a code sample for executing a VB function in Excel using perl

newOLE wrote...
I have an existing perl script that opens an excel sheet and writes stuff in
it.

but i cannot make it run a VB function that is inside it.

can you send me a code sample for executing a VB function in Excel using perl

Opens an *existing* workbook that contains VBA code in general modules?
If so, you should be able to use the Excel application class's Run
method. This works for me.


# xl_example.pl
use Win32::OLE;
$xl = Win32::OLE-CreateObject("Excel.Application");
$wb = $xl-Workbooks-Open('d:/test/deleteme.xls');
$xl-Run('foo'); #runs Sub foo
$zz = $xl-Run('bar'); #runs Function bar and stores its result
print $zz, "\n";
undef $wb;
undef $xl;

Harlan

thanks for the code. i tried it but always get this error:

Win32::OLE(0.1502) error 0x800a01a8
in METHOD/PROPERTYGET "" at valueTRS.pl line 40

and the code at line 40 is:

$Sheet-Run("myown"); # calculate all those rows

do you know what's going on?

-R



"Harlan Grove" wrote:

newOLE wrote...
I have an existing perl script that opens an excel sheet and writes stuff in
it.

but i cannot make it run a VB function that is inside it.

can you send me a code sample for executing a VB function in Excel using perl

Opens an *existing* workbook that contains VBA code in general modules?
If so, you should be able to use the Excel application class's Run
method. This works for me.


# xl_example.pl
use Win32::OLE;
$xl = Win32::OLE-CreateObject("Excel.Application");
$wb = $xl-Workbooks-Open('d:/test/deleteme.xls');
$xl-Run('foo'); #runs Sub foo
$zz = $xl-Run('bar'); #runs Function bar and stores its result
print $zz, "\n";
undef $wb;
undef $xl;

"newOLE" wrote...
thanks for the code. i tried it but always get this error:

Win32::OLE(0.1502) error 0x800a01a8
in METHOD/PROPERTYGET "" at valueTRS.pl line 40

and the code at line 40 is:

$Sheet-Run("myown"); # calculate all those rows

do you know what's going on?
....
"Harlan Grove" wrote:
....
# xl_example.pl
use Win32::OLE;
$xl = Win32::OLE-CreateObject("Excel.Application");
$wb = $xl-Workbooks-Open('d:/test/deleteme.xls');
$xl-Run('foo'); #runs Sub foo
$zz = $xl-Run('bar'); #runs Function bar and stores its result
print $zz, "\n";
undef $wb;
undef $xl;

Is your $Sheet variable a reference to a worksheet object? If so, that's
your problem. Excel's Worksheet class doesn't have a Run method, Excel's
Application class does. If you look at my code above you'll see that I call
the Run method from the Excel application object $xl. Either you need use
Run directly from an Excel application object reference or by deriving one
from a worksheet object reference, i.e.,

$Sheet-Parent-Parent-Run('myown');

Thanks. It turned out my error was i was closing the book before that.

New question: how do i execute a function written with arguments passed and
lay out its return parameters on a range of cells. This function is an Excel
add-in. Does that require special processing?

-R

"Harlan Grove" wrote:

"newOLE" wrote...
thanks for the code. i tried it but always get this error:

Win32::OLE(0.1502) error 0x800a01a8
in METHOD/PROPERTYGET "" at valueTRS.pl line 40

and the code at line 40 is:

$Sheet-Run("myown"); # calculate all those rows

do you know what's going on?
....
"Harlan Grove" wrote:
....
# xl_example.pl
use Win32::OLE;
$xl = Win32::OLE-CreateObject("Excel.Application");
$wb = $xl-Workbooks-Open('d:/test/deleteme.xls');
$xl-Run('foo'); #runs Sub foo
$zz = $xl-Run('bar'); #runs Function bar and stores its result
print $zz, "\n";
undef $wb;
undef $xl;

Is your $Sheet variable a reference to a worksheet object? If so, that's
your problem. Excel's Worksheet class doesn't have a Run method, Excel's
Application class does. If you look at my code above you'll see that I call
the Run method from the Excel application object $xl. Either you need use
Run directly from an Excel application object reference or by deriving one
from a worksheet object reference, i.e.,

$Sheet-Parent-Parent-Run('myown');

newOLE wrote...
....
New question: how do i execute a function written with arguments passed and
lay out its return parameters on a range of cells. This function is an Excel
add-in. Does that require special processing?
....

At this point I have to question why you're screwing around with Perl
to do this. You may be better off using Perl to do no more than start
Excel and load the necessary workbooks. Then run a VBA macro in one of
the workbooks to do what you need to do.

However, if the add-in you need to use is automatically loaded by Excel
(via settings made in Excel using Tools Add Ins), you may need to use
the Evaluate method of the Application class rather than the Run
method. You'd need to make the argument to Evaluate look like a cell
formula.

Thanks

so, what's the syntax to run a VBA macro that has arguments

e.g. answer = dothis(a, b, c, d)

Also, does any non-visible function (when I do Alt-F11) loaded through an
add-in become a VBA macro?


"Harlan Grove" wrote:

newOLE wrote...
....
New question: how do i execute a function written with arguments passed and
lay out its return parameters on a range of cells. This function is an Excel
add-in. Does that require special processing?
....

At this point I have to question why you're screwing around with Perl
to do this. You may be better off using Perl to do no more than start
Excel and load the necessary workbooks. Then run a VBA macro in one of
the workbooks to do what you need to do.

However, if the add-in you need to use is automatically loaded by Excel
(via settings made in Excel using Tools Add Ins), you may need to use
the Evaluate method of the Application class rather than the Run
method. You'd need to make the argument to Evaluate look like a cell
formula.

newOLE wrote...
so, what's the syntax to run a VBA macro that has arguments

e.g. answer = dothis(a, b, c, d)

Also, does any non-visible function (when I do Alt-F11) loaded through an
add-in become a VBA macro?

Using Evaluate? It needs to look like a cell formula. If you have
Excel, you should have Excel's online help, so you could use that to
check the syntax. If you don't have Excel or its online help, you
shouldn't be trying to automate Excel.

Why don't you just enter the necessary formulas into Excel cells? If
you have an add-in that provides a function named Takes4Arguments which
(oddly enough) takes 4 arguments, you could use something like

# xl_example.2.pl
use Win32::OLE;
$xl = Win32::OLE-CreateObject("Excel.Application");
$wb = $xl-Workbooks-Add;
$ws = $wb-Worksheets(1);
$ws-Cells(1,1)-{'Formula'} = "=Takes4Arguments(1,2,3,4)";
$ws-Cells(2,1)-{'Value'} =
$xl-Evaluate('=Takes4Arguments(1,2,3,4)');
$xl-{'Visible'} = True;
__END__
# leave Excel app instance running