r - 如何删除 VBA 中 shell.exec 中弹出的 shell？

Question

我正在尝试从 VBA 代码中调用 R 脚本，我正在使用VBA.CreateObject("WScript.Shell").Exec它显示 shell 窗口的弹出窗口，然后它会关闭。我的输出工作正常，但我不想要任何可以使用VBA.CreateObject("WScript.Shell").Run命令实现的窗口弹出窗口。这里的问题是，我无法将我的命令转换为使用VBA.CreateObject("WScript.Shell").Run. 想法.Run是将输出打印到输出文件中，然后读取输出文件。

'Function to call the Rscript to convert any case to title case
Public Function ToTitleCase(Text As String) As String
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim path As String
    
    path = """C:\Program Files\R\R-4.0.5\bin\Rscript.exe"" ""S:\01) Analytics\Temp\AnalystInput\ConvertToTitleCase.R"" -t " + """" + Text + """"
    ToTitleCase = shell.Exec(path).StdOut.ReadAll
    
End Function

这段代码工作正常。我想做的代码如下：

'Function to call the Rscript to convert any case to title case
Public Function ToTitleCase(Text As String) As String
    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim path As String
    
    'path = """C:\Program Files\R\R-4.0.5\bin\Rscript.exe"" ""S:\01) Analytics\Temp\AnalystInput\ConvertToTitleCase.R"" -t " + """" + Text + """"
    'ToTitleCase = shell.Exec(path).StdOut.ReadAll
    
    path = """C:\Program Files\R\R-4.0.5\bin\Rscript.exe"" ""S:\01) Analytics\Temp\AnalystInput\ConvertToTitleCase.R"" -t " + """" + Text + """" + " > " + """C:\Users\AnalystInput\output.txt"""
    Debug.Print path
    With CreateObject("WScript.Shell")
        ' Pass 0 as the second parameter to hide the window...
        .Run path, 0, True
    End With
    
    ' Read the output and remove the file when done...
    Dim strOutput
    With CreateObject("Scripting.FileSystemObject")
        strOutput = .OpenTextFile("C:\Users\AnalystInput\output.txt").ReadAll()
        '.DeleteFile "c:\out.txt"
    End With
    
End Function

我已经打印出来了path，它 "C:\Program Files\R\R-4.0.5\bin\Rscript.exe" "S:\01) Analytics\Temp\AnalystInput\ConvertToTitleCase.R" -t "VishalKumar" > "C:\Users\AnalystInput\output.txt"在 Windows 命令提示符下运行良好。

任何人都可以在这里帮助我。

标签： rexcelvbashellcmd