Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Perl vith VBA macros with arguments

by Anonymous Monk
on Oct 11, 2022 at 18:59 UTC ( #11147352=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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?


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

Replies are listed 'Best First'.
Re: Perl vith VBA macros with arguments
by pryrt (Abbot) on Oct 11, 2022 at 20:59 UTC
    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.

      it helped me a lot thanks!!!
Re: Perl vith VBA macros with arguments
by GrandFather (Saint) on Oct 11, 2022 at 20:08 UTC

    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.

    Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond
Re: Perl vith VBA macros with arguments
by harangzsolt33 (Hermit) on Oct 14, 2022 at 07:59 UTC
    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 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 "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.

      "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?

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11147352]
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2023-05-31 00:59 GMT
Find Nodes?
    Voting Booth?

    No recent polls found