CODEWITHSUNDEEP
vbaexcelpowershell
Petter_M
Petter_M

Reputation: 465

PowerShell Command from VBA

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?

Upvotes: 0

Views: 6209

Answers (1)

Jordan
Jordan

Reputation: 413

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.

Upvotes: 2

Related Questions