1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • 1
    I would place all the VBA code in an ADDIN workbook named `MsgBox.xlam` with all the code referring to the active workbook. Then simply open both workbooks with the VBS script and call the procedure. – Florent B. Jan 25 '18 at 13:38

0 Answers0