Solved

VBscript to open mutiple batch files and wait for complete

Posted on 2011-09-27
9
3,193 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:TelMaco
  • 5
  • 2
  • 2
9 Comments
 
LVL 11

Expert Comment

by:X_layer
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.
0
 

Author Comment

by:TelMaco
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

0
 

Author Comment

by:TelMaco
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.

Thanks!
0
 
LVL 11

Expert Comment

by:X_layer
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

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:TelMaco
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.
0
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 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:

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

~bp
0
 

Author Comment

by:TelMaco
ID: 37154993
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
 

Author Closing Comment

by:TelMaco
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!
0
 
LVL 51

Expert Comment

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

~bp
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now