PowerShell Command from VBA

Question

I been trying to run a Powershell script from VBA, but got no success. The idea was to write the Powershell-coe inside the VBA (to automatize some processes). To simplify, below there are some attempts.

On VBA

 Sub test()

 Dim command_shell As Variant
 Dim code As String

 code = "PowerShell -Command ""{import-csv c:\Users\flyer\Documents\H2SO4.csv -delimiter ';' | select -first 3}"""

 command_shell = Shell(code, vbNormalFocus)

 Debug.Print code

 End Sub

Alternatively

 Sub test()

 Dim command_shell As Variant
 Dim code As String

 code = "import-csv c:\Users\flyer\Documents\H2SO4.csv -delimiter ';' | select -first 3"

 command_shell = Shell(code, vbNormalFocus)

 Debug.Print code

 End Sub

But unfortunately nothing runs and I cannot see the PowerShell screen.

To double-check if the code was not running I tried the command to put out the script:

 code = "PowerShell -Command {Start-Transcript}"

In that case the PowerShell appears shortly and desapears but no transcript file is generated.

Do you have better ideas to figure that out?

Jordan · Accepted Answer · 2018-08-17 22:57:58Z

Since you are effectively running this from a command prompt you need the & since cmd won't recognize a scriptblock.

code = "PowerShell -Command """& {import-csv c:\Users\flyer\Documents\H2SO4.csv -delimiter ';' | select -first 3}"""

Here is the applicable section from powershell.exe /?

-Command
Executes the specified commands (and any parameters) as though they were
typed at the Windows PowerShell command prompt, and then exits, unless
NoExit is specified. The value of Command can be "-", a string. or a
script block.

If the value of Command is "-", the command text is read from standard
input.

If the value of Command is a script block, the script block must be enclosed
in braces ({}). You can specify a script block only when running PowerShell.exe
in Windows PowerShell. The results of the script block are returned to the
parent shell as deserialized XML objects, not live objects.

If the value of Command is a string, Command must be the last parameter
in the command , because any characters typed after the command are
interpreted as the command arguments.

To write a string that runs a Windows PowerShell command, use the format:
    "& {<command>}"
where the quotation marks indicate a string and the invoke operator (&)
causes the command to be executed.

Thank you very much @Jordan! The missing point here if that VBA does not accept that definition of 'code' (with 3 quotes). Although if I put the following code = "PowerShell -Command ""& { Start-Transcript ; import-csv c:\Users\p119124\Documents\H2SO4.csv -delimiter ';' | select -first 3 | export-csv -Path C:\Users\p119124\teste123.csv -NoTypeInformation }""" it runs only the Start-Transcript...
I tried now the following: code = "PowerShell -Command " & "& {import-csv c:\Users\p119124\Documents\H2SO4.csv -delimiter ';' | select -first 3 | export-csv -Path C:\Users\p119124\teste123.csv -NoTypeInformation }" . It seems to run OK, but no output is generated teste123.csv
Solved it! I adjusted the admin config of the PC and everything went fine... @Jordan thanks again!
The escape characters for double quotes still have me a bit baffled but I'm glad it's working for you!

Collectives™ on Stack Overflow

PowerShell Command from VBA

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related