Can I pass a VBA variable into a called Powershell script?

Question

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!

Mike Zboray · Accepted Answer · 2013-06-12 07:43:03Z

1

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)

edited Jun 12, 2013 at 7:43

answered Jun 11, 2013 at 1:41

Mike Zboray

41.1k3 gold badges101 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lawrence Knowlton Over a year ago

Wow, I have some reading to do! So you'd use an ampersand and not a '+' sign in the VBA code?

Mike Zboray Over a year ago

@LawrenceKnowlton I'll admit I haven't done VBA in a long time and I had to double-check that. I guess either one works here. This answer suggests you would want to prefer &:stackoverflow.com/a/1727709/517852

Lawrence Knowlton Over a year ago

Ah, great! Sounds like it's easier to avoid issues by using & to concatenate strings! Thank you!

Lawrence Knowlton Over a year ago

Any ideas why I can't seem to pass the parameter, it's like the PS script runs but doesn't do anything.

Mike Zboray Over a year ago

@LawrenceKnowlton Two ideas: 1) Bypass is not a valid value for ExecutionPolicy. I think you mean Unrestricted. 2) If you are passing a path that contains spaces you will need to quote it. I have made edits to my answer.

|

Collectives™ on Stack Overflow

Can I pass a VBA variable into a called Powershell script?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related