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!

Community
  • 1
  • 1
Lawrence Knowlton
  • 99
  • 1
  • 3
  • 10

1 Answers1

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)
Mike Zboray
  • 39,828
  • 3
  • 90
  • 122
  • Wow, I have some reading to do! So you'd use an ampersand and not a '+' sign in the VBA code? – Lawrence Knowlton Jun 11 '13 at 02:00
  • @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 `&`:http://stackoverflow.com/a/1727709/517852 – Mike Zboray Jun 11 '13 at 04:15
  • Ah, great! Sounds like it's easier to avoid issues by using & to concatenate strings! Thank you! – Lawrence Knowlton Jun 11 '13 at 22:13
  • Any ideas why I can't seem to pass the parameter, it's like the PS script runs but doesn't do anything. – Lawrence Knowlton Jun 12 '13 at 02:23
  • @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. – Mike Zboray Jun 12 '13 at 07:43
  • Cool, thanks! I'll test it later. One question I have is why the need for the multiple quotes? The extra sets of double quotes don't make sense to me. Could you explain? Thank you! – Lawrence Knowlton Jun 12 '13 at 14:28
  • This still doesn't work, I must have something wrong somewhere. – Lawrence Knowlton Jun 12 '13 at 23:41
  • @LawrenceKnowlton So I called the variable "path", but is it going to be a full path or just a file name. If it is just a file name, then it's going into either powershell's current directory or its working directory (yes, those are different), which might not be where you expect. I believe calling Read/WriteAllText will go to `[Environment]::CurrentDirectory`. I would suggest passing the full path. Beyond that you may want to ask another question. – Mike Zboray Jun 13 '13 at 00:09
  • Currently I'm using the following code to replace the tabs in my file: `code` param([string]$FileToOpen) gc $FileToOpen | % { $_ -replace ' +',"`t" } | set-content "\temp.txt" $FileToOpen is a pathname to file. – Lawrence Knowlton Jun 13 '13 at 01:15