• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

VBA Shell

You are here: Home / Excel VBA / VBA Shell
vba shell
September 15, 2017 by Philip Treacy

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.

search for cmd

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:

using dir

Or you can start programs, like Notepad:

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



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the sample workbook.


vba shell
Philip Treacy

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection

Center Across Selection Macro

Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category: Excel VBA
Previous Post:Excel Pivot Chart Drill Down Buttons
Next Post:Highlight Cells Referenced in Excel FormulasHighlight Referenced Cells

Reader Interactions

Comments

  1. Alexio

    January 31, 2022 at 9:26 pm

    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

    Reply
    • Alexio

      January 31, 2022 at 9:35 pm

      nvm, a simple double quotation of the inkscape command doees the trick!

      Reply
    • Philip Treacy

      February 3, 2022 at 10:44 am

      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

      Reply
  2. djiso

    January 21, 2021 at 8:47 pm

    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

    Reply
    • Catalin Bombea

      January 25, 2021 at 3:39 am

      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

      Reply
  3. Roohith

    February 14, 2020 at 4:16 pm

    The internal commands are not working. It gives an error saying invalid procedure call or argument

    Reply
    • Philip Treacy

      February 14, 2020 at 5:03 pm

      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

      Reply
  4. Pradeep Singh

    November 28, 2019 at 7:30 pm

    Hi,

    How can I use Shell command to open & close image, pdf etc.
    Please provide the code.

    Reply
    • Philip Treacy

      November 29, 2019 at 10:52 am

      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

      Reply
  5. Robin Clay

    September 9, 2019 at 3:31 am

    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]

    Reply
    • Philip Treacy

      September 9, 2019 at 11:52 am

      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

      Reply
  6. Arunkumar G

    December 14, 2018 at 1:20 am

    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

    Reply
    • Philip Treacy

      December 14, 2018 at 10:57 am

      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

      Reply
  7. george lentz

    May 15, 2018 at 12:36 am

    download of example workbook is corrupt shell

    Reply
    • Philip Treacy

      May 15, 2018 at 8:39 am

      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

      Reply
  8. Yan

    March 5, 2018 at 4:58 pm

    Really help me

    Reply
    • Philip Treacy

      March 6, 2018 at 9:18 am

      Glad to help

      Reply
  9. Sunny Kow

    September 18, 2017 at 2:48 pm

    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.

    Reply
    • Mynda Treacy

      September 19, 2017 at 4:12 pm

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x