How to Run a Batch File and Wait Until It Finishes with VBA

How do I run a batch file and wait until it finishes with VBA?

Maybe the batch files are from a legacy project, or maybe they’re a short-hand way to accomplish something without needing a scripting language like Python or Ruby — it doesn’t REALLY matter though. Sometimes you just need to run a batch file from VBA!

There are lots of solutions to this problem scattered on Stack Overflow and blogs, but most of them assume that your VBA script does not depend on that results of that batch file operation. What if you need to wait until the batch file finishes though!?

The built-in Windows Script Host Object Model solves this problem for you. First, you’ll need to activate it though.

From the VBA window, click Tools > References. In the pop-up window, check the box next to Windows Script Host Object Model:

Make sure the Windows Script Host Object Model is selected

Cool! You know have access to the WshShell object, which will let you WshShell.Run batch files or even regular old CMD commands.

Suppose our batch file is named “Create New 2015 Project Folder.bat” and it’s sitting in C:\wshshell-fun. The following code will run that file and wait for the batch file to finish before moving on.

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Nice! Here’s a quick run-down of the juicy bits:

Line 10: we use Chr(34) to indicate a double quote character, ". We will need these because the full path to our batch file includes spaces, so variable strCommand becomes:
 
"C:\wshshell-fun\Create New 2015 Project Folder.bat"

Line 11-13: the WshShell.Run command returns a number, so we set it a variable we can later check for errors. (0 is the ideal output from WshShell.Run.)

If you want the operation to happen invisibly, set WindowStyle to 0 — if you actually want to see CMD come up and execute the command, though, you can set WindowStyle:=1 instead.

That final beauty is on Line 13. WaitOnReturn takes True or False — if you want VBA to, you know, WAIT until the command finishes, set it to True!

What if you want to use the some of the old CMD standby commands, like MOVE or REN? In that case, you need to:

1. Prepend your strCommand variable with cmd /K
2. Set WaitOnReturn to False

Let’s say that first, we need to run the batch file. Then, we need to rename the new folder that results from that batch file using some info from cells D3 and E3 on Sheet1. Sound good? Good — here’s the code in action:

Run batch file, wait on results, modify the results

Here’s the code that does it:

Here’s a link to the code above so you can review it side-by-side with the walk through below. Right-click, “Open in new window”.

Sweet! Let’s review how it works with our trusty 4-step VBA process:

Step 1 – Setup
Step 2 – Exploration
Step 3 – Execution
Step 4 – Cleanup

Per usual, we start with Step 1 – Setup. Essentially, this all happens on lines 11-12, where we assign a reference to Sheet1 and store it in the wks variable… easy cheesy.

Step 2 – Exploration is a cinch in this case too and is covered from lines 14-17. From our problem statement, we know that the first part of the intended folder name is in cell D3, and the second part is in cell E3, so we concatenate those values on Line 16 and store the result in strNewFolderName. Progress y’all!

Things get a lot more interesting in our Step 3 – Execution, which takes place from between lines 20-42.

First, from lines 20-29, we apply the same technique we learned above — create a double-quoted string like we’re operating from the command line and run it with WshShell.Run (using the wsh object).

Lines 31-42 get a bit more interesting though, as we need to run the REN CMD command. As mentioned above, when using CMD commands the Run method requires cmd /K to be prepended — which is exactly what we do on line 32. Our trusty Chr(34) is used to wrap the new folder name in double quotes (since it has spaces), and the strNewFolderName variable ALSO has spaces so we wrap it in Chr(34) double quotes too. This time around, we do not wait for a return (since we’re using a built-in CMD command), so line 35 has the WaitOnReturn option set to False.

Finally, Step 4 – Cleanup occurs on line 44, where we prompt the user with a Msgbox to indicate that the job is done. Nice!

Are you running batch files with ease from your VBA code? If not, let me know and I’ll help you get what you need! And if you’d like more step-by-step, no-bullshit VBA guides delivered direct to your inbox, join my email newsletter below.