Office tutorials

Excel VBA Shell – How to control programs from Excel

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:

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.

Constant Value Description
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:

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:

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.

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.

Deleting files:

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

or

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:

copying:

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:

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:

Computer shutdowns:

shutdown.exe / s

Computer restart:

shutdown.exe / r

hibernation:

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:

Leave a Reply

Your email address will not be published.