Run VBA from Command Line

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 IDItem TypeProduct IDProduct NameProduct CodeBin Picking Number
1Product4563ACB PSU 660AC-ll00007AC
2Product4569ITR KBD M6AC-ll00008AC
3Product5489MSF HDS LIFAC-ll00009AC
4Product6485MSF MSE MOBILEAC-ll00010AC
5Product7245MSF KBDAC-ll00011AC

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:

  1. Press Alt + F11 to open the Visual Basic Editor (VBE).
  2. Insert a module and type in the code below:
Sub importWordTableData()
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")
    End If
    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
            Next j
        Next i
        ' Close the Word Document without saving the changes
    wdDoc.Close SaveChanges:=False
    ' Quit the application
    wdApp.Quit
    ' release system memory assigned to the two variables
    Set wdDoc = Nothing
    Set wdApp = Nothing
End Sub
  1. 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:

  1. 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)

'Run Macro
sMacroToRun = "'" & sFilePathXlsm & "'!importWordTableData"
objExcelApp.Run sMacroToRun

'Save & Close file
iWb.Save
iWb.Close
objExcelApp.DisplayAlerts = True
objExcelApp.Quit

The comments in the code as preceded by an apostrophe and are ignored during program execution.

The comments explain what the code is doing.

  1. 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

  1. 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:
  1. 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:
  1. 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:

Conclusion

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.

Posted in vba