ShellAndWait not working
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 Document开发者_如何学运维s\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
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
You can add
batchlog.txt
to allow the result that flashes quickly on the screen to be captured to a file.
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
精彩评论