ShellAndWait not working

Question

I'm using ShellAndWait from here http://www.cpearson.com/excel/ShellAndWait.aspx and I keep getting 1 returned (which means the command didn't work in Windows). But when i paste my cmdLine into Start, Run box it runs fine. Any ideas? I'm using Excel VBA for this and here's my code that I'm calling ShellAndWait with What am I doing wrong?

thank you very much

Sub test()

Dim cmdLine As String

cmdLine = "C:\Documents and Settings\natalie.rynda\My Documents\Marta\Calling Files\_SFTP\Minacs.bat"

ShellAndWait cmdLine, 1000, vbHide, PromptUser

If ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 0 Then
MsgBox "yes!!!!!!"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 1 Then
MsgBox "1"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 2 Then
MsgBox "2"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 3 Then
MsgBox "3"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 4 Then
MsgBox "4"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 5 Then
MsgBox "5"
ElseIf ShellAndWait(cmdLine, 1000, vbHide, PromptUser) = 6 Then
MsgBox "6"
End If

End Sub

Answer 1

never mind, i just thought to try a different folder, C:\temp and it worked, so it's something with my folder, will keep testing to see what exactly

EDIT

It's still not working. now it's going through and returns 0 which is Success but the file is not posted to the SFTP site. Here's the exact code i'm using

Dim cmdLine As String

cmdLine = "C:\TEMP\Minacs.bat"

ShellAndWait cmdLine, 100000, vbHide, AbandonWait

If ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 0 Then


'does some stuff here, like send an email, omitted


ElseIf ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 1 Then
MsgBox "The file hasn't been uploaded." & vbCrLf & "Wait operation failed due to a Windows error."
ElseIf ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 2 Then
MsgBox "The file hasn't been uploaded." & vbCrLf & "The operation timed out."
ElseIf ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 3 Then
MsgBox "The file hasn't been uploaded." & vbCrLf & "An invalid value was passed to the procedure."
ElseIf ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 4 Then
MsgBox "The file hasn't been uploaded." & vbCrLf & "The system abandoned the wait."
ElseIf ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) = 5 Or ShellAndWait(cmdLine, 10000, vbHide, AbandonWait) = 6 Then
MsgBox "The file hasn't been uploaded." & vbCrLf & "The user abandoned the wait."
End If

Answer 2

Enter PAUSE as the last line in Minacs.bat to keep the command window open until you press a key. That should give you a chance to see what's happening.

You should also revise your VBA code. It calls the same ShellAndWait command at least twice ... once before the If block, and then again to start the If block. And it can keep trying until it hits an If/ElseIf condition which matches the return value from ShellAndWait.

Change your code to execute ShellAndWait once time only, and store the return value in a variable. Then you can evaluate the variable in a Select Case block.

Sub test()
Dim cmdLine As String
Dim strMsg As String
Dim lngResult As Long

cmdLine = "C:\TEMP\Minacs.bat"

'lngResult = ShellAndWait(cmdLine, 100000, vbHide, AbandonWait) '
lngResult = ShellAndWait(cmdLine, 100000, vbNormalFocus, AbandonWait)

Select Case lngResult
Case 0
    'does some stuff here, like send an email, omitted '
Case 1
    strMsg = "The file hasn't been uploaded." & vbCrLf & _
        "Wait operation failed due to a Windows error."
Case 2
    strMsg = "The file hasn't been uploaded." & vbCrLf & _
        "The operation timed out."
Case 3
    strMsg = "The file hasn't been uploaded." & vbCrLf & _
        "An invalid value was passed to the procedure."
Case 4
    strMsg = "The file hasn't been uploaded." & vbCrLf & _
        "The system abandoned the wait."
Case 5, 6
    strMsg = "The file hasn't been uploaded." & vbCrLf & _
        "The user abandoned the wait."
Case Else
    strMsg = "WTF?!!!"
End Select

If Len(strMsg) > 0 Then
    MsgBox strMsg
End If
End Sub