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

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

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

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.

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"

Instead of directly comparing the strings (VB won't find them equal since GetEnvironmentVariable returns a string of length 255) write this:

Private Sub Workbook_Open()
If InStr(1, GetEnvironmentVariable("InBatch"), "TRUE", vbTextCompare) Then
Debug.Print "Batch"
Call Macro
Else
Debug.Print "Normal"
End If


End Sub

I have always tested the number of open workbooks in Workbook_Open(). If it is 1, then the workbook was opened by the command line (or the user closed all the workbooks, then opened this one).

If Workbooks.Count = 1 Then


' execute the macro or call another procedure - I always do the latter
PublishReport


ThisWorkbook.Save


Application.Quit


End If

@ Robert: I have tried to adapt your code with a relative path, and created a batch file to run the VBS.

The VBS starts and closes but doesn't launch the macro... Any idea of where the issue could be?

Option Explicit


On Error Resume Next


ExcelMacroExample


Sub ExcelMacroExample()


Dim xlApp
Dim xlBook


Set xlApp = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFilePath = objFSO.GetAbsolutePathName(".")
Set xlBook = xlApp.Workbooks.Open(strFilePath, "Excels\CLIENTES.xlsb") , 0, True)
xlApp.Run "open_form"




Set xlBook = Nothing
Set xlApp = Nothing


End Sub

I removed the "Application.Quit" because my macro is calling a userform taking care of it.

Cheers

EDIT

I have actually worked it out, just in case someone wants to run a userform "alike" a stand alone application:

Issues I was facing:

1 - I did not want to use the Workbook_Open Event as the excel is locked in read only. 2 - The batch command is limited that the fact that (to my knowledge) it cannot call the macro.

I first wrote a macro to launch my userform while hiding the application:

Sub open_form()
Application.Visible = False
frmAddClient.Show vbModeless
End Sub

I then created a vbs to launch this macro (doing it with a relative path has been tricky):

dim fso
dim curDir
dim WinScriptHost
set fso = CreateObject("Scripting.FileSystemObject")
curDir = fso.GetAbsolutePathName(".")
set fso = nothing


Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open curDir & "\Excels\CLIENTES.xlsb"
xlObj.Run "open_form"

And I finally did a batch file to execute the VBS...

@echo off
pushd %~dp0
cscript Add_Client.vbs

Note that I have also included the "Set back to visible" in my Userform_QueryClose:

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ThisWorkbook.Close SaveChanges:=True
Application.Visible = True
Application.Quit
End Sub

Anyway, thanks for your help, and I hope this will help if someone needs it

You can check if Excel is already open. There is no need to create another isntance

   If CheckAppOpen("excel.application")  Then
'MsgBox "App Loaded"
Set xlApp = GetObject(, "excel.Application")
Else
' MsgBox "App Not Loaded"
Set  wrdApp = CreateObject(,"excel.Application")
End If

I'm partial to C#. I ran the following using linqpad. But it could just as easily be compiled with csc and ran through the called from the command line.

Don't forget to add excel packages to namespace.

void Main()
{
var oExcelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
try{
var WB = oExcelApp.ActiveWorkbook;
var WS = (Worksheet)WB.ActiveSheet;
((string)((Range)WS.Cells[1,1]).Value).Dump("Cell Value"); //cel A1 val
oExcelApp.Run("test_macro_name").Dump("macro");
}
finally{
if(oExcelApp != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcelApp);
oExcelApp = null;
}
}

The method shown below allows to run defined Excel macro from batch file, it uses environment variable to pass macro name from batch to Excel.

Put this code to the batch file (use your paths to EXCEL.EXE and to the workbook):

Set MacroName=MyMacro
"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" "C:\MyWorkbook.xlsm"

Put this code to Excel VBA ThisWorkBook Object:

Private Sub Workbook_Open()
Dim strMacroName As String
strMacroName = CreateObject("WScript.Shell").Environment("process").Item("MacroName")
If strMacroName <> "" Then Run strMacroName
End Sub

And put your code to Excel VBA Module, like as follows:

Sub MyMacro()
MsgBox "MyMacro is running..."
End Sub

Launch the batch file and get the result:

For the case when you don't intend to run any macro just put empty value Set MacroName= to the batch.

I generally store my macros in xlam add-ins separately from my workbooks so I wanted to open a workbook and then run a macro stored separately.

Since this required a VBS Script, I wanted to make it "portable" so I could use it by passing arguments. Here is the final script, which takes 3 arguments.

• Full Path to Workbook
• Macro Name
• [OPTIONAL] Path to separate workbook with Macro

I tested it like so:

"C:\Temp\runmacro.vbs" "C:\Temp\Book1.xlam" "Hello"


"C:\Temp\runmacro.vbs" "C:\Temp\Book1.xlsx" "Hello" "%AppData%\Microsoft\Excel\XLSTART\Book1.xlam"

runmacro.vbs:

Set args = Wscript.Arguments


ws = WScript.Arguments.Item(0)
macro = WScript.Arguments.Item(1)
If wscript.arguments.count > 2 Then
macrowb= WScript.Arguments.Item(2)
End If


LaunchMacro


Sub LaunchMacro()
Dim xl
Dim xlBook


Set xl = CreateObject("Excel.application")
Set xlBook = xl.Workbooks.Open(ws, 0, True)
If wscript.arguments.count > 2 Then
Set macrowb= xl.Workbooks.Open(macrowb, 0, True)
End If
'xl.Application.Visible = True ' Show Excel Window
xl.Application.run macro
'xl.DisplayAlerts = False  ' suppress prompts and alert messages while a macro is running
'xlBook.saved = True ' suppresses the Save Changes prompt when you close a workbook
'xl.activewindow.close
xl.Quit


End Sub