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

[download]

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

[download]

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

[download]

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.

In reply to Re: Perl vith VBA macros with arguments by harangzsolt33
in thread Perl vith VBA macros with arguments by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.