Call Powershell Script from VBA(with Parameter)

Question

I want to call a Powershell-Script from Excel VBA and pass 1 Parameter. Without VBA the Script works perfectly and does what it supposed to do.. As soon as the Call works, i would like to add 1 parameter, but first the successfull call...

But i cant manage to call it within Excel VBA. First ill show you my Powershell-Script:

#param([string]$server)
$server = "chvmes01"

$BasicPath = Split-Path $script:MyInvocation.MyCommand.Path

write-host $BasicPath

Invoke-Command -FilePath $BasicPath\IISReport.ps1 -ComputerName $server

Read-Host "Return drücken..."

In VBA I created this Code:

strCommand = "Powershell.exe -ExecutionPolicy ByPass -NoExit -File """ & BasicPath & """, 1"
Set WsShell = CreateObject("WScript.Shell")
WsShell.Run (strCommand)

strCommand looks like this:

Powershell.exe -ExecutionPolicy Unrestricted -NoExit -File "E:\Temp\Registry\EXCEL_ALLE\Version_AllFromExcel_Aktuell\IISReport\InvokeCommand.ps1", 1

Like this i get the following Error:

I have no idea what i can change anymore, i read so many forum posts and changed different things, but nothing worked!

I tried with strCommand without "" like this:

strCommand = "Powershell.exe -ExecutionPolicy ByPass -NoExit -File " & BasicPath & ", 1"

I tried

-ExecutionPolicy Unrestricted AND -ExecutionPolicy ByPass 

I tried with and without

-NoExit

I also tried

Shell (strCommand)

As i mentioned, the Scripts work perfectly without VBA! Can anyone help here?

Answer 1

I found the Solution!!

Actually the comment from @Pᴇʜ lead me to the answer!

I i mixed up the

Set WsShell = CreateObject("WScript.Shell")
WsShell.Run (strCommand)

with

Shell (strCommand)

The ", 1" is only for the Shell Command.

I only had to change my strCommand to

    strCommand = "Powershell.exe -ExecutionPolicy ByPass -NoExit -File " & BasicPath
    Set WsShell = CreateObject("WScript.Shell")
    WsShell.Run (strCommand)

and its working!

Now i can try to pass the parameter to powershell! Thanks a lot!

Answer 2

The error seems to indicate that a comma is being appended on to the end of your path. You likely just need to wrap it in single quotes. Try this:

strCommand = "Powershell.exe -ExecutionPolicy ByPass -NoExit -File '" & BasicPath & "'"