我可以将VBA变量传递给被称为Powershell脚本吗？ [英] Can I pass a VBA variable into a called Powershell script?

问题描述

我有一个Powershell脚本可以并入Excel VBA宏。请参考这个问题：
Powershell Regex：只替换多个空格与标签

这是Convert_To_Tab_Delimited.ps1中的代码：

 code> gc'foo.txt'| ％{$ _ -replace'+'，`t} |设置内容<我的路径> \temp.txt
  

我需要得到'foo .txt'作为我传递给它的VBA变量。基本上是从VBA中的选择文件对话框获取的路径字符串。 Powershell文件将由此语句调用：

  Shell（powershell.exe -ExecutionPolicy Unrestricted< pathname> \Convert_To_Tab_Delimited .ps1，1）
  

有没有办法将一个字符串变量作为参数传递给被调用Powershell脚本？任何对此的帮助将不胜感激！

解决方案

您只需要在脚本的顶部定义参数：

  param（[string] $ path）
 $ content = [IO.File] :: ReadAllText（$ path）
＃等
  

在VBA中，您需要传递参数：

  Shell（powershell.exe -ExecutionPolicy Unrestricted -File< pathname> \Convert_To_Tab_Delimited.ps1 -path& pathVariable& ，1）
  

I have a Powershell script to incorporate into an Excel VBA macro. Please reference this question: Powershell Regex: Replace only multiple spaces with tabs

This is the code in question 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:

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!

解决方案

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:

Shell("powershell.exe -ExecutionPolicy Unrestricted -File <pathname>\Convert_To_Tab_Delimited.ps1 -path """ & pathVariable & """", 1)

这篇关于我可以将VBA变量传递给被称为Powershell脚本吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！