Snoobie Snoobie - 1 year ago 221
Bash Question

Excel VBA Wait For Shell to Finish before continuing with script

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!

Tim Tim
Answer Source

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
            MsgBox "Error: Timed Out", vbCritical, "Timed Out"
        End If

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

    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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download