A little off-topic but maybe on-target

[stanl]

Those of us who have been around WB for 20+ years who that (1) it integrates well with VBA, Wsrript, Python, PHP (2) introduced an ODBC Extender, later worked with ADO for database work (3) incorporated CLR to work with .NET.

Being said, below is a script for further connecting WB with Powershell. It is already connected as you can run PS code from WB via the CLR. Having just been termed and at my age probably won't be asked to work for the man anymore. I am developing scripts for friends who have upgraded to 64-bit SQL Server Express, and most of my 32-bit ADO scripts no longer work, don't matter as PS can work with SQL Server with 1 or two lines of code. I tested creating a SQL Server database and creating tables from Access, csv, sqlite with a few lines of code. A great benefit to PS is the powershell gallery and the PowerShellGet module that enables installing, updating, finding Powershell modules and scripts from the Microsoft Powershell Gallery. If a user has it installed WB can handle them via PS code executed through CLR.  Below is a very simple script to execute PS code and determine if PowershellGet exists.

Code (WINBATCH) Select Expand



;Winbatch 2022C - Check for existance of PowerShellGet module
;This should work with versions after 2013                 
;Generaates a simple pop-up that Module Exists/Does Not Exist
;Stan Littlefield, January 27,2024
;/////////////////////////////////////////////////////////////////////////////////////////////////////////


If Version( )< '2013A'
   Pause('Notice', 'Need 2013A or Newer Version of WinBatch')
   Exit
EndIf
cScript = $"$a = New-Object -ComObject wscript.shell
$module = gmo -l PowerShellGet -ErrorAction SilentlyContinue
if ($module -ne $null)
{ $b = $a.popup("Module PowershellGet Exists",2,"SUCCESS",4096) }
else
{ Write-Host $b = $a.popup("Module PowershellGet Does Not Exist",2,"OOPS",4096) }
Exit$"
ObjectClrOption("useany", "System.Management.Automation")
objAutoPs = ObjectClrNew("System.Management.Automation.PowerShell")
oPshell = objAutoPs.Create()
oScope = ObjectType("BOOL",@TRUE)
oPshell.AddScript(cScript,oScope)
objAsync = oPshell.BeginInvoke()
oPShell.EndInvoke(objAsync)     
oPshell.Dispose()
oPshell=0
Exit


[td]

Unfortunately, age bias is very real in the tech world.

[stanl]

change { Write-Host $b = $a.popup("Module PowershellGet Does Not Exist",2,"OOPS",4096) }
to { $b = $a.popup("Module PowershellGet Does Not Exist",2,"OOPS",4096) }

and if you have PowerShellGet. you can try

Code (WINBATCH) Select Expand



;Winbatch 2022C - Check for existance of PowerShellGet module
;This should work with versions after 2013                 
;Generaates a simple pop-up that Module Exists/Does Not Exist
;Stan Littlefield, January 27,2024
;/////////////////////////////////////////////////////////////////////////////////////////////////////////


If Version( )< "2013A"
   Pause("Notice", "Need 2013A or Newer Version of WinBatch")
   Exit
EndIf
cScript = $"$a = New-Object -ComObject wscript.shell
$b = $a.popup("Looking for Modules that refer to Database Please Wait",2,"Searching",4096)
Find-Module -Tag Database -Repository PSGallery | Out-Gridview -Title "Modules in Powershell Gallery" -Passthru$"
cScript = cScript:@CRLF:"[System.Runtime.Interopservices.Marshal]::ReleaseComObject($a)"
ObjectClrOption("useany", "System.Management.Automation")
objAutoPs = ObjectClrNew("System.Management.Automation.PowerShell")
oPshell = objAutoPs.Create()
oScope = ObjectType("BOOL",@TRUE)
oPshell.AddScript(cScript,oScope)
objAsync = oPshell.BeginInvoke()
oPShell.EndInvoke(objAsync)     
oPshell.Dispose()
oPshell=0
Exit
]

NOTE: had to add last line separate as :: are considered special characters not allowed in $" .... $" text block.

As for the age thing. Even Walmart has eliminated greeters.

[td]

$"::::$" should work to place back-to-back colons in a multi line string.

Code (winbatch) Select Expand

ml = $"::::$"
Message("Back-to-Back", ml)
exit

[stanl]

$"::::$" should work to place back-to-back colons in a multi line string.

Code (winbatch) Select Expand

ml = $"::::$"
Message("Back-to-Back", ml)
exit

Remember trying that, it appeared to fail [probably something dumb I did], and I plan-B'd it. I've tested some interesting PS commands for cScript - i.e. to indicate modules installed/available, to find modules based on a -Tag in PS Gallery, but I'm brain-dead using WB to execute an Install-Module.  Appears to be a no-go with the CLR [pulls up PS console as ADMINISTRATOR: then just dies], so pushed the command to a batch file, i.e. c:\temp\test.bat which contains as an example

Code Select Expand


start Powershell.exe -Command Install-Module -Name Write-ObjectToSQL -Scope CurrentUser



which works perfect if I double click on the .bat file - but trying WB's Run(), ShellExecute(), CreateObject("WScript.Shell") to run the .bat from WB all fail. [3246 error: Object does not exist or period used instead of comma]


for fun: the cScript code

Code (WINBATCH) Select Expand



cScript = $"$a = New-Object -ComObject wscript.shell
$b = $a.popup("Installing Module Write-ObjectToSQL",3,"Working",4096)


Install-Module -Name Write-ObjectToSQL -Scope CurrentUser
if ((Get-Module -Name Write-ObjectToSQL -listAvailable) -eq $Null)
{
   $b = $a.popup("Module Not Installed",3,"Error",4096)
}
else
{
   $b = $a.popup("Installed, Module Contains",3,"Working",4096)
   Get-Command -Module Write-ObjectToSQL | Out-Gridview -Title "Commands in Write-ObjectToSQL" -Passthru
}$"


[td]

This worked for me:

Code (winbatch) Select Expand

Run("Powershell.exe",  "-Command Install-Module -Name Write-ObjectToSQL -Scope CurrentUser")


and this

Code (winbatch) Select Expand

Run("cmd.exe",  "/C start Powershell.exe -Command Install-Module -Name Write-ObjectToSQL -Scope CurrentUser")


[stanl]

I tried both of your suggestions in both 32/64 WB. Also tried /K instead of /C as a start option. The snippet [below] ran without issue for both , but the module is never installed. Basically, the blue window for PS appear, a prompt flashes for 2-3 seconds then it ends. I then tested with  -ExecutionPolicy ByPass or -ExecutionPolicy Unrestricted to the command line with same results. Finally added full path C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe - same results. But double-clicking on test.bat [with the command text] will install the module.

Code (WINBATCH) Select Expand



;Run("cmd.exe",  "/C start Powershell.exe -Command Install-Module -Name Write-ObjectToSQL -Scope CurrentUser")
Run("Powershell.exe",  "-Command Install-Module -Name Write-ObjectToSQL -Scope CurrentUser")
Message("Installed","Write-ObjectToSQL")
Exit


[stanl]

Strange, but this worked. PowerShellGet also has a Save-Module function. The docs say it doesn't perform an install, but I ran the WB command below [username should be replaced by actual username] and it installed it. I originally tested by indicating c:\temp as the path, but just changed it to where installs are normally placed and whaddyaknow ;D .  I have multiple versons of PowerShellGet installed [something PS permits] so I'm assuming Install-Module may be a little quirky on my PC. Anyway this worked:

Code (WINBATCH) Select Expand



Run("C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe",  "-Command Save-Module -Name Write-ObjectToSQL -Path C:\Users\[username]\OneDrive\Documents\WindowsPowerShell\Modules -Repository PSGallery")



based on $env:PSModulePath PS looks for modules in

Code Select Expand



C:\Users\[username]\OneDrive\Documents\WindowsPowerShell\Modules;C:\Program Files\WindowsPowerShell\Modules;C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules;C:\Program Files (x86)\Microsoft S
QL Server\150\Tools\PowerShell\Modules\



which has the OneDrive added, not contained in system env for PSModulePath. That is associated with -Scope CurrentUser so newer modules or script from PS Gallery are installed there.


Long Story short... No need to use  System.Management.Automation.PowerShell for installs.