WScript.Shell to run a script with spaces in path and arguments from VBA

Question

I need to call a script (R) from VBA using the WScript.Shell. The path to the file contains spaces. Additionally, a series of arguments is passed to the script, of which several also contain spaces.

I have tried all thinkable combinations of quotes and double quotes around paths, arguments or even the whole string (notably this here). Nothing seems to work when arguments need to be passed (I either get "C:\Program" not recognized"- or "invalid-syntax"-errors in the command line).

Option Explicit
Private Const QUOTE As String = """"

Sub test()
    Dim WS_Shell As Object
    Set WS_Shell = VBA.CreateObject("WScript.Shell")
    Dim err_code As Long

    Dim path As String
    Dim arg_1 As String
    Dim arg_2_with_spaces As String

    Dim complete_cmd_str As String

    path = "C:\Program Files\R\R-3.3.2\bin\Rscript.exe"
    arg_1 = "F:\path\to\my\script.R"
    arg_2_with_spaces = "A string-arg with spaces"

    complete_cmd_str = "cmd.exe /K " & QUOTE & path & QUOTE & " " _
                    & QUOTE & arg_1 & QUOTE & " " _
                    & QUOTE & arg_2_with_spaces & QUOTE

    Debug.Print complete_cmd_str
    err_code = WS_Shell.Run(complete_cmd_str, vbMinimizedNoFocus, True)
End Sub

All of this is happening on Windows 7.

How can i run a command line script with spaces both in path and in the arguments to be passed? Any help greatly appreciated!

Update:

The code works if the prefix "cmd.exe /K " is removed from the command string. However, for testing and debugging, I would like to keep the shell window open once the script has run. How can this be achieved?

You should concate the string you want to execute in a variable and make sure to add " around all strings with spaces. A visible " can be added by """ — FloLie
@FloLie I have done that. I adjusted the code to make it clear. In the print-out, all 3 parts of the command are enclosed in quotes, as one would do in the shell directly. However, that does still not work ("C:\Program not recognized"). — Sam
You might try assigning Chr(34) to the QUOTE variable. BUT... If you type the entire thing in.literally, without variables, does it run? Figure that out, first, then try breaking it down... — Cindy Meister
@Cindy Meister Typing the whole command directly into an open shell window works. Executing WS_Shell.Run with the literal string does not. Calling .Run with the path and no or only unquoted arguments works as well. However, I need to be able to pass args containing spaces. — Sam

warner_sc warner_sc · Accepted Answer · 2018-04-04T15:45:59

I used to run scripts and pass arguments from Excel, this code works for me.

In order to run the script is very important that you first specify the path of your Rscript.exe and the path of your R script (.r file), then you can pass the arguments with spaces.

Sub run_R()

    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorcode As Integer
    Dim path As String

    path = Chr(34) & "C:\R\R-3.4.0\bin\i386\Rscript.exe" & Chr(34) & " " & Chr(34) & "C:\Users\userexample\Desktop\accionable.r" & Chr(34) & " " & Chr(34) & "Argument 1" & Chr(34)

    errorcode = shell.Run(path, style, waitTillComplete)

End Sub

R code to read the arguments:

args = commandArgs(trailingOnly = T)
cat(args[1])

WScript.Shell to run a script with spaces in path and arguments from VBA

Update:

1 Answers