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 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
5	Product	7245	MSF KBD	AC-ll00011	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:

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

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

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)

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

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

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

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:

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.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 4,219