我可以将 VBA 变量传递给名为 Powershell 的脚本吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17034887/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I pass a VBA variable into a called Powershell script?
提问by Lawrence Knowlton
I have a Powershell script to incorporate into an Excel VBA macro. Please reference this question: Powershell Regex: Replace only multiple spaces with tabs
我有一个 Powershell 脚本可以合并到 Excel VBA 宏中。请参考这个问题： Powershell Regex: Replace only multiple space with tabs
This is the code in question Convert_To_Tab_Delimited.ps1:
这是有问题的代码 Convert_To_Tab_Delimited.ps1：
gc 'foo.txt'| % { $_ -replace ' +',"`t" } | set-content "<my path>\temp.txt"
I need to get 'foo.txt' to be a VBA variable that I pass to it. Basically a path string that is obtained from a select file dialog in VBA. The Powershell file will be called by this statement:
我需要让 'foo.txt' 成为我传递给它的 VBA 变量。基本上是从 VBA 中的选择文件对话框中获取的路径字符串。Powershell 文件将通过以下语句调用：
Shell(“powershell.exe -ExecutionPolicy Unrestricted <pathname>\Convert_To_Tab_Delimited.ps1″, 1)
Is there a way to pass a string variable as an argument to the called Powershell script? Any assistance with this would be greatly appreciated!
有没有办法将字符串变量作为参数传递给被调用的 Powershell 脚本？对此的任何帮助将不胜感激！
采纳答案by Mike Zboray
You just need to define parameters at the top of your script:
您只需要在脚本顶部定义参数：
param([string]$path)
$content = [IO.File]::ReadAllText($path)
# etc.
In VBA, you need to pass the parameter:
在VBA中，需要传递参数：
Shell("powershell.exe -ExecutionPolicy Unrestricted -File <pathname>\Convert_To_Tab_Delimited.ps1 -path """ & pathVariable & """", 1)
