How to execute a PowerShell function by passing parameters using VBA
<p>I have been trying to execute a PowerShell function (<code>countLines</code>) that takes an input parameter <code>fileName</code> from VBA. I have referred some answers from <a href="https://stackoverflow.com/questions/17034887/can-i-pass-a-vba-variable-into-a-called-powershell-script">this</a> and <a href="https://stackoverflow.com/questions/1405750/calling-a-specific-powershell-function-from-the-command-line">this</a> but they are not working for me. The following are my attempts to do the same (<code>fname</code> is a VBA variable that I am sending as a parameter to the VBA routine that contains the following line of codes):</p>

<p>The contents of <code>fileReader.ps1</code> are:</p>

<pre class="lang-bsh prettyprint-override"><code>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;
}
</code></pre>

<p>First Attempt:</p>

<pre class="lang-vb prettyprint-override"><code>Call Shell("powershell  -ExecutionPolicy Unrestricted ""D:\Temp\fileReader.ps1"";countLines -fileName """ &amp; fname &amp; """", vbMaximizedFocus))
</code></pre>

<p>Second Attempt:</p>

<pre class="lang-vb prettyprint-override"><code>Dim strCommand As Variant, WshShell As Variant, WshShellExec As Variant, output As Variant
strCommand = "powershell -command ""&amp; { . ""D:\Temp\fileReader.ps1"";countLines -logFile """ &amp; fname &amp; """ }"""
Set WshShell = CreateObject("WScript.Shell")
Set WshShellExec = WshShell.Run(strCommand)
output = WshShellExec.StdOut.ReadAll
</code></pre>

<p>Using this, I am getting an error: Type Mismatch.</p>

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

<pre class="lang-vb prettyprint-override"><code>Call Shell("powershell -ExecutionPolicy Bypass -file ""D:\Temp\fileReader.ps1""", vbMaximizedFocus)
</code></pre>

<p>and changing the above powershell script to:</p>

<pre class="lang-bsh prettyprint-override"><code>$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
</code></pre>

<p>This seems to be working.</p>

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