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 macro from the command line or batch file? I'm not familiar with such a command.

Assume a Windows NT environment.

From stackoverflow Polymeron

• you could write a vbscript to create an instance of excel via the createobject() method, then open the workbook and run the macro. You could either call the vbscript directly, or call the vbscript from a batch file.

  Here is a resource I just stumbled accross: http://www.codeguru.com/forum/showthread.php?t=376401

  From Fink

• If you're more comfortable working inside Excel/VBA, use the open event and test the environment: either have a signal file, a registry entry or an environment variable that controls what the open event does.

  You can create the file/setting outside and test inside (use GetEnviromentVariable for env-vars) and test easily. I've written VBScript but the similarities to VBA cause me more angst than ease..

  [more]

  As I understand the problem, you want to use a spreadsheet normally most/some of the time yet have it run in batch and do something extra/different. You can open the sheet from the excel.exe command line but you can't control what it does unless it knows where it is. Using an environment variable is relatively simple and makes testing the spreadsheet easy.

  To clarify, use the function below to examine the environment. In a module declare:

  Private Declare Function GetEnvVar Lib "kernel32" Alias "GetEnvironmentVariableA" _
      (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long
  
  Function GetEnvironmentVariable(var As String) As String
  Dim numChars As Long
  
      GetEnvironmentVariable = String(255, " ")
  
      numChars = GetEnvVar(var, GetEnvironmentVariable, 255)
  
  End Function
  

  In the Workbook open event (as others):

  Private Sub Workbook_Open()
      If GetEnvironmentVariable("InBatch") = "TRUE" Then
          Debug.Print "Batch"
      Else
          Debug.Print "Normal"
      End If
  End Sub
  

  Add in active code as applicable. In the batch file, use

  set InBatch=TRUE
  

  From Chris Chambers

• The simplest way to do it is to:

  1) Start Excel from your batch file to open the workbook containing your macro:

  EXCEL.EXE /e "c:\YourWorkbook.xls"
  

  2) Call your macro from the workbook's Workbook_Open event, such as:

  Private Sub Workbook_Open()
      Call MyMacro1          ' Call your macro
      ActiveWorkbook.Save    ' Save the current workbook, bypassing the prompt
      Application.Quit       ' Quit Excel
  End Sub
  

  This will now return the control to your batch file to do other processing.

  Polymeron : Thought of that, but it would run even if I'm opening it not through the batch file, making the file itself impossible to work with without disabling macros first.

  MrSpreadsheet : This is simple to solve by modifying a registry setting that tells Excel which level of security is set.

  MrSpreadsheet : Of course, digitally signing the workbook always helps, even with a Test certificate :)
  From MrSpreadsheet

• You can launch Excel, open the workbook and run the macro from a VBScript file.

  Copy the code below into Notepad.

  Update the 'MyWorkbook.xls' and 'MyMacro' parameters.

  Save it with a vbs extension and run it.

  Option Explicit
  
  On Error Resume Next
  
  ExcelMacroExample
  
  Sub ExcelMacroExample() 
  
    Dim xlApp 
    Dim xlBook 
  
    Set xlApp = CreateObject("Excel.Application") 
    Set xlBook = xlApp.Workbooks.Open("C:\MyWorkbook.xls", 0, True) 
    xlApp.Run "MyMacro"
    xlApp.Quit 
  
    Set xlBook = Nothing 
    Set xlApp = Nothing 
  
  End Sub 
  

  The key line that runs the macro is:

  xlApp.Run "MyMacro"

  Polymeron : This is what I was looking for! It works. I should add a couple of changes are necessary for this to work without glitches: 1. It's "xlApp.Run". 2. Before quitting, one should use xlApp.SaveAs or no modifications made by the macro will be saved. (one can use xlApp.DisplayAlerts = False) to avoid related popups) 3. Before quitting, xlApp.ActiveWorkbook.Close should be invoked or the spreadsheet remains open (though hidden), which disables further editing. All in all though, this is what I was looking for :)
  From Robert Mearns