How to run Excel macros from the command line or batch file?

Question

I have an Excel VBA macro that I need to run when I access a file from a batch file, but not open it every time (therefore, not using the open file event). Is there a way to run a macro from a command line or a batch file? I am not familiar with such a team.

Assume that the environment is Windows NT.

Answer 1

You can start Excel, open the workbook, and run the macro from the VBScript file.

Copy the code below into Notepad.

Update the "MyWorkbook.xls" and "MyMacro" options.

Save it with the 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 

Key line executing macro:

xlApp.Run "MyMacro"

Answer 2

The easiest way to do this:

1) Launch 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_Open event of the book, for example:

 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 a batch file for other processing.

Answer 3

The method shown below allows you to run a specific Excel macro from a batch file; it uses an environment variable to transfer the macro name from the package to Excel.

Put this code in a batch file (use your paths to EXCEL.EXE and to working EXCEL.EXE ):

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

Put this code in 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 in Excel VBA module as below:

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

Run the batch file and get the result:

For the case when you are not going to run any macro, just set the empty Set MacroName= value to the package.

Answer 4

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

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

Answer 5

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

 If Workbooks.Count = 1 Then ' execute the macro or call another procedure - I always do the latter PublishReport ThisWorkbook.Save Application.Quit End If 

Answer 6

If you prefer working in Excel / VBA, use an open event and test the environment: either there is a signal file, a registry entry, or an environment variable that controls what the open event does.

You can create the file / setting from the outside and test it inside (use GetEnviromentVariable for env-vars) and easily test it. I wrote VBScript, but the resemblance to VBA makes me more angry than light.

[more details]

As I understand it, you want to use a spreadsheet, usually most / part of the time, but it starts in batch mode and does something extra / different. You can open the sheet from the excel.exe command line, but you cannot control what it does if it does not know where it is. Using an environment variable is relatively simple and simplifies testing a spreadsheet.

To clarify, use the function below to explore the environment. In the 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 opened Workbook event (like others):

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

Add a valid code, if applicable. In batch file use

 set InBatch=TRUE 

Answer 7

Instead of directly comparing strings (VB will not find them equal, because GetEnvironmentVariable returns a string of length 255):

 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 

Answer 8

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

VBS starts and closes, but doesn't run the macro ... Any idea on where the problem 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 deleted "Application.Quit" because my macro invokes a custom form, taking care of this.

Greetings

EDIT

I really worked it out, just in case someone wants to run a custom form “like” a standalone application:

Problems I encountered:

1 - I did not want to use the Workbook_Open event, since excel is read-only blocked. 2 - The party team is limited by the fact that (as far as I know) it cannot call a macro.

First I wrote a macro to run my custom form, hiding the application:

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

Then I created vbs to run this macro (doing this with a relative path was complicated):

 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 made a batch file to run VBS ...

 @echo off pushd %~dp0 cscript Add_Client.vbs 

Note that in my Userform_QueryClose :

I also included "Set back to visible"

 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 helps if someone needs it

Answer 9

You can check if Excel is open. 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 

Answer 10

I partially relate to C #. I used the following linqpad. But it could be easily compiled with csc and run through a call from the command line.

Remember to add excel packages to the 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; } }