VBscript to open mutiple batch files and wait for complete

Hi,

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"
   WScript.Quit
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

TelMacoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bill PrewConnect With a Mentor Commented:
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:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_27327334.html?cid=1576

~bp
0
 
X_layerCommented:
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.
0
 
TelMacoAuthor Commented:
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

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TelMacoAuthor Commented:
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.

Thanks!
0
 
X_layerCommented:
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

0
 
TelMacoAuthor Commented:
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.
0
 
TelMacoAuthor Commented:
Hi

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", _
	"daily_4.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
		Next

	Next 

	wscript.sleep 3000
	Iterations = iterations +1
Loop

Open in new window

0
 
TelMacoAuthor Commented:
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!
0
 
Bill PrewCommented:
Thanks, glad that was helpful, good job getting something that worked.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.