CODEWITHSUNDEEP
excelvbscript
Jcmoney1010
Jcmoney1010

Reputation: 922

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

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?

Upvotes: 0

Views: 997

Answers (2)

Bern
Bern

Reputation: 1

Path statement need full path eg " c:\ .... " And I think it should be Module1.Macro_Name (not Module) Write in note pad but save with .vbs on desktop. Just click on the run

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

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.

Upvotes: 0

Related Questions