Using VBS to open text file in excel and run macro without opening excel

Question

This might sound confusing, and I'm not entirely sure it's possible to do it the way I want, but what I'm looking to do is create a script that will take a .txt file and run Excel macros on it, without actually opening up Excel or the file. One of the blogs I was reading suggested this method, but I'm not very familiar with VBS.

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'Path\Test.xlsm'!Module.Macro"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing

This did not work when I tried to use it with a .txt file. The error I receive is Cannot run the macro Path.... The macro may not be available in this workbook or all macros may be disabled

I'm sure it has something to do with my lack of VBS knowledge, but so far it's been the closest kind of script I've found for what I'm looking for. Is what I'm trying to do possible?

Just to be clear, you HAVE opened up Excel when you created an Excel.Application object. You just haven't opened the workbook and that is where your problem is. — user4039065

Ansgar Wiechers Ansgar Wiechers · Accepted Answer · 2015-09-15T20:24:53

You need to open the workbook before you can run macros from it.

...
Set wb = objExcel.Workbooks.Open("C:\path\to\Test.xlsm")
objExcel.Application.Run "'Path\Test.xlsm'!Module.Macro"
...

You can't run a macro without opening Excel or the file containing the macro. If you want something like that you need to translate the VBA code to plain VBScript.

Using VBS to open text file in excel and run macro without opening excel

2 Answers