VBscript to open mutiple batch files and wait for complete

Posted on 2011-09-27
Medium Priority
Last Modified: 2012-05-12

I've just started using VBScript, could anyone assist with an issue I have?  

I have mutiple batch files that I would like to open at the same time.  Once all the batch files complete, I want to open an excel document.  

I have a script that will open a single batch file, wait for completion and open excel.  I need to modify this to open mutiple batch files at the same time though.

I will need to open upto five batch files for this script; they will all be in the same directory.  This directory will contain other files as well as other batch files that are not needed for this script so I would like to specifically name the batch files I want to open.
sBatFile = "o:\Automation\test.bat"

Set oShell = CreateObject("WScript.Shell")
Set oFSO = CreateObject("Scripting.FileSystemObject")

If Not oFSO.FileExists(sBatFile) Then
   MsgBox "Could not find batch file, quitting!", _
      vbCritical + vbSystemModal, "Text search"
End If

' just in case there is spaces in the path
sBatFileShort = oFSO.GetFile(sBatFile).ShortPath

' Run the batch file, and let the VBScript wait for
' the batch file to finish
oShell.Run sBatFileShort, 1, True

'open Excel
Set Ex = CreateObject("Excel.Application")
	With Ex
	   .Visible = True
	   .Workbooks.Open "O:\Automation\Daily Template.xlsm", , , , ,"pw"
         End With

Open in new window

Question by:TelMaco
  • 5
  • 2
  • 2
LVL 11

Expert Comment

ID: 36711434
One solution would be that you create one batch file and run all batch files you need in it. In VBScript then call this batch file that contains all other needed.

Author Comment

ID: 36711536
I'm open to that, but now sure how to proceed.

The batch files are used to run sql and I want to run it all at once since some queries can take up to an hour.  If I run them back to back, I'd have to wait all day to finish everything.

Below are examples of two batch files, how would I combine them into one, so that everything ran at the same time?
chdir "O:\Automation"
SET tns_admin=C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
sqlplus user/pw$@db @example_1.sql

and then a second batch file that would need to run at the same time:

chdir "O:\Automation"
SET tns_admin=C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
sqlplus user/pw$@db @example_2.sql

Open in new window


Author Comment

ID: 36712896
I think I might have it

I created a new batch file: Master.bat

it contains the following :
chdir "O:\Automation"
start test_1.bat
start test_2.bat

This opens both batch files at the same time, so I should be able to just call the single master.bat from the vbscript.  I'll test it out later tonight, but it looks ok so far.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 11

Expert Comment

ID: 36714735
Or you can put something like this in script:
' Run the batch files, and let the VBScript wait for
' the batch file to finish
oShell.Run sBatFile1, 1, False
oShell.Run sBatFile2, 1, False
oShell.Run sBatFile3, 1, False
oShell.Run sBatFile4, 1, False
oShell.Run sBatFile5, 1, True

Open in new window


Author Comment

ID: 36717235
I tried that in the script, but it only waits for the last batch file to complete.  So if the last batch file takes 45 minutes, and one of the earlier ones takes 50 minutes, it does not wait.  It would need to wait for all of the batch files to complete.

I'm running some more tests today and will post the results.
LVL 59

Accepted Solution

Bill Prew earned 2000 total points
ID: 36717530
There isn't any built in capability for this, you will need to create your own.

One typical approach to this is to use a status file to indicate when the called batch file has ended.  So the VBS script would create a file for each BAT file that it will launch, and then a line needs to be added to each batch file to delete the specific file for that BAT script.  SO if I'm running "batscript1.bat" then I might create a matching file called "batscript1.txt".  After the BAT script does it's normal processing, it deletes that file.

The VBS that launches all the BAT scripts (in a mode that doesn't wait for each to finish) then goes into a loop checking the status files.  As long as any of them exist it keeps sleeping and looping.  Once they all have been deleted it continues on.

There are several ways the status thing can be done, but this is an example of a farily simple and straight forward approach.  The only thing to watch out for is if the BAT file hits a fatal problem end errors out, it will not delete its status file, and in that case the VBS could be stuck looping, so you need some max time to wait before moving on.

Another approach is to just look for the running processes that you launch in a loop until none are found. An example of something similar to this can be found at this recent question:



Author Comment

ID: 37154993

I make a few changes to get it right, I'll post the revised code I'm using, based on the link above:

A few notes -
I removed the On Error Resume Next, I'll build in some handling later
I removed "Const" as it was causing issues when I looped back
I also added a max iterations for the loop, to prevent an infinte one, I'll add an escape for that scenario to email me if it occurs, and I'll be increasing the sleep time between loops.  Afew little things to pretty it up, but this is working perfectly.

Thank you
dim WshShell
set WshShell = CreateObject("WScript.Shell")

WshShell.Run "cmd.exe /c daily_1.bat",2 ,FALSE
WshShell.Run "cmd.exe /c daily_2.bat",2 ,FALSE
WshShell.Run "cmd.exe /c daily_3.bat",2 ,FALSE
WshShell.Run "cmd.exe /c daily_4.bat",2 ,FALSE

arrTasks = Array( _
	"daily_1.bat", _
	"daily_2.bat", _
	"daily_3.bat", _

prev_counter = 0		
counter = 1
Iterations = 0

do while prev_count <> counter AND iterations < 3

	prev_count = counter
	wbemFlagReturnImmediately = &h10
	wbemFlagForwardOnly = &h20
	strComputer = "."
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")

	For Each strCommand In arrTasks
		Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Process WHERE CommandLine LIKE '%" _
		 & strCommand & "%'", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)

		For Each objItem In colItems
			counter = counter + 1


	wscript.sleep 3000
	Iterations = iterations +1

Open in new window


Author Closing Comment

ID: 37155074
Points go to Bill.  X's suggestion to use a master batch file and call it from vbs unfortunately did not work.  Once the master batch file had opened all the others, it closes itself and the vbs continues.  Since run times for the batch files varies depending on time of day, data availability, bandwidth, etc... I had to know that all batch files completed, not just the last one that was started.

Bill's suggestions encouraged me to learn more about vbs, and in particular the win32_process.   I apologize for leaving this q open for so long, it just took me a while to learn enough to understand how to manipulate the example Bill referenced.

I actually ended up created another method based on Bills suggestion of opening a Notepad instance for each Batch file that closed on complete.  I figured out how to use vbs to check for that notepad file and loop until it closed.  So there are a a few ways to do it.

Thanks EE!
LVL 59

Expert Comment

by:Bill Prew
ID: 37155414
Thanks, glad that was helpful, good job getting something that worked.


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question