ShellAndWait for multiple Batch Files via Access VBA 1

[newestAF]

Here's the down and dirty. My db creates up to 4 batch files, runs all created files, creates messages and kills batch files. My problem is identifying when the batch files are done running. Before creating the messages, I need to know when all batch files are done. Doing a shellandwait function forces me to run each batch after another. I need all to run simultaneously. When they all finish, the db searches which reports refreshed and sends them out. How do I get the db to find out when the batches are done? I've researched for 2 weeks with no luck.

 

[xlbo]

get the batch files to write a Batch_x.done file when done - poll for that every so often from Access.

Also add a new line to the batch files to delete the batch_x.done files as their 1st task

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HughLerwill]

Try;

In a module;

Option Explicit

Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Public Declare Function WaitForInputIdle Lib "user32" (ByVal hProcess As Long, ByVal dwMilliseconds As Long) As Long
Public Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Public Const INFINITE = &HFFFF, SYNCHRONIZE = &H100000

Public Sub RunBatchFiles()

Dim Pid(0 To 3) As Long, hProc As Long, lmsTimeOut As Long
Dim i As Integer

Dim t As Single

lmsTimeOut = INFINITE

t = Timer

Pid(0) = Shell(Environ$("COMSPEC") & " /C " & "c:\mybat0.bat", vbHide)
Pid(1) = Shell(Environ$("COMSPEC") & " /C " & "c:\mybat1.bat", vbHide)
Pid(2) = Shell(Environ$("COMSPEC") & " /C " & "c:\mybat2.bat", vbHide)
Pid(3) = Shell(Environ$("COMSPEC") & " /C " & "c:\mybat3.bat", vbHide)

For i = 1 To UBound(Pid)
hProc = OpenProcess(SYNCHRONIZE, 0&, Pid(i))
If hProc <> 0& Then
WaitForInputIdle hProc, INFINITE
WaitForSingleObject hProc, lmsTimeOut
CloseHandle hProc
End If
Next

MsgBox "Batch files took " & Timer - t & " seconds to run"

End Sub

 

[newestAF]

HughLerwill, worked like a charm. You are the man!

 

[strongm]

Alternatively add a reference to the Windows Script Host Object Model library, and the following achieves much the same:

[blue]Public Sub RunBatchFiles()
    Dim wsh As WshShell
    Dim wshe(3) As WshExec
    
    Set wsh = New WshShell
    Set wshe(0) = wsh.Exec("c:\temp\test1.bat")
    Set wshe(1) = wsh.Exec("c:\temp\test2.bat")
    Set wshe(2) = wsh.Exec("c:\temp\test3.bat")
    Set wshe(3) = wsh.Exec("c:\temp\test4.bat")
    
    Do
    Loop Until wshe(0).Status And wshe(0).Status And wshe(0).Status And wshe(0).Status <> WshRunning
    
    MsgBox "all done"
End Sub[/blue]

Disadvantages - as it stands does not hide the command shell windows

 

[HughLerwill]

newestAF
Good. As you probably gathered my;

For i = 1 To UBound(Pid)

should have been

For i = 0 To UBound(Pid)

strongm
An interesting alternative.