Perl vith VBA macros with arguments

[Anonymous Monk]

HI I need to use perl to call a VBA method that has arguments. I have been searching but was unable to find how to so it. Could any of you help me?

Example

VBA macro:

Public Function Flowupdate(sheetName As String) As Long
    On Error GoTo errHandler
    Dim startTime As Double
    Dim secondsElapsed As Double
.....

So how do I use perl to pass the argument sheetname to the macro?

Thanks in advance.

Markup (see Writeup Formatting Tips) added by GrandFather

[pryrt]

I created a macro-enabled spreadsheet which had the following VBA:

Public Function myfn(arg As Double) As Double
    myfn = arg + Rnd()
End Function

Cell A1: =myfn(RANDBETWEEN(5,17)) -- this cell-formula proves that myfn(arg) is working: Excel generates a random integer from 5 to 17, and passes it to myfn ; the myfn VBA then adds a random offset

Then I run the following perl, using Win32::OLE :

#!perl

use 5.012; # strict, //
use warnings;
use Win32::OLE;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');

print "output = ", $Excel->Run('myfn', 17), "\n";

The output I get will be some random number from 17 to 18:

output = 17.468005657196

This is using the 'Run' Application Object from the Excel Object Model. The function (or subroutine) name is the first argument to $Excel->Run() ; arguments to that function/subroutine are the remaining arguments.

[roblesfel]

it helped me a lot thanks!!!

[GrandFather]

The starting point is Win32::OLE, but it's quite a few years since I did that sort of stuff and I'd need to refresh my memory outside work hours. If you don't get a detailed answer by this evening (my time) I'll have a dig around for some old code and see what I can find.

If you are dealing with Excel you may find Win32::ExcelSimple helpful, although I've not used it and don't know its capabilities. Its documentation is essentially completely missing and suggests you read the test files to figure out how to use it.

[harangzsolt33]

If I had to do this, I would probably create a Perl script that creates a VBScript which then launches Excel and executes the macro I want. And using various websites, including this https://analystcave.com/excel-running-excel-macro-vbscript-cmd I was able to put together a VBScript that opens an Excel file, runs the macro in it, and then closes the file (of course, since the macro makes some changes, it will ask "Do you want to save changes?" and you can select yes or no...)

I called this x.vbs:

'This runs the macro below
RunMacro
 
'The RunMacro procedure
Sub RunMacro() 
  Dim xl
  Dim xlBook      
  Dim sCurPath
  'path = CreateObject("Scripting.FileSystemObject").GetAbsolutePathNa
+me(".")
  Set xl = CreateObject("Excel.application")
  Set xlBook = xl.Workbooks.Open("D:\DESKTOP\TEST.xls", 0, True)      
  Set ws = xlBook.Worksheets("Sheet1")
  Row = ws.UsedRange.Rows.Count
  ws.Cells(Row + 1, 2).Value = "ABC"
  xl.Application.Visible = True
  xl.DisplayAlerts = True
  xl.Application.run "TEST.xls!Macro1", "HELLO"
  
  xl.ActiveWindow.close
  xl.Quit
  Set xlBook = Nothing
  Set xl = Nothing
End Sub

Notice that I pass TWO bits of data to the Excel application. One is an argument to the macro ("HELLO" string) like you wanted, and the other way is by directly overwriting one of the cells in the Excel spreadsheet ("ABC"). Then the macro can read from that box and do something...

This is the macro inside the TEST.XLS file:

Sub Macro1(Param1 As String)

    Range("E9").Select
    ActiveCell.FormulaR1C1 = Param1
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "14"
    Range("C9").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
    Range("C10").Select
End Sub

And the excel spreadsheet looks something like this:

1    2    3    4        
1    2    3    4        
1    2    3    4        
1    2    3    4        
1    2    3    4        
1    2    3    4        
1    2    3    4        
1    2    3    4        
        24    1    1    1

So, when I double-click on the VBScript, Excel starts, it launches the macro immediately. (I set the security level to low prior to this.) Looks like it works! You just have to play with it. But it's definitely doable and it's not too hard.

[marto]

"But it's definitely doable and it's not too hard."

"I would probably create a Perl script that creates a VBScript which then launches Excel and executes the macro I want."

This seems needlessly cumbersome, what advantages does this have over the Win32::OLE based solutions?