Last Updated on January 11, 2023 by token
CMD Shell in VBA Excel – what it is and how to use it
In this article you will learn how to control other Windows programs from Microsoft Excel using VBA code. If you are trying to do any action in Windows using Excel, the most of these actions are used by the Windows Command Shell, called alse command line . The command line can be run simply by entering the CMD command in the Widnows Start menu. When you enter it, you’ll see the Windows command line window. Command Shell from the VBA Excel level enables:
- Running any programs from Excel, including running a macro from another Excel file.
- Controlling Windows tasks
- Managing files and directories on the disk, copying, deleting, renaming files (when you are using batch file).
- And every action you can do with the Windows command line, like start formatting your disk 🙂
In this article, we focus on the first three points.
Syntax of the Shell function in VBA Excel
How do you most easily use the Command Shell via VBA? Enter the VBA Shell function in the code and give it parameters:
'OfficeInside.Org Shell(PathName, [WindowStyle As VbAppWinStyle = vbMinimizedFocus]) As Double
PathName – The path of the file that you want to run or the name of the program.
WindowStyle – Optional argument. You can specify in which mode the program will start. Below are all the options that you can use in this param. Whether you enter in the constant argument ie. vbHide, or value = 0, is not important for the operation of the command. They are equivalent. If you don’t complete this argument, the default argument is 1, so vbNormalFocus.
|vbHide||0||The program runs in the background window. The program window is invisible.|
|vbNormalFocus||1||The program runs in a visible window in the normal size. The window of the new program is now an active window.|
|vbMinimizedFocus||2||The program starts in a minimized size window. The window of the new program is now an active window.|
|vbMaximizedFocus||3||The program starts in the full-size window. The window of the new program is now an active window.|
|vbNormalNoFocus||4||The program starts in the last used window size and the last used position on the screen. The currently used window remains active.|
|vbMinimizedNoFocus||6||The program starts in a minimized window. The currently used window remains active.|
How to run any program from Excel VBA
Using the VBA Shell function, you can run, most of programs delivered with the Windows system, such as Paint, Notepad, etc. To do this, type the program name in quotes in the first Shell function argument:
Sub vbaShellFunction() 'OfficeInside.Org
Shell ("excel")End Sub
If you want to run a specific file with a specific program, ie. run an Excel file with MsExcel, enter the program name in the Shell function argument, and after the spacebar the file you want to open in it:
To run any file from Excel VBA using the default program set in Windows, type the following in the function argument: explorer.exe.
In this way you opened another Excel file using Excel and VBA.
How to copy, move and delete files and directories from Excel VBA
How to use simple commands to manage data on disk? For example using VBA Excel you can:
- copy files
- delete files
- rename files
All of these activites you can do using VBA Shell function and CMD command. Remember that, after cmd command you need to type this letters: \k. All examples are listed here:
Delete file using VB code:
Shell ("cmd /k delete FILE_PATH\FILE_NAME.FILE_EXTENSION")End Sub
Copy file using VB code:
Shell("cmd /k copy C:\FILE_PATH\FILE_NAME.FILE_EXTENSION C:\NEW_FILE_PATH\")
'copy whole directory
Shell("cmd /k copy C:\FILE_PATH\* C:\NEW_FILE_PATH\")End Sub
Rename file using VB code:
Shell ("cmd /k RENAME C:\FILE_PATH\FILE_NAME.FILE_EXTENSION FILE_NAME.FILE_EXTENSION")End Sub
How to run batch file from Excel VBA?
How to do it? To use the above-mentioned commands, you should create files with the extension bat, called batch files. The bat file in its content should have the shell command ie. copy, delete or rename files. If on the other hand you would like to add parameters for commands, this is not possible from the Shell function level. Other functions of the VBA language are used for this.
How to turn off the computer in VBA Excel
To close programs and turn off the computer from MS Excel, you should create batch files with the extension bat. Below you can find code for these files:
- Computer shutdown: shutdown/s
- Computer restart: shutdown/r
- Computer hibernation: shutdown/h
Here is an example:
Shell ("cmd /k shutdown/h")End Sub
You can download all examples here:
This article is part of the Excel vba course. The course can be found here: VBA course.