Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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; |
#3
|
|||
|
|||
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; |
#4
|
|||
|
|||
"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'); |
#5
|
|||
|
|||
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'); |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the cell value in excel by using vb code | Excel Discussion (Misc queries) | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |