3

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?

Sam
  • 191
  • 1
  • 7
  • 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 Apr 04 '18 at 13:57
  • @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 Apr 04 '18 at 15:01
  • 1
    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 Apr 04 '18 at 15:23
  • @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 Apr 04 '18 at 15:45

1 Answers1

5

enter image description hereI 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])
warner_sc
  • 848
  • 6
  • 13
  • Thank you for your answer. Your code works and has helped me figure out that the problem seems to be the "cmd.exe /K " part of the command string (see update of the question). So, how do you get the shell window to stay open after the script has run (for testing/debugging)? – Sam Apr 05 '18 at 07:16
  • Use `Sys.sleep(time)` at the end of your R script to stop the shell window to close, example `Sys.sleep(60)` for 60 seconds, If you found my answer helpfull please mark it as an acceptable answer! :) – warner_sc Apr 05 '18 at 14:47