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!
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)
I prefered omegastripe's suggest:
Public Sub RunCMD(ByVal strCMD As String)
'Runs the provided command
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
Call wsh.Run(strCMD, 2, True)
End Sub
With 2 as second param to avoid windows to popup
CreateObject("WScript.Shell").Run("FTP -i -s:C:\Temp\" & ftpfileName & ".txt", 1, True, last argumentTruemeans that macros will wait for the command to complete before continuing execution.