Kam Kam - 11 months ago 159
Bash Question

code to run shell capture error code and wait in vba

I was able to come up with VBA code that runs my shell program.
But I am finding that it is not waiting.
It will go to the next line of code and move the file before the shell even starts to process the file.

Dim ShellString As String
ShellString = _
"cmd.exe /k " _
+ "cpdf.exe" + _
" -split " + _
Chr(34) + PDFFile + Chr(34) + _
" -o " + _
Chr(34) + ActiveWorkbookPath + "\" + OutputFileSubFolder + "\" + ProcessingFileWithoutExtension + "_%%%.pdf" + Chr(34)

Shell ShellString

' THERE IS SOME CODE HERE THAT MOVES MY FILE.
' THIS CODE RUNS BEFORE THE SHELL COMMAND RUNS
' THAT'S THE PROBLEM. IT MOVES THE FILE BEFORE SHELL HAS A CHANCE TO WORK ON IT...


So there are 2 desired things.
1) I would like to capture an error code from the shell command that runs.
2) Hence, I would like to also not do any other logic, until error code is returned.... i.e., don't move to next line of code until shell finishes and error code is returned...

Answer Source

VBA's Shell function is asynchronous, which is the problem that you are running into. In contrast, VBScript's Run method is much more flexible. It is easy enough to call it from VBA. To your code add:

Dim WshShell As Object
Dim ErrorCode As Long
Set WshShell = VBA.CreateObject("WScript.Shell")

ErrorCode = WshShell(ShellString,1,True)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download