How do you say to excel WAIT !!!! in VBA

[grovithis]

I have a sub call run_bat wich works great
unfortunatly the next sub routine nust wait until the bat file is run and then only start HOW!!!!!
NO idea even what the command should be

 

[Ivan F Moala]

I think Colos is OS dependant
If it doesn't close the Bat file down then
Try this;

What this routine does is similar to Colo's
It will open the Bat file up and run it,
checking to see if it is still running.
If it is finished it closes the application
down and continues on with the rest of your
code.<pre/>
Option Explicit

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Long) As Long
Private Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long

Private Const STILL_ACTIVE = &H103
Private Const PROCESS_QUERY_INFORMATION = &H400

Private Const WM_CLOSE = &H10
Private Const GW_HWNDNEXT = 2

Sub Run_bat()
Dim BatFileToRun As String

BatFileToRun = "C:test.bat" ' Name of Bat File

ShellAndClose BatFileToRun, vbMaximizedFocus ' Execute code

'// Your code here
MsgBox "Finished"
'//
End Sub

Private Function ShellAndClose(ByVal BatchFile As String, Optional ExecMode) As Long
Dim ProcessID As Long
Dim PID As Long
Dim hProcess As Long
Dim hWndJob As Long
Dim nRet As Long
Dim TitleTmp As String

On Error Resume Next
ProcessID = Shell(BatchFile, CLng(ExecMode))
If Err Then
ShellAndClose = vbObjectError + Err.Number
Exit Function
End If
On Error GoTo 0

hWndJob = FindWindow(vbNullString, vbNullString)

Do Until hWndJob = 0
If GetParent(hWndJob) = 0 Then
Call GetWindowThreadProcessId(hWndJob, PID)
If PID = ProcessID Then Exit Do
End If
hWndJob = GetWindow(hWndJob, GW_HWNDNEXT)
Loop

hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, ProcessID)

Do
TitleTmp = Space(256)
nRet = GetWindowText(hWndJob, TitleTmp, Len(TitleTmp))
If nRet Then
TitleTmp = UCase(Left(TitleTmp, nRet))
If InStr(TitleTmp, "FINISHED") = 1 Then
Call SendMessage(hWndJob, WM_CLOSE, 0, 0)
End If
End If

GetExitCodeProcess hProcess, nRet

Loop While nRet = STILL_ACTIVE

Call CloseHandle(hProcess)

ShellAndClose = nRet

End Function</pre>


_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font><MARQUEE/>...Have a Nice day :_) ...</MARQUEE>
This message was edited by Ivan F Moala on 2002-05-01 00:56

 

[grovithis]

Thanks a lot to Colo and to Ivan !!!!!!!
I will try both your codes BUT only next week
wednesday!!!!! It´s greek easter this weekend
ao i am off until next week wednesday, i will definatly tell youy guys if they work (of course they will LOL) and how!!
Again thanks a lot for the help !!!!!!