Passing argument from Excel VBA module to PoSh script
Gordon Pegue
Greetings –
Having some difficulties figuring out how to get my Excel VBA module to pass an arugument out to a powershell script.
Looking for a second pair of eyes that might spot what the heck I’m missing to get it to work.
Here’s salient code from each:
**** First, the VBA
Sub GetFolderStats()
' Define the PowerShell script path:
Dim scriptPath As String
scriptPath = "C:\Temp\Get-FolderStatsVBA.ps1"
' Declare the inputValue type:
Dim inputValue As String
' Get the reference to the active sheet:
Dim currentSheet As Worksheet
Set currentSheet = ActiveSheet
' Declare the Row and Column value types:
Dim rowNumber As Long
Dim columnNumber As Long
' Get the selected cell:
Dim selectedCell As Range
Set selectedCell = Selection
' Check if a single cell is selected:
If selectedCell.Cells.Count = 1 Then
' Get the value of the selected cell:
inputValue = selectedCell.Value
' Get the row and column values of the selected cell:
rowNumber = selectedCell.Row
columnNumber = selectedCell.Column
' Create a new instance of the Shell object:
Dim wShell As Object
Set wShell = CreateObject("WScript.Shell")
' Create full path to PoSh executable:
Dim psExe As String
psExe = wShell.ExpandEnvironmentStrings("%WINDIR%") & "\System32\WindowsPowerShell\v1.0\powershell.exe"
' Run the PowerShell script and capture the output:
Dim scptout As Object
Set scptout = wShell.Run(psExe & " -ExecutionPolicy Bypass -NoExit -File """ & scriptPath & """ -inputValue """ & inputValue & """", 1, True)
Dim output As String
output = scptout.StdOut.ReadAll
… more code
**** Now, the PoSh…
[CmdletBinding()]
Param (
[Parameter(Mandatory=$true)]
[String]$inputVal
)
$folderPath = [IO.File]::ReadAllText($inputVal)
… more code
****
When I step into the Set scptout line (highlighted in yellow above) in the VBA gui, a PoSh window is opened (as expected) but the following error is displayed:
I’ve tried different ways of assembling the string that the wShell.Run executes but cannot seem to figure out why the inputValue (a string that is a UNC path to a remote resource) is not getting passed to the PoSh script…
In the VBA gui, the Locals window shows that the VBA is getting the inputValue properly from the spreadsheet:
As always, thanks in advance!
Gordon
Michael B. Smith
If your PS excerpt is accurate, the problem is because “-inputVal” -ne “-inputValue”.
Thanks.
Regards,
Michael B. Smith
Managing Consultant
Smith Consulting, LLC
--
You received this message because you are subscribed to the Google Groups "ntpowershell" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
ntpowershell...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/ntpowershell/CH3PR07MB98184EB708504421D5A3472DD35DA%40CH3PR07MB9818.namprd07.prod.outlook.com.
Gordon Pegue
Crud… <smacking forehead with palm>
FWIW it didn’t know that the variable name in the VBA module had to match the variable name in the PoSh parameter section.
Thanks for spotting that Michael.
Now I’m trying to figure out how to get the JSON output from the PoSh back into the VBA.
Stay tuned
From: ntpowe...@googlegroups.com <ntpowe...@googlegroups.com>
On Behalf Of Michael B. Smith
Sent: Wednesday, June 21, 2023 09:23
To: ntpowe...@googlegroups.com
Subject: [ntpowershell] RE: Passing argument from Excel VBA module to PoSh script
[EXTERNAL]
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/2faedfb4fbbe446089105c7a08f44aca%40smithcons.com.
Michael B. Smith
A known temporary filename on disk may be easiest.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CH3PR07MB9818C6D2989570152675C3C5D35DA%40CH3PR07MB9818.namprd07.prod.outlook.com.
Gordon Pegue
After mucking with it, I’m beginning to think that may be the best approach Michael.
Seems to be all kinds of little gotchas associated with parsing the JSON, once it’s been returned to the VBA.
Gordon
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/91aca635bfba475ab4b6300e6a22a054%40smithcons.com.
Michael B. Smith
I don’t know your application, but json is messy. Just slightly less messy than XML.
You might try (if it works for you) returning the data basically as a hashtable – a list of name/value pairs. That’s what I tend to do.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CH3PR07MB981869570FA07B19770D17F9D35DA%40CH3PR07MB9818.namprd07.prod.outlook.com.
Gordon Pegue
I thought that was how a simple JSON output worked….
So my PoSh script needs to return 3 numeric values, one floating point and two integers, to the VBA module.
Those values then get written to the spreadsheet in columns to the right of where the inputValue path cell is.
Got a quick learning example using a hashtable approach?
Something like this fragment to conclude my PoSh?
$values = @{
'value1' = $totalSize
'value2' = $itemCount
'value3' = $subfolderCount
}
$values
}
If this is the proper way to build the hashtable, how do I get that properly bound to a variable in VBA so I can extract the values?
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/e04ab12dde2642a5aaa6799535779918%40smithcons.com.
Michael B. Smith
Basic JSON is going to look like this:
[{
"c": 2.4,
"b": 1.23,
"a": 1
}]
Where outputting a hash table will be:
A 1
B 1.23
C 2.4
You’ll use the Split() function to split the input string into multiple values. CInt() to convert string to integer. CDec() to convert string to decimal/real/float.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CH3PR07MB98185ABEA7E4228A0FF9715DD35DA%40CH3PR07MB9818.namprd07.prod.outlook.com.
Michael B. Smith
Oh I forgot the second part of your question:
$values = @{
'value1' = $totalSize
'value2' = $itemCount
'value3' = $subfolderCount
}
$values
Yes, you can do this, or you can just
Write-Output “Value1 $totalSize”
Write-Output “Value2 $itemCount”
Etc.
Just depends on how you handle data in your script.
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/CH3PR07MB98185ABEA7E4228A0FF9715DD35DA%40CH3PR07MB9818.namprd07.prod.outlook.com.
Gordon Pegue
Here’s what worked in the end (now to cleanup all the junk, commented out debug lines in both scripts):
In the PoSh:
$values = "$totalSize,$itemCount,$subfolderCount"
$values
}
Concludes the PoSh and returns a single string with my values separated by commas
In the VBA:
Set scptout = wShell.Exec(psExe & " -ExecutionPolicy Unrestricted -File """ & scriptPath & """ -inputValue """ & inputValue & """")
Dim values As Variant
values = scptout.StdOut.ReadAll
MyArray = Split(values, ",")
value1 = MyArray(0)
value1 = CDbl(value1)
value2 = MyArray(1)
value2 = CInt(value2)
value3 = MyArray(2)
value3 = CInt(value3)
Balance of vba code that updates the spreadsheet cells.
Note how I ended up using the Exec method instead of the Run method. Found a hit that said if you’re returning a value back to VBA, Exec works better…
Once again, thanks for “tipping” me in the right direction Michael!
To view this discussion on the web visit https://groups.google.com/d/msgid/ntpowershell/3643103d6f544eab823977df58d430de%40smithcons.com.
Michael B. Smith
Sent: Wednesday, June 21, 2023 3:49:48 PM
To: ntpowe...@googlegroups.com <ntpowe...@googlegroups.com>