code to run shell capture error code and wait in vba

Question

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...

Konstantin · Accepted Answer · 2019-01-14 16:04:50Z

2

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.Run(ShellString, 1, True)

edited Jan 14, 2019 at 16:04

Konstantin

8161 gold badge13 silver badges33 bronze badges

answered Dec 22, 2017 at 14:46

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

code to run shell capture error code and wait in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related