1. 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. If you are trying to perform any action in Windows using Excel, all of these actions are used by the Windows Command Shell, so called. command line . The command line itself can be run simply by entering the CMD command in the Start menu . When you start it, you’ll see the well known Windows command line window. Command Shell from the VBA Excel level enables, among others:
- Running any programs from Excel, including running a macro from another Excel file.
- Controlling Windows tasks
- Managing files and directories on the disk, hence copying, deleting, renaming files (using batch file).
- And practically every action you could do with the Windows command line, like formatting a disk
In this article, we focus on the first three points, namely running programs, controlling Windows tasks and managing files.
2. Syntax of the Shell function in VBA Excel
How do you most easily use the Command Shell via VBA? Enter the Shell function in the code and give the right arguments by. the following syntax:
'OfficeInside.Org Shell(PathName, [WindowStyle As VbAppWinStyle = vbMinimizedFocus]) As Double
PathName – The path of the file that we 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 we can use in this argument. Whether we enter in the constant argument np vbHide, or eg value = 0, is not important for the operation of the command. They are equivalent. If we do not 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.|
3. How to run any program from Excel VBA
Using the command line, you can run, among others, selected 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:
'OfficeInside.Org shell_1 = Shell("notepad") shell_2 = Shell("mspaint") shell_3 = Shell("excel")
If you want to run a specific file with a specific program, eg 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:
'OfficeInside.Org shell_4 = Shell("excel d:\Sheet1.xlsx") 'or shell_5 = Shell("path.bat d:\Sheet1.xlsx")
To run any file from Excel VBA using the default program set in Windows, type the following in the function argument: explorer.exe , followed by the path of the file you want to run.
'OfficeInside.Org shell_6 = Shell("explorer.exe d:\Sheet1.xlsx")
In this way you opened another Excel file using Excel , using VBA.
4. How to copy, move and delete files and directories from Excel VBA
Among the simple commands for managing data on the disk, we can, among others:
- copy files
- delete files
- rename files
How to do it? To use the above-mentioned commands, we should create files with the extension bat , so-called. Batch files . The bat file in its content should have the command to copy, delete or eg rename files. If on the other hand you would like to substitute parameters for commands, this is not possible from the Shell function level . Separate functions of the VBA language are used for this.
If you want to delete any file using VBA, create a text file and name it eg delete.bat . In the body of the file enter the correct command, ie, for example:
DEL c: \ directory \ file.extension to delete a specific file in the directory
DEL c: \ directory \ * to delete the entire contents of the directory.
Now, when you want to run your bat file, use the shell function in the following way:
'OfficeInside.Org deleting= Shell("file_path\file_name.bat")
The content of the bat file if you want to copy any file to another directory:
COPY c: \ directory \ file.ext extension c: \ new_dir \
The content of the bat file, if you want to copy all files from a given directory to the second directory:
COPY c: \ directory \ * c: \ new_directory \
The Excel VBA code that launches the bat file:
'OfficeInside.Org copying= Shell("file_path\file_name.bat")
If you want to rename a particular file, your file with the extension of the bat should look like this:
RENAME C: \ path_to_file \ filename.extension new_file_name. Extension
Run the bat program using the VB code:
'OfficeInside.Org name_change= Shell("file_path\file_name.bat")
5. How to turn off the computer in VBA Excel
To close programs and turn off the computer from MS Excel, we should create files with the extension bat , like in one of the previous points . Below the content of these files for:
shutdown.exe / s
shutdown.exe / r
shutdown.exe / h
The article is part of the Excel vba course. The whole course can be found at this link: VBA course . Below is the file with the program code: