The VBA Shell function runs a command in the operating system shell.
The shell refers to the interface, typically where you type commands, to run programs. This is called a command line interface or CLI.
In Windows, the shell is commonly known as the Command Prompt. To access it, click on the Windows button and type cmd (Windows 10). Windows finds the program for you, so click on it to start it.
In other versions of Windows the process for starting the Command Prompt is similar. Just search for cmd.
Once you are in the command prompt you can type commands like dir to list the folder contents:
Or you can start programs, like Notepad:
Commands like dir, copy, del etc are known as internal commands because they are built into the shell - they are part of the code that forms the shell, not separate programs.
Programs like Excel, Notepad etc are known as external commands because they are programs in their own right, but can be called or executed from the shell.
The method for calling internal and external programs using the VBA Shell function is different.
You can also use the Shell to run scripts like batch files, PowerShell scripts, PERL, Python etc.
VBA Shell Syntax
The syntax for calling Shell is
Shell (Program,WindowStyle)
Program can be the name of an internal or external command or a script. It can contain any arguments or switches required by the program, as well as the drive and path to the program itself
WindowStyle determines how the window of the called program behaves. WindowStyle is optional but if it is omitted, the program starts minimized with focus. You can specify the WindowStyle using a constant or the actual numeric value, as shown here:
| Constant | Value | Description |
| vbHide | 0 | The window is hidden, and focus is passed to the hidden window. |
| vbNormalFocus | 1 | The window has focus and appears in its most recent size and position. |
| vbMinimizedFocus | 2 | The window is minimized but has focus. |
| vbMaximizedFocus | 3 | The window is maximized with focus. |
| vbNormalNoFocus | 4 | The window appears in its most recent size and position, and the currently active program retains focus. |
| vbMinimizedNoFocus | 6 | The window is minimized, the currently active program retains focus. |
Focus is where keyboard input is sent to. If focus is on Excel and you type, the characters appear in Excel. If focus is on Notepad, the characters appear in Notepad.
When you use Shell it returns a Variant (Double) data type that contains the process ID of the program you called. You can use this PID to terminate the program later.
If your attempt to run a program with Shell was unsuccessful, it returns 0.
Examples of Using Shell
External Commands
After we declare a Variant variable called PID, we call Shell to start Notepad like this:
PID = Shell("notepad", vbNormalFocus)
Using vbNormalFocus starts Notepad with its most recent size and position, and changes focus to it.
To close the same instance of Notepad:
PID = Shell ("TaskKill /F /PID " & PID, vbHide)
If you wanted to open Notepad with a specific file then supply the filename, and path if needed:
PID = Shell("notepad c:\MyFiles\TextFile.txt", vbNormalFocus)
If you are using a shell that doesn't understand spaces in file names or paths, then you need to wrap the file name/path in two sets of double quotes, inside the double quotes that delimit the Program string:
I'm using Windows 10 and don't have that issue though.
But if you had wanted to open a file
c:\My Files\Text File.txt
and your shell required that this be wrapped in "", then you'd write the string like this
PID = Shell("notepad ""c:\My Files\Text File.txt""", vbNormalFocus)
The same goes for any path you need to specify for the actual command/script name. In this example I'm calling a batch file (Text Parser.bat) to process the text file (Text File.txt):
PID = Shell("""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""", vbNormalFocus)
All of those """ look a bit strange but let me explain. The first and last " mark the beginning and end of the string that specifies the program being called, including any parameters, switches and file(s) it will use:
"""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt"""
If we remove those " we are left with the Program string itself, which is composed of two separate strings, one for the path\batch file (red), and the other for the path\file the batch file will use (blue).
""c:\My Scripts\Text Parser.bat"" ""c:\My Files\Text File.txt""
When this is passed to the Shell one of the double " is removed so what is actually seen in the Shell is
"c:\My Scripts\Text Parser.bat" "c:\My Files\Text File.txt"
Which looks like two normally delimited strings.
Internal Commands
To call an internal command like dir, you must start an instance of the actual shell, which in Windows is cmd.exe. You then say that you want to use the dir command. The /k switch specifies that cmd should not terminate after dir has finished. You can terminate cmd later.
PID = Shell("cmd /k dir", vbNormalNoFocus)
Asynchronous Execution
Calls to the Shell are executed asynchronously, VBA will make the call and then continue without waiting for whatever program you called to finish whatever job you asked it to do.
This probably isn't an issue if you are just trying to open a text file in Notepad. But if you are say, trying to list files in a directory and then you want to import the resultant CSV into Excel, you need to make sure that the CSV file is complete before you do that import.
One way to do this would be to make your VBA sleep or pause.
Error handling
Make sure that you use error handling when making Shell calls. Just in case the program or file you want isn't in the location you expect, or is missing altogether.
Download Sample Workbook
The sample workbook I've prepared contains several examples of VBA Shell calls including the use of error handling and terminating programs you have started.
Enter your email address below to download the sample workbook.
Excel Pivot Chart Drill Down Buttons
Alexio
Hi,
thanks a lot for the article, it’s very helpfull!
Is there any way to get the Shell Function to “ignore” quotation marks?
I’m trying to open an inkscape file and run some inkscape commands, which unfortunately use “” aswell, thus resulting in an Error.
the line in question:
KommZ = Shell(“cmd /k cd C:\Users\admin\Desktop && inkscape –actions=”select-all:all;SelectionUnGroup” –export-filename=Diag1.pdf –export-pdf-version=1.5 –export-ignore-filters –export-area-drawing –export-latex Diagramm1.pdf”, 1)
thanks in advance!
Regards,
Alex
Alexio
nvm, a simple double quotation of the inkscape command doees the trick!
Philip Treacy
Hi Alexio,
try this
KommZ = Shell("cmd /k cd C:\Users\admin\Desktop && inkscape –actions=""select-all:all;SelectionUnGroup"" –export-filename=Diag1.pdf –export-pdf-version=1.5 –export-ignore-filters –export-area-drawing –export-latex Diagramm1.pdf", 1)Regards
Phil
djiso
hi,
don’t work with explorer (launch from vba) :
pid = Shell(“explorer.exe”, vbNormalFocus)
pid = Shell(“TaskKill /F /PID ” & pid, vbHide) ‘ don’t kill process
the pid is not the same than in task manager.
child windows problem ?
thanks for interesting job
Catalin Bombea
I think you should use Windows API functions to identify the window, it’s the most reliable way.
You have here an example:
https://stackoverflow.com/questions/25098263/how-to-use-findwindow-to-find-a-visible-or-invisible-window-with-a-partial-name
Roohith
The internal commands are not working. It gives an error saying invalid procedure call or argument
Philip Treacy
Hi Roohith,
I’ need to see your code to se what you are trying to do.
Please a topic on the forum and post your workbook there.
Regards
Phil
Pradeep Singh
Hi,
How can I use Shell command to open & close image, pdf etc.
Please provide the code.
Philip Treacy
Hi Pradeep,
This will open an image/pdf with the default program
PID = Shell(“explorer something.png”, vbNormalFocus)
PID = Shell(“explorer something.pdf”, vbNormalFocus)
Regards
Phil
Robin Clay
Thank you for that WorkBook.
But What I sought was a way in Excel VBA to SHELL running as Administrator.
The command is simply e.gg. Shell “DIR C: /S >myfile.txt” – but that will not include e.g. program files, unless it runs as Admin.
[This is my own home computer, that nobody else uses, and it has no passwords]
Philip Treacy
Hi Robin,
Be default all users should have read access to Program Files so you shouldn’t need to be Administrator to list the files. If you are getting an error here, it may be because you don’t have write access to the root of C: to create myfile.txt?
You can run cmd.exe as Administrator using this
Sub MyShell()
Dim oShell As Object
Set oShell = CreateObject(“Shell.Application”)
oShell.ShellExecute “cmd.exe”, “”, “”, “runas”, 1
End Sub
But it will still prompt you to give permission for it to run, so not much help if you want the whole thin to be automated.
Regards
Phil
Arunkumar G
Hi, this is’nt working for my case
args = ActiveWorkbook.Path & “\CopyLogs.py ”
pid = Shell(“C:\Users\a11g\AppData\Local\Programs\Python\Python37\python.exe ” & args, vbMaximizedFocus)
Can you tell me if some syntax problem
Philip Treacy
Hi,
What error message are you getting?
Is it a Python error or VBA error?
Is your CopyLogs.py file in the same folder as your workbook?
Is that the correct path to your Python executable?
If you run the Python script from the command line does it work?
regards
Phil
george lentz
download of example workbook is corrupt shell
Philip Treacy
Hi George,
The download works fine for me. What problem exactly are you having?
Are you receiving an error message?
Is the file that you download named Excel-VBA-Shell-Function.xlsm? If the file extension is not .xlsm then your browser is probably changing this. Some browsers have the really annoying habit of doing this.
Right click the download link and make sure the file has the correct name before you start the download.
Let me know if you still have issues.
Regards
Phil
Yan
Really help me
Philip Treacy
Glad to help
Sunny Kow
Although I rarely use the command prompt nowadays, this article bring back fond memories of the time when I was still using DOS, green/grey text monitors (no graphics or mouse way back then), batch (.BAT) files, BASIC programming etc.
Mynda Treacy
Ah, batch files. Fond memories. I used to write a lot of batch scripts in DOS. I once rolled out dozens of W98 machines with a DOS boot disk and batch files run from networked drives.
Phil