how to run powershell script through excel VBA

Question

I am trying to use an Excel VBA macro to launch a PowerShell script if a certain condition is met. I'm not getting any errors in the code when it runs and I am able to manually run the PowerShell script from the Windows Explorer window without errors. However, when I execute the below VBA code, the PowerShell does not run and I am not sure why.

Dim x as Variant
If rs.RecordCount = 0 Then
    x = Shell("POWERSHELL.exe " & "H:\MyFolder\MyFile.ps1", 1)
End If

I can't tell if something in the VBA code is wrong because I'm not getting any run time errors but the PowerShell script is not actually running

UPDATE: with the extra quotes I am able to see the error message now that's popping up in the cmd window but it is still having issues with the space even with the extra quotes. I have moved my script to a different file path that doesn't have spaces but I now seeing errors that running scripts are disabled. This seems like it is no longer a code based problem. thank you all!

Is PS execution policy allowing PS1 scripts to run? Default is to block execution. Try adding the -noexit switch if the PS1 window immediately closes. — Mathieu Guindon
– Mathieu Guindon, Commented Jul 11, 2018 at 20:04
I tried and that did not help, I see the window pop open but then closes and the script isn't executed. I did the below: x = Shell("POWERSHELL.exe -noexit" & "H:\Operations\REPORTS\Reports2018\Balance Sheet\SLmarginJE.ps1", 1) — Meghan
– Meghan, Commented Jul 11, 2018 at 20:16
You can edit your question with any additional relevant info / what you've tried. — Mathieu Guindon
– Mathieu Guindon, Commented Jul 11, 2018 at 20:17
...-noexit" & "H:\Operations\... you haven't left a space between these arguments — Tim Williams
– Tim Williams, Commented Jul 11, 2018 at 20:18

Tim Williams · Accepted Answer · 2018-07-11 20:25:10Z

14

If your file path has spaces you need to put quotes around it.

x = Shell("POWERSHELL.exe -noexit " & _
           """H:\Operations\REPORTS\Reports2018\Balance Sheet\SLmarginJE.ps1""", 1)

answered Jul 11, 2018 at 20:25

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

4 Comments

johny why Over a year ago

at a command prompt, -noexit causes command window to remain at PS prompt after executing. Excluding -noexit returns to normal command-prompt. I don't understand your usage.

Tim Williams Over a year ago

@johnywhy - not my usage - that's from the OP

johny why Over a year ago

OP seems to have removed -noexit. Even if they hadn't removed it, if it's in your answer then that seems to indicate that you agree with it.

Tim Williams Over a year ago

It's in a comment of theirs below the question.

Collectives™ on Stack Overflow

how to run powershell script through excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related