Excel: Shell, close window and return to macro 3

[AcousticalConsultant]

Hi,

I need my macro to shell to DOS, run a program (which just translates a binary file to text), close the shell window and then continue running my code.

Since the "Shell" command runs asynchronously, I had to put a "wait" command in there for now, and on top of it, the window stays open after it's finished. Leaving it up to the user to close the window once the macro has finished.

Here's my code:

Private Sub cmdOK_Click()
If (txtInputFilename.Text <> &quot;&quot Then
myval = Shell(&quot;c:\program.exe &quot; & _
txtInputFilename.Text & &quot; c:\temp.asc&quot;, 6)
End
Else
MsgBox &quot;Invalid Filename!&quot;, vbCritical
Trans.Show
Exit Sub
End If
.
.
.
End Sub

Application.Wait Now + TimeValue(&quot;00:00:03&quot


So I need my code to wait until the shell window returns &quot;finished&quot;, and then to close that window and continue on with my code.

Also, as you can see, if the user enters a null string, it will return the msgbox &quot;invalid filename!&quot;, but how do I modify it so that if the user enters any file name that doesn't exist, the error will pop up?

 

[AcousticalConsultant]

Sorry, the *End Sub* line is obviously supposed to be after the *Application.Wait ...* line.

Just wanted to clear that up.

I'd appreciate any help!

Thanks!
Pascal

 

[dsi]

Try this:

Option Explicit

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = -1&
'-----------------------------------------------------------
Sub Main()
    Dim sPrgm As String
    
    sPrgm = "C:\Winnt\notepad.exe"
    
    RunUntilFinished sPrgm
    
    MsgBox "App is finished!"

    End
End Sub
'-----------------------------------------------------------
Public Sub RunUntilFinished(ByVal sApp As String)
    Dim lProcID As Long
    Dim hProc As Long

    ' Start the App
    On Error GoTo ErrHndlr
    lProcID = Shell(sApp, vbNormalFocus)
    On Error GoTo 0

    DoEvents

    ' Wait for the App
    hProc = OpenProcess(SYNCHRONIZE, 0, lProcID)
    If hProc <> 0 Then
        WaitForSingleObject hProc, INFINITE
        CloseHandle hProc
    End If
    Exit Sub

ErrHndlr:
    MsgBox &quot;Error starting App:&quot; & vbCrLf & _
           &quot;App: &quot; & sApp & vbCrLf & _
           &quot;Err Desc: &quot; & Err.Description
    Err.Clear
End Sub

 

[AcousticalConsultant]

Ok, that works fine as far as the error handling goes for an invalid filename, but it still doesn't automatically close my shell window once it's finished. It waits for me to close it manually, and then it continues the code.

Is there a way to make VB close the window by itself when the DOS program is finished running?

Thanks!

Pascal

 

[Mossoft]

Run the DOS program in a DOS shell using the automatic close command line option:

Shell(&quot;Command.com /C <dos program name>&quot;,6)

M

 

[AcousticalConsultant]

Mossoft,

Thanks, that works beautifully!


Regards,
Pascal