How to Open the Windows Shell from VBA, With Use Cases
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.
The above commands are already a part of the shell, so they are called internal commands.
This command lists the contents of a folder.
This command provides network information.
Provides all information related to the hardware of the system.
This command helps to check a the server is reachable.
This command lists out all the tasks in the system that are currently running.
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.
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.
- Batch files with the extension “.bat”
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.
Pid=Shell ( <Program name>, [ <Window Style> ] )
Program nameis the name of any internal or external command or a script.
Window Styledescribes 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).
Pidis 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
|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.
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 " &amp;amp; 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 " &amp;amp; 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 " &amp;amp; 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.
- 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).
The final string that is passed in the shell is:
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:
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.