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. |