I have two file:
a VBS Script file
The file is named Trigger.vbs
and contain
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\MyPath\MsgBox.xltm", 0, True)
xlApp.Run "hello"
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
inspired by this answer.
an Excel file
Named MsgBox.xltm
and with the following Sub inside, written in VBA
Sub hello()
MsgBox "Hello everybody"
End Sub
From C:\MyPath
, when I double click on Trigger.vbs
the macro is being executed.
The question
Now I would like to save the file Excel in XLSX format (so it can't contain any VBA code). Is it possibile to modify the VBS script including there the sub code or (better) to write the VBA code in a TXT file and tell the VBS script to open it and run into the Excel file?
Something like
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\MyPath\MsgBox.xltm", 0, True)
xlApp.Run (R:\MyPath\Module.txt)
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
where R:\MyPath\Module.txt
contains the VBA code listed above.