WScript.Shell运行脚本，在VBA的路径和参数中使用空格 [英] WScript.Shell to run a script with spaces in path and arguments from VBA

问题描述

我需要使用WScript.Shell从VBA调用脚本（R）。文件的路径包含空格。此外，一系列参数传递给脚本，其中一些参数还包含空格。

我已经尝试过将路径，参数或甚至整个字符串（尤其是我曾经运行脚本并传递参数

为了运行脚本，非常重要的一点是，首先指定Rscript.exe的路径和R的路径脚本（.r文件），则可以使用空格传递参数。

  Sub run_R（）
 
 Dim shell作为对象
 Set shell = VBA.CreateObject（ WScript.Shell）
 Dim waitTillComplete As布尔值：waitTillComplete = True 
 Dim样式为整数：style = 1 
 Dim错误代码为整数
 Dim路径为字符串
 
 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）& 参数1& Chr（34）
 
错误代码= shell.Run（路径，样式，waitTillComplete）
 
结束子
  

R代码读取参数：

  args = commandArgs（trailingOnly = T）
 cat（args [1]）$ ​​b $ b  

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?

解决方案

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运行脚本，在VBA的路径和参数中使用空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！