Run shell command synchronously from VBA whilst showing and capturing the output

Question

I want to run an R script from VBA for which I use the rscript.exe. I also pass this script 2 arguments. The execution should run synchronously and the output should both be displayed live and stored in a logfile.

So far I'm able to create a command, use the cpearson shellAndwait module to run it from VBA in a synchronous manner (VBA waits for shell command to execute before continuing) and to get the output live in a nice cmd prompt window.

batchCommand =   Chr(34) & r_location & Chr(34) & _
                 " --verbose " & _
                 Chr(34) & fp_rscript & Chr(34) & _
                 " " & Chr(34) & arg1 & Chr(34) & _
                 " " & Chr(34) & arg2 & Chr(34)  
errorcode = ShellAndWait (batchCommand, 0, vbMaximizedFocus, AbandonWait)

However, if my R script fails, errorcode is still 0 and the only way of getting output is pasting batchCommand in a cmd prompt

Adding cmd /K and wrapping everything in a second set of " is no option, as the shellandwait will wait for the user to close the cmd prompt manually.

Therefore I would like the output to be written to a text file for which I found this solution using powershell & tee.

However, my new batchCommand using powershell fails.

batchCommand =   "powershell.exe " & _
                    Chr(34) & r_location & Chr(34) & _
                   " --verbose " & _
                   Chr(34) & fp_rscript & Chr(34) & _
                   " " & Chr(34) & fp_rscript & Chr(34) & _
                   " " & Chr(34) & arg2 & Chr(34) & _
                   " | tee " & Chr(34) & logfile & Chr(34)

The batchCommand looks like this:

?batchCommand 
powershell.exe "C:\Users\...\bin\RScript.exe" --verbose "E:\...\stage_2\antares_model_builder_stage_2.R" "E:\...\stage_2\antares_model_builder_stage_2.R" "E:\...\2018-09-28-1232 srv6_19-20_basecase" | tee "E:\...\2018-09-28-1232 srv6_19-20_basecase\s2_rscript_logfile.txt"

As indicated in the comments I can no longer enter this batchCommand in cmd since it tries to interpret tee.

"'tee' is not recognized as an internal or external command, operable program or batch file."

Pasting the command in powershell gives:

Fatal error: cannot open file 'E:\SRV6\vanilla_hybrid\030': No such file or directory

So it has to do with spaces in the paths. Adding " " arround all arguments after powershell.exe gives the same error. However, using '&' works:

& "C:\Users\hgd253\appdata\Local\Microsoft\AppV\Client\Integration\5A67571D-6F50-4EAD-BB33-A0D712D7FC6A\Root\R\R-3.3.3\bin\RScript.exe" --verbose "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1337 srv6_19-20_basecase"

Now the question becomes how to pass this string as an argument to powershell -command? The help files states

If the value of Command is a string, Command must be the last parameter in the command , because any characters typed after the command are interpreted as the command arguments.

To write a string that runs a Windows PowerShell command, use the format: "& {}" where the quotation marks indicate a string and the invoke operator (&) causes the command to be executed.

So I try once more:

powershell -command "& {"C:\Users\hgd253\appdata\Local\Microsoft\AppV\Client\Integration\5A67571D-6F50-4EAD-BB33-A0D712D7FC6A\Root\R\R-3.3.3\bin\RScript.exe" --verbose "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1337 srv6_19-20_basecase"}"
Fatal error: cannot open file 'E:\SRV6\vanilla_hybrid\030': No such file or directory

Can anyone help me to get this final requirement (wrting output to logfile) to work?

It might be nice to see what `batchCommand` looks like before you execute it. — Matt, Sep 28 '18 at 10:41
`code` ?batchCommand powershell.exe "C:\Users\hgd253\appdata\Local\Microsoft\AppV\Client\Integration\5A67571D-6F50-4EAD-BB33-A0D712D7FC6A\Root\R\R-3.3.3\bin\RScript.exe" --verbose "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R" "E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1232 srv6_19-20_basecase" | tee "E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1232 srv6_19-20_basecase\s2_rscript_logfile.txt" — jayke, Sep 28 '18 at 10:44
Thank you for providing the command to be executed. As you see, code does not format well in comments. It would make it easier to understand if it were formatted as code in the question. Thanks. — lit, Sep 28 '18 at 11:24
edited the original post to display the ?batchCommand output in code brackets. — jayke, Sep 28 '18 at 11:30
"However, I can't seem to debug this batchcommand as pasting it into a cmd prompt gives me..." The reason you're getting your error about `Tee` is that it's _not_ recognized in a Command Shell (cmd prompt). If you open a PowerShell window and paste your _PowerShell_ command string in there, I'd imagine it would work much better. — FreeMan, Sep 28 '18 at 11:36
proceeded testing in the powershell environment and got the base command to work, but invoking it with the powershell prefix still fails. see edited original post — jayke, Sep 28 '18 at 12:10

score 0 · Answer 1 · answered Sep 28 '18 at 12:23

Found the solution. A mixture of ' and " is needed. The batchcommand should look like this:

?batchCommand
powershell.exe -command "& 'C:\Users\hgd253\appdata\Local\Microsoft\AppV\Client\Integration\5A67571D-6F50-4EAD-BB33-A0D712D7FC6A\Root\R\R-3.3.3\bin\RScript.exe' --verbose 'E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R' 'E:\SRV6\vanilla_hybrid\030 scripts\libraries\stage_2\antares_model_builder_stage_2.R' 'E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1337 srv6_19-20_basecase' | tee 'E:\SRV6\vanilla_hybrid\100 input buffer\2018-09-28-1337 srv6_19-20_basecase\s2_rscript_logfile.txt'"

The VBA code thus becomes:

batchCommand = "powershell.exe -command " & _
               Chr(34) & "& " & _
               Chr(39) & r_location & Chr(39) & _
               " --verbose " & _
               Chr(39) & fp_rscript & Chr(39) & _
               " " & Chr(39) & fp_rscript & Chr(39) & _
               " " & Chr(39) & arg2 & Chr(39) & _
               " | tee " & _
               Chr(39) & logfile & Chr(39) & Chr(34)

Run shell command synchronously from VBA whilst showing and capturing the output

1 Answers1