Run Excel Macro from Outside Excel Using VBScript From Command Line
command-line, excel, vba, vbscript
Solution
Ok, it's actually simple. Assuming that your macro is in a module,not in one of the sheets, you use:
objExcel.Application.Run "test.xls!dog"
'notice the format of 'workbook name'!macro
For a filename with spaces, encase the filename with quotes.
If you've placed the macro under a sheet, say sheet1, just assume sheet1 owns the function, which it does.
objExcel.Application.Run "'test 2.xls'!sheet1.dog"
Notice: You don't need the macro.testfunction notation you've been using.
Problem
I'm trying to run an Excel macro from outside of the Excel file. I'm currently using a ".vbs" file run from the command line, but it keeps telling me the macro can't be found. Here is the script I'm trying to use ``` Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("test.xls") objExcel.Application.Visible = True objExcel.Workbooks.Add objExcel.Cells(1, 1).Value = "Test value" objExcel.Application.Run "Macro.TestMacro()" objExcel.ActiveWorkbook.Close objExcel.Application.Quit WScript.Echo "Finished." WScript.Quit ``` And here is the Macro I'm trying to access: ``` Sub TestMacro() 'first set a string which contains the path to the file you want to create. 'this example creates one and stores it in the root directory MyFile = "C:\Users\username\Desktop\" & "TestResult.txt" 'set and open file for output fnum = FreeFile() Open MyFile For Output As fnum 'write project info and then a blank line. Note the comma is required Write #fnum, "I wrote this" Write #fnum, 'use Print when you want the string without quotation marks Print #fnum, "I printed this" Close #fnum End Sub ``` I tried the solutions located at Is it possible to run a macro in Excel from external command? to get this far (and modified of course) but it didn't seem to work. I keep getting the error `Microsoft Office Excel: The macro 'Macro.TestMacro' cannot be found. EDIT: Excel 2003.