Call PowerShell script in VBA with variables as parameters

Question

I am making a VBA script that will take items from an Excel file and pass those off to a PowerShell script.

I have been trying to add the variables, but when I run the script, it opens PowerShell for a split second and doesn't run the script.

strCommand = "powershell.exe -command Start-Process -Verb RunAs powershell.exe \"" -ExecutionPolicy Unrestricted -NoExit -file `\""C:\Users\mbaradihi\Desktop\AutoADTest\test.ps1`\"" -FName """ & FName & """ -middleIn """ & middleIn & """ -LName """ & LName & """ -Branch """ & Branch & """ -Title """ & Title & """ -employeeNum """ & employeeNum & """ -company """ & company & """ -dept """ & dept & """ -ad """ & ad & """ -city """ & city & """ -state """ & state & """ -zip """ & zip & """ -manager """ & manager & ""

This code runs the script, but it doesn't use the variables, it passes through what I typed in for the variables and uses that as the parameters.

strCommand = "powershell.exe -command Start-Process -Verb RunAs powershell.exe \"" -ExecutionPolicy Unrestricted -NoExit -file `\""C:\Users\mbaradihi\Desktop\AutoADTest\test.ps1`\"" FName middleIn LName Branch Title employeeNum company dept ad city state zip manager \"""

Answer 1

Just like your script-file path, your arguments must also be enclosed in what PowerShell ultimately sees as `\", which is represented as `\"" inside a VBScript string literal (see this answer for the PowerShell command line being invoked and why two, nested calls to powershell.exe are required).

For instance, to add the named -FName argument with its value (ultimately) enclosed in (just) double quotes:

"... -FName `\""" & FName & "`\"" ..."

To put it all together:

strCommand = "powershell.exe -command Start-Process -Verb RunAs powershell.exe \"" -ExecutionPolicy Unrestricted -NoExit -file `\""C:\Users\mbaradihi\Desktop\AutoADTest\test.ps1`\"" -FName `\""" & FName & "`\"" -middleIn `\""" & middleIn & "`\"" -LName `\""" & LName & "`\"" -Branch `\""" & Branch & "`\"" -Title `\""" & Title & "`\"" -employeeNum `\""" & employeeNum & "`\"" -company `\""" & company & "`\"" -dept `\""" & dept & "`\"" -ad `\""" & ad & "`\"" -city `\""" & city & "`\"" -state `\""" & state & "`\"" -zip `\""" & zip & "`\"" -manager `\""" & manager & "`\"" \"""

---

Since constructing this string by hand is tedious and it is easy to make mistakes - which result in quiet failure - it's better to construct the string algorithmically, as the following example shows:

' The *full* target script path.
' Note that the elevated process will see C:\Windows\System32 as its working dir.
' (In PowerShell (Core) 7+, with pwsh.exe, the caller's working dir. is now inherited).
scriptPath = "C:\Users\mbaradihi\Desktop\AutoADTest\test.ps1"

' Create a dictionary of parameter name-value pairs.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "FName", "Anna"
dict.Add "middleIn", "E."
dict.Add "LName", "Roosevelt"
dict.Add "Branch", "White House"
dict.Add "Title", "First Lady"
dict.Add "employeeNum", "2"
dict.Add "company", "USA"
dict.Add "dept", "PCSW"
dict.Add "ad", "aeroosevelt@example.org"
dict.Add "city", "Washington"
dict.Add "state", "DC"
dict.Add "zip", "20500"
dict.Add "manager", "None"

' Synthesize the string that encodes all arguments.
args=""
for each key in dict.Keys
    args = args & " -" & key & " `\""" & dict(key) & "`\"""
next

' Synthesize the overall command line.
strCommand = "powershell.exe -command Start-Process -Verb RunAs powershell.exe \"" -ExecutionPolicy Unrestricted -NoExit -file `\""" & scriptPath & "`\""" & args & "\"""

' Execute it.
' Note: WshShell.Exec() executes asynchronously and provides no direct feedback.
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Exec(strCommand)

Answer 2

I would separate the elevation from your call. Start by getting the script to run as expected. This should work:

strCommand = "powershell.exe -ExecutionPolicy Unrestricted -NoExit -file ""C:\Users\mbaradihi\Desktop\AutoADTest\test.ps1"" -FName """ & FName & """ -middleIn """ & middleIn & """ -LName """ & LName & """ -Branch """ & Branch & """ -Title """ & Title & """ -employeeNum """ & employeeNum & """ -company """ & company & """ -dept """ & dept & """ -ad """ & ad & """ -city """ & city & """ -state """ & state & """ -zip """ & zip & """ -manager """ & manager & """"

If that works then all you need to do is perform the elevation inside the script itself. If it needs elevation, it should handle that.

I use this clause at the top of any PS script that needs elevation and it simply elevates itself:

# If not running as Administrator, escalate self
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
    $CommandLine = "-File `"" + $MyInvocation.MyCommand.Path + "`" " + $MyInvocation.UnboundArguments
    Start-Process -FilePath PowerShell.exe -Verb Runas -ArgumentList $CommandLine
    Exit
}