An introduction to the shell

A shell is an interface in the Windows that runs commands and helps you use programs. Hence it is also called the “Command Line Interface” (CLI). In Windows, we also call it the command prompt.

You can also open the command prompt by typing “cmd” in the Run dialog after clicking on the Windows button on your keyboard.

In Windows 10, we can directly type it intp the search box next to the windows button.

Some sample commands

Below are some commands and output that may interest you. If you’re already quite familiar with the shell commands that you have at your disposal, feel free to skip to the next section to learn how to call the shell from VBA.

Internal commands

The above commands are already a part of the shell, so they are called internal commands.

Dir

This command lists the contents of a folder.

Ipconfig

This command provides network information.

Systeminfo

Provides all information related to the hardware of the system.

Ping

This command helps to check a the server is reachable.

Tasklist

This command lists out all the tasks in the system that are currently running.

External Commands

There are several programs in the Windows system which may or may not come with the operating system. For example, Notepad, MS Word, MS Excel and any other software that we explicitly install after setting up the operating system.

All these can be invoked using external commands.

notepad

mspaint

Changing the directory and opening a specific document from that directory

Run other scripts using Shell

Below are some of the other scripting languages that can be run using the Windows shell or command prompt.

1. Powershell
2. Batch files with the extension “.bat”
3. PERL
4. Python
5. Javascript

And so on…

The VBA Shell function

VBA offers a built-in function named “Shell” to use the Windows shell and run or execute any program with ease.

Syntax

Pid=Shell ( <Program name>, [ <Window Style> ] )

Where

1. Program name is the name of any internal or external command or a script.
2. Window Style describes the behavior of the subsequent display window. It is an optional argument and can be any one of values in the table below (which are constants).
3. Pid is the variable that holds the Process id of the program that is initiated by the shell function. This is a value of double data type that is returned by the Shell function. It can be used later to terminate the program.

Note: If the execution of the function is not successful i.e. if not program is initiated, a 0 value is returned and it gets stored in the Pid variable.

Constant	Value	Description
vbHide	0	The window is opened and hidden . The focus is passed on to the hidden window.
vbNormalFocus	1	The window opens in its most recent position and size. The widow is visible and gains focus
vbMinimizedFocus	2	The window opens but remains minimized, but it has focus.
vbMaximizedFocus	3	The window is maximized with focus.
vbNormalNoFocus	4	The window opens in its most recent position and size. The widow is visible but does not gains focus. Only the currently active program retains its focus.
vbMinimizedNoFocus	6	The window opens and remains minimized. The program that is currently in focus retains focus.

Wherever we send keyboard input, the focus remains there and whatever keys are pressed or sent, gets typed in the window with focus. For example, if Notepad is in focus, whatever is typed using the keyboard or sendkeys, gets typed in that Notepad window.

Examples

1. Opening a Notepad window using normal focus and closes it using its program ID.

Sub shell_demo()

'declare a variable to hold the process id that is returned
Dim Pr_id As Double

'Use shell function to open a notepad using normal focus
Pr_id = Shell("notepad", vbNormalFocus)

'Kill or close the notepad using the same process id that was returned.
Pr_id = Shell("TaskKill /F /PID " & Pr_id, vbHide)

End Sub

2. The code below opens a Notepad file which already contains some data. So the file path should also be supplied in the parameters. The Notepad application opens with the focus maximized, as specified.

Sub shell_demo2()

'declare a variable to hold the process id that is returned
Dim Pr_id As Double

'Use shell function to open a notepad using maximized focus
Pr_id = Shell("notepad C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\sai invite.txt", vbMaximizedFocus)

'Kill or close the notepad using the same process id that was returned.
Pr_id = Shell("TaskKill /F /PID " & Pr_id, vbHide)

End Sub

In some cases, Windows does not understand spaces between the file names or file paths. In this event, we need to use double double quotes for the <Program name> parameter.

Pr_id = Shell("notepad ""C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\sai invite.txt""", vbMaximizedFocus)

This is typically not an issue with Windows 10.

3. Calling programs with different extensions

Sub shell_demo3()

'declare the required variables to hold the process ids that are returned
Dim Pr_id_not, Pr_id_word, Pr_id_acc, Pr_id1, Pr_id2 As Double

'Open a simple notepad using Shell function
Pr_id_not = Shell("Notepad", vbMaximizedFocus)

'Open a simple notepad using Shell function
Pr_id_word = Shell("winword", vbMaximizedFocus)

'Open a simple notepad using Shell function
Pr_id_acc = Shell("MsAccess", vbMaximizedFocus)

'Use shell function to open a folder using maximized focus
Pr_id1 = Shell("Explorer.exe C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\Training", vbMaximizedFocus)

'Use shell function to open a non- Excel file using normal focus
Pr_id2 = Shell("Explorer.exe C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\5.png", vbNormalFocus)

Debug.Print Pr_id_not
Debug.Print Pr_id_word
Debug.Print Pr_id_acc
Debug.Print Pr_id1
Debug.Print Pr_id2

End Sub	

4. Calling a batch file to run a text file using the Shell function.

Sub shell_demo3()

'declare a variable to hold the process id that is returned
Dim Pr_id_new As Double

'Use shell function to open a notepad using normal focus
Pr_id_new = Shell("""C:\Lakshmi\processme.bat"" ""C:\Lakshmi\textfile_run.bat""", vbMaximizedFocus)

'Kill or close the notepad using the same process id that was returned.
Pr_id_new = Shell("TaskKill /F /PID " & Pr_id_new, vbHide)

End Sub

Yes, that’s a lot of quotes in the Shell function’s <Program Name> parameter. Let me explain.

• One double quote each at the two ends “, mark the beginning and end of the string that refers to the name or path of the program to be run.

(““”C:\Lakshmi\processme.bat”” “”C:\Lakshmi\textfile_run.bat””“)

• The rest of the parameter (with one double quote in front and one at the end) consists of 2 strings. One is the path of the batch file to be run and the other is the path of the text file to be run. As you might know, a double quote needs to be used twice consecutively within a string to have one double quote there (to escape a quote).

(““”C:\Lakshmi\processme.bat“” “”C:\Lakshmi\textfile_run.bat“”“)

The final string that is passed in the shell is:

“C:\Lakshmi\processme.bat” “C:\Lakshmi\textfile_run.bat”

Asynchronous Execution

VBA code keeps executing without waiting to see if execution of the command is complete. So if we need to check for the completion of a command’s run before the next command executes, we need to use the VBA sleep or pause statements.

For example, to type something into a Notepad document, we can just open and type using the Shell function. But if we need to list out the files in a directory, we definitely need to verify that the run is complete, then export the list to Excel or CSV afterwards. In this case, we can use a sleep or wait statement to ensure the command is executed fully.

Here is a sample program that explains how to wait.

Sub shell_demo_4()

'Declaration of objects and variables
Dim sh_obj As Object
Dim counter As Integer

'Assign all values
Set ie_pr_id = Nothing
counter = 0
Set sh_obj = CreateObject("shell.application")
counter = sh_obj.Windows.Count
ie = Shell("C:\Program Files\Internet Explorer\iexplore.exe -nosessionmerging , -noframemerging", vbNormalFocus)

'wait till the window opens
Do Until sh_obj.Windows.Count = counter + 1
Loop

'set the new process id
Set ie_pr_id = sh_obj.Windows(sh_obj.Windows.Count - 1)

'Print the new process id
Debug.Print ie_pr_id

End Sub

Output of the program in the immediate window:

Internet Explorer

Conclusion

The built-in VBA Shell function to execute commands just like how you’d execute commands from the Windows command prompt. You can use it to open any kind of window during the execution of VBA macros, such as automation, web scraping, or file comparison.