问题

以下是翻译好的代码部分：

原始宏代码：

Private Sub Macro()
    Dim ExecuteCommand As String
    ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users\MyUser\Cool Calculations.R'"""
    Shell ExecuteCommand
End Sub

您想要替换 "MyUser" 部分，改成使用 $env:UserName，但是出现问题的宏代码如下：

Private Sub Macro()
    Dim ExecuteCommand As String
    ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users$env:UserName\Cool Calculations.R'"""
    Shell ExecuteCommand
End Sub

您已经手动在 PowerShell 中运行了相同的命令，并且它正常工作：

& "C:\Program Files\R\R-4.2.2\bin\Rscript.exe" "C:\Users$env:UserName\Cool Calculations.R"

但是在宏中添加 $env:UserName 部分似乎不起作用。这个问题可能是因为 VBA 宏不会解释 PowerShell 变量。要在 VBA 中使用环境变量，您可以使用 VBA 的 Environ 函数。以下是修改后的宏代码：

Private Sub Macro()
    Dim ExecuteCommand As String
    Dim UserName As String
    UserName = Environ("UserName")
    ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users\" & UserName & "\Cool Calculations.R'"""
    Shell ExecuteCommand
End Sub

这段代码会获取当前用户的用户名并将其替换到 PowerShell 命令中，以便宏可以正常工作。

Private Sub Macro()
    Dim ExecuteCommand As String
    ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users\MyUser\Cool Calculations.R'"""
    Shell ExecuteCommand 
End Sub

Private Sub Macro()
    Dim ExecuteCommand As String
    ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users$env:UserName\Cool Calculations.R'"""
    Shell ExecuteCommand 
End Sub

& "C:\Program Files\R\R-4.2.2\bin\Rscript.exe" "C:\Users$env:UserName\Cool Calculations.R"

答案1

得分: 2

* 正如Mathias指出的，只有`"..."`字符串（[可扩展字符串](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules#double-quoted-strings)）在Powershell中执行变量扩展（内插）；`'...'`字符串（[纯字符串](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules#single-quoted-strings)）不执行。

* 因此，你必须传递给PowerShell _最终_ 视为 `"C:\Users\$env:UserName\Cool Calculations.R"` 参数（另外提一句：考虑简化为 `"$env:USERPROFILE\Cool Calculations.R"`）；因为你是从VBA调用的，这需要：
  * 使用 `""...""` 将 `"` 字符嵌入到VBA `"..."` 字符串中...
  * _并且_ 为了`powershell.exe`，Windows PowerShell CLI（它将其`-Command`参数作为 `"..."` 字符串接收，因此任何要保留作为命令一部分的 `"` 字符必须进行转义）
  * 也就是说，你需要类似下面的内容（此处为了可读性添加了空格）

        " PowerShell -Command "" & '...' \""...\"" "" "

具体代码：

Private Sub Macro()
Dim ExecuteCommand As String
ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' ""C:\Users$env:UserName\Cool Calculations.R"""""
Shell ExecuteCommand
End Sub

<details>
<summary>英文:</summary>

&lt;!-- language-all: vb --&gt;

* As Mathias points out, only `&quot;...&quot;` strings ([expandable strings](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules#double-quoted-strings)) perform variable expansion (interpolation) in Powershell; `&#39;...&#39;` strings ([verbatim strings](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_Quoting_Rules#single-quoted-strings)) do not.

* Thus you must pass what PowerShell _ultimately_ see as a `&quot;C:\Users$env:UserName\Cool Calculations.R&quot;` argument (as an aside: consider simplifying to `&quot;$env:USERPROFILE\Cool Calculations.R&quot;`); since you&#39;re calling from VBA, this requires:
  * Using `&quot;&quot;...&quot;&quot;` to embed `&quot;` chars. inside a VBA `&quot;...&quot;` string...
  * _and_ `\`-escaping for the sake of `powershell.exe`, the [Windows PowerShell CLI](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_powershell_exe) (which receives its `-Command` argument as a `&quot;...&quot;` string, so any `&quot;` chars. to be retained as part of the command must be escaped)
  * That is, you need something like the following (sic; spaces added for readability)

        &quot; PowerShell -Command &quot;&quot; &amp; &#39;...&#39; \&quot;&quot;...\&quot;&quot; &quot;&quot; &quot;

Specifically:

Private Sub Macro()
Dim ExecuteCommand As String
ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' ""C:\Users$env:UserName\Cool Calculations.R"""""
Shell ExecuteCommand
End Sub

答案2

得分: 0

mklement0的回答非常优雅，已测试可行。但我尝试了没有转义的命令，在Windows PowerShell下似乎不需要，正如Mathias建议的那样，它也可以工作。我用Get-ChildItem cmdlet替换了我没有安装的Rscript.exe，如下所示：

<pre><code>
Sub RunPSFromVba()
    Dim ExecuteCommand As String
    'ExecuteCommand = "PowerShell -Command ""& 'C:\Program Files\R\R-4.2.2\bin\Rscript.exe' 'C:\Users$env:UserName\Cool Calculations.R'"""
    ExecuteCommand = "PowerShell -NoExit -Command ""& 'Get-ChildItem' 'C:\Users$env:UserName\Downloads'"""
    Shell ExecuteCommand
End Sub
</code></pre>

使用PowerShell -NoExit，我在执行后保持了PowerShell窗口的活动状态，我得到了这个屏幕截图：

希望这也对你有帮助。