Excel VBA Wait For Shell to Finish before continuing with script

Question

Right now I have this VBA script:

Sub executeFTPBatch(ftpfileName)
     Call Shell("FTP -i -s:C:\Temp\" & ftpfileName & ".txt")
     On Error Resume Next
     Kill (C:\temp\" & ftpfileName & ".txt")
End Sub

The problem is that it kills the text file before the FTP script has even begun. I saw some wsh codes, but I wasn't sure of the syntax on how to use it with respect to calling the shell FTP. If you can help me with the correct syntax I would really appreciate it!

Solution

Use WScript's Shell instead, then you can check the status of the command

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function RunCMD(ByVal strCMD As String) As String
    'Runs the provided command
    Dim wsh As New wshShell
    Dim cmd As WshExec
    Dim x As Integer

    On Error GoTo wshError

    x = 0
    RunCMD = "Error"
    Set cmd = wsh.Exec(strCMD)

    Do While cmd.Status = WshRunning
        Sleep 100 'for 1/10th of a second
        x = x + 1
        If x > 1200 Then 'We've waited 2 minutes so kill it
            cmd.Terminate
            MsgBox "Error: Timed Out", vbCritical, "Timed Out"
        End If
    Loop

    RunCMD = cmd.StdOut.ReadAll & cmd.StdErr.ReadAll
    Exit Function

wshError:
    RunCMD = cmd.StdErr.ReadAll
End Function

This is a function I use, and it will return the status of the command including any errors.

(Almost forgot the Sleep declaration!)

(Edit 2: You will also want to include a reference to the Windows Script Host Object Model (wshom.ocx) so you can use the Intellisense features)