Run Excel Macro from Outside Excel Using VBScript From Command Line

excel vba command-line vbscript
muttley91 picture muttley91 · Apr 19, 2012 · Viewed 230k times · Source

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.

Answer

Wolf-Kun picture Wolf-Kun · Apr 19, 2012

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.

Related questions

Way to run Excel macros from command line or batch file?

I have an Excel VBA macro which I need to run when accessing the file from a batch file, but not every time I open it (hence not using the open file event). Is there a way to run the …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More