redirecting input to an executable from Excel VBA

Question

How do you redirect input to an executable from inside VBA? Specifically, why does the code below not work?

ChDir theRightDirectory
Set WshShell = VBA.CreateObject("WScript.Shell") 
WshShell.Run "runme < start.txt", 1, True

Or

RetVal = Shell("runme < start.txt", vbNormalFocus)

runme.exe does start up all right, but the input is not redirected and has to be type in manually in the command window. Also tried:

RetVal = Shell("type start.txt | runme.exe", vbNormalFocus)

Piping the output of type start.txt into runme.exe just plain returns a “file not found” error.

Yet when I type those various commands directly at the command line, they all work. Any insight you may have would be appreciated!

Answer 1

You are probably shelling out to a different folder than you expect.

You may have to change directory using CHDIR to the appropriate folder, or qualify your file names using the entire or relative path.

Answer 2

Execute the command this way:

WshShell.Run "%COMSPEC% /C runme < start.txt", 1, True

That way it will run it through the command line interpreter.

Answer 3

I've been made aware of a solution that is somewhat more elegant than the %COMSPEC% workaround. No need to write the input to start.txt; input can just be fed directly to the input stream. (My question should have made clear that this is also an option.) A bonus is that the user can get feedback from the output stream.

Set WshShell = VBA.CreateObject("WScript.Shell")
WshShell.CurrentDirectory = theRightDirectory
Set oExec = WshShell.exec("runme.exe")

' Write to the input stream
oExec.StdIn.Write "some input for the first prompt" & vbCrLf & _
                  "some more input" & vbCrLf 

' The output stream can be shown to the user, e.g.
sOutput = oExec.StdOut.ReadAll()
MsgBox (sOutput)