How to execute a PowerShell function by passing parameters using VBA

Question

I have been trying to execute a PowerShell function (countLines) that takes an input parameter fileName from VBA. I have referred some answers from this and this but they are not working for me. The following are my attempts to do the same (fname is a VBA variable that I am sending as a parameter to the VBA routine that contains the following line of codes):

The contents of fileReader.ps1 are:

function countLines {
    param([string]$logFile);
      #The directory path may differ for others according to the machine's source code repository
    $scriptPath = Resolve-Path "..\..\testFolder";
    $varHolder = -join($scriptPath, "\logFileCount.txt");
    $linesb4Exec = Get-Content $logFile;
    $nLines = $linesb4Exec.Count;
    Clear-Content $varHolder
    $nLines | Out-File $varHolder
    #For second attempt, I am adding the following too
    #echo $nLines;
}

First Attempt:

Call Shell("powershell  -ExecutionPolicy Unrestricted ""D:\Temp\fileReader.ps1"";countLines -fileName """ & fname & """", vbMaximizedFocus))

Second Attempt:

Dim strCommand As Variant, WshShell As Variant, WshShellExec As Variant, output As Variant
strCommand = "powershell -command ""& { . ""D:\Temp\fileReader.ps1"";countLines -logFile """ & fname & """ }"""
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Run(strCommand)
output = WshShellExec.StdOut.ReadAll

Using this, I am getting an error: Type Mismatch.

Third Attempt (I tried hardcoding everything in my PowerShell file and removed the function and just running a block of code):

Call Shell("powershell -ExecutionPolicy Bypass -file ""D:\Temp\fileReader.ps1""", vbMaximizedFocus)

and changing the above powershell script to:

$logFile = "C:\somepath\Logs\Log.txt";
$varHolder = "D:\testing\testFolder\logFileCount.txt";
$linesb4Exec = Get-Content $logFile;
$nLines = $linesb4Exec.Count;
Clear-Content $varHolder
$nLines | Out-File $varHolder

This seems to be working.

I will be glad if someone could help me to call any PowerShell function with parameters using VBA.

Ansgar Wiechers · Accepted Answer · 2016-11-14T13:02:45.273

There are basically two mistakes in your attempts:

powershell "D:\Temp\fileReader.ps1";countLines -fileName ...
This cannot work, because the script file is executed in a child context while you're calling the function in the parent context. You need to dot-source the file to make its content available in the current context.
Set WshShellExec = WshShell.Run(strCommand)
Here you're confusing the Run and Exec methods. The former returns an integer (the exit code of the external command), the latter a WshScriptExec object (and only that object provides access to StdIn, StdOut and StdErr of the external process). You're getting a type mismatch because you try to assign the integer return value using the Set keyword, which must only be used when assigning objects to variables.

The commandline from your second attempt is the right approach if you want to stick with -Command and dot-sourcing the file. Use single quotes inside the PowerShell scriptblock to avoid quotefusion and execute the commandline with the Shell function:

cmd = "powershell -ExecutionPolicy Bypass -Command ""&{. 'D:\Temp\fileReader.ps1';countLines -logFile '" & fname & "'}"""
Shell cmd

With that said, if the function is the only code in your script it'd be simpler to parameterize the script and run the code directly from it:

[CmdletBinding()]
Param(
  [string]$LogFile
)

$scriptPath = Resolve-Path "..\..\testFolder"
$varHolder  = Join-Path $scriptPath "logFileCount.txt"
@(Get-Content $logFile).Count | Out-File $varHolder

Run the script using the parameter -File like this:

cmd = "powershell -ExecutionPolicy Bypass -File ""D:\Temp\fileReader.ps1"" -LogFile """ & fname & """"
Shell cmd

Beware that the path ..\..\testFolder is relative to your current working directory, not to the location of the script. For the latter use $MyInvocation.MyCommand.Path.

If something doesn't work add the parameter -NoExit to the commandline (so the PowerShell window isn't closed automatically), and call the Shell function with the option vbNormalFocus to run the process in the foreground.

cmd = "powershell -ExecutionPolicy Bypass -NoExit -File ""D:\Temp\fileReader.ps1"" -LogFile """ & fname & """"
Shell cmd, vbNormalFocus

Oops...Sorry, my bad...I'll edit it soon... Thanks for pointing out — Rishi, Nov 14 '16 at 12:19
That worked! :) And you implicitly sorted out one of my doubts that I forgot to ask as for how could I stop PowerShell window to close automatically. I wasn't able to identity the problem that the path was not getting resolved s you rightly pointed it out... Bravo for such a well descriptive answer! I need to do a lot of reading... — Rishi, Nov 14 '16 at 12:49
It will be great if you can specify as how can I correctly capture the return value of a PowerShell function in my VBA. As you have already pointed out, the return value using the above actually fetches me the exit code, so that's not working for me. — Rishi, Nov 14 '16 at 13:16

How to execute a PowerShell function by passing parameters using VBA

1 Answers1