To Run an Excel VBA macro from the command line has the advantage of convenience. With a few simple keystrokes, we can execute tasks that would otherwise require many keystrokes in the Graphical User Interface (GUI).
Because it is not possible to execute an Excel macro outside of Excel, the only way we can run a macro from the command line is to use a VBScript. VBScript is an executable text file with a .vbs file extension.
The VBScript is natively supported by Windows operating system and therefore can be executed outside of the Excel Application and other MS Office applications.
The VBScript we will create in this tutorial launches the Excel Application, opens the Excel file in hidden mode, and runs the VBA macro.
In this tutorial, we will first create a macro that imports data into Excel from a Word Table. Then we will create a VBScript that we can launch from the command line and have the macro executed.
Create a macro that imports data from the Word table
We will use the following Word table to show how we can import data from a Word table into an Excel file. The table shows details of different products:
|Supplier ID||Item Type||Product ID||Product Name||Product Code||Bin Picking Number|
|1||Product||4563||ACB PSU 660||AC-ll00007||AC|
|2||Product||4569||ITR KBD M6||AC-ll00008||AC|
|3||Product||5489||MSF HDS LIF||AC-ll00009||AC|
|4||Product||6485||MSF MSE MOBILE||AC-ll00010||AC|
A macro that can import data from word tables into Excel is very useful because many people are comfortable with Word and find it easier to work with Word than Excel. But Word does not have the powerful data analysis tools that Excel has.
When the macro imports the data we can apply Excel’s powerful data analysis tools to analyze it.
We create the macro by using the following steps:
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Insert a module and type in the code below:
Dim wdApp As Object
Dim wdDoc As Object
Dim xlSheet As Worksheet
Dim wdTable As Object
Dim wdCell As Object
Dim i As Long, j As Long
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
On Error GoTo 0
Set xlSheet = ActiveSheet
Set wdDoc = wdApp.Documents.Open("C:\files\Product List.docx") 'Use the full path of the document with the table
Set wdTable = wdDoc.Tables(1) 'This is the first table in the document. For a different table, change the number as appropriate
For i = 1 To wdTable.Rows.Count
For j = 1 To wdTable.Columns.Count
Set wdCell = wdTable.Cell(i, j).Range
wdCell.End = wdCell.End - 1
xlSheet.Cells(i, j) = wdCell 'adds the content to the corresponding cell in Excel
' Close the Word Document without saving the changes
' Quit the application
' release system memory assigned to the two variables
Set wdDoc = Nothing
Set wdApp = Nothing
- Place a cursor inside the procedure and press F5 to run it. The macro should run perfectly without any runtime errors if we have to run it successfully from the command line.
The macro opens the Word document and imports the data in the table into the active worksheet of the Excel file:
The macro is working as it should. Select the imported data and delete it so that the macro run by VBScript later can import the data into a blank worksheet.
Let’s see how we can run the macro from the command line.
Create a VBScript file
We first need to create a VBScript file that we can launch from the command line. VBScript is a scripting language developed by Microsoft and is a lighter version of Visual Basic.
VBScript is quite identical to VBA and that is why most VBA scripts can be exported to simple VBScript files.
To create the VBScript we use the following steps:
- Open Notepad and type in the following code:
'Create Excel App Instance & Open Xlsm File
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = True
objExcelApp.DisplayAlerts = False
'Define Macro File & Path. You can change it to reflect the path of your Excel file.
sFilePathXlsm = "C:\files\Run_VBA_from_Command_Line.xlsm"
Set iWb = objExcelApp.Workbooks.Open(sFilePathXlsm)
sMacroToRun = "'" & sFilePathXlsm & "'!importWordTableData"
'Save & Close file
objExcelApp.DisplayAlerts = True
The comments in the code as preceded by an apostrophe and are ignored during program execution.
The comments explain what the code is doing.
- Save the file as myMacro.vbs. The file must have a .vbs extension.
For this tutorial, the VBScript is saved in the directory C:\Users\Sirali\Desktop\Run_VBA_from_Command_Line
Run the VBscript from the command line
- To open the Command Prompt console type “command prompt” in the Windows start search box and press Enter key. Alternatively, type in “cmd” and press Enter:
- At the prompt type the command cd (change directory) followed by a space and then type the path of the VBScript: C:\Users\Sirali\Desktop\Run_VBA_from_Command_Line and press Enter key:
- Type in the name of the VBScript myMacro.vbs as follows and then press the Enter key:
The VBScript myMacro.vbs will take some time to run. We can observe as the Word and Excel files are opened and quickly closed.
The VBScript will create an instance of the Excel application. Any alerts are not displayed for there is no need for user intervention. The ideal macros for running from a command line are those that do not require input from users.
Data from the Word table will be imported into Excel.
The Excel file will then be saved and closed
We can open the Excel file and see that the data has been imported:
An Excel VBA macro cannot be run outside of Excel.
In this tutorial, we have seen that the only way a macro can be run from the command line is through a VBScript. VBScript is a scripting language that is a lighter version of Visual Basic.
VBScript is supported natively by Windows and therefore can be executed outside of Excel and other Office applications.
We created a VBScript that opens an instance of Excel and runs the VBA macro. When the macro is finished running the VBScript takes over and saves and closes the Excel Application.