Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

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

  1. #11
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



    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



    _________________
    Kind Regards,
    Ivan F Moala

    ...Have a Nice day :_) ...

    [ This Message was edited by: Ivan F Moala on 2002-05-01 00:56 ]

  2. #12
    New Member
    Join Date
    Apr 2002
    Location
    athens
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !!!!!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •