Introduction | Example | Tutorial | Applications | Comments
Introduction - VBA Shell Function
Learn to open and close a file with the VBA Shell function. The Shell function returns the Process ID, PID, so you can kill the file when you’re done.
If you want to know how to activate a file or folder that was previously opened, check out my article on AppActivate and more.
Example - VBA Shell Function
Open and Close a Text File
Sub OpenClose() 'Open and Close a File Dim vPID As Variant 'Launch file vPID = Shell("notepad.exe ""C:\VBA\text.txt""", vbNormalFocus) 'Perform your actions here 'Kill file Call Shell("TaskKill /F /PID " & CStr(vPID), vbHide) End Sub
Write better macros in half the time
I want to help you write macros with my Excel VBA tutorials and VBA code examples. That’s why I developed this unique 3-part VBA training program to help you quickly learn VBA and gain access to my entire macro library without ever leaving Excel.
Tutorial - VBA Shell Function
Customize VBA Shell Input
Before I show you how to customize the VBA
Shell function, I want to tell you there are multiple ways to invoke the
Shell function. The following are all equivalent ways of opening the VBScript Timer. Notice the
Shell application returns a Variant. Well, technically it returns a Double.
Different Ways to Launch Applications with VBA Shell
Sub DailyTimer() 'Run timer Dim vPID As Variant 'Variation 1: vPID = Shell("wscript.exe ""C:\VBScripts\Timer.vbs""", vbNormalFocus) 'Variation 2: Shell "explorer.exe ""C:\VBScripts\Timer.vbs""", 1 'Variation 3: Call Shell("Explorer.exe ""C:\VBScripts\Timer.vbs""", vbNormalFocus) 'Variation 4 - Open with Notepad for Editing: vPID = Shell("notepad.exe ""C:\VBScripts\Timer.vbs""", vbNormalFocus) End Sub
Shell takes the form
Shell( pathname [, windowstyle ] ), where the windowstyle argument is optional. You can launch different applications by simply changing the pathname argument in the
Launch Notepad with Excel Macro
Sub LaunchNotepad() 'Run Notepad Call Shell("Explorer.exe ""C:\Windows\system32\notepad.exe""", vbNormalFocus) End Sub
The windowstyle property is an integer corresponding to how you want the program to run. The windowstyle properties are defined below:
You can use the Process ID, stored in
vPID, to kill the file or application when you’re done with it. Instead of opening a file, all you have to do is invoke the
TaskKill command with the same
Call Shell("TaskKill /F /PID " & CStr(vPID), vbHide)
This time the window was hidden using the windowstyle property
Note: When you open a file with explorer.exe, as was done in Variation 1 and Variation 2,
vPID is associated with the temporary explorer.exe Process ID. Hence, you will not be able to kill your program with
To learn how to use the
vPID to control previously opened files and folders, check out my article on AppActivate and more.
Application Ideas - VBA Shell Function
- Open Timesheet program
- Open Web Browser
- Open Terminal
- Connect to Server
- Send Commands
- Kill Terminal Connection with stored Process ID
- Add a Master Shortcut to Outlook to open all your applications
- Open an Exercise Reminder when you first step in the Office each morning
Now it’s your turn! Leave a comment and let me know how I can help you. Remember - Automate Responsibly and subscribe to my email list for more great content.