Feb 09 2024 05:17 AM - edited Feb 09 2024 05:28 AM
I have a command such as
(Get-StartApps | Where-Object name -eq 'Notepad').AppId
or just simply
Get-StartApps
which works just fine via PowerShell ISE.
I, on the other hand, am trying to run it via VBA in Excel/Access.... and do so by using WScript.Shell
CreateObject("WScript.Shell").Run "powershell -executionpolicy RemoteSigned -command ""Get-StartApps | Where-Object name -eq 'Notepad';Start-Sleep -Seconds 8;""", 1, True
where I then receive the error :
Get-StartApps : The term 'Get-StartApps' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Get-StartApps | Where-Object name -eq 'Notepad';Start-Sleep -Seconds ...
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Get-StartApps:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
I can simplify it to bare minimum
CreateObject("WScript.Shell").Run "powershell -executionpolicy RemoteSigned -command ""Get-StartApps;Start-Sleep -Seconds 8;""", 1, True
and it still returns
Get-StartApps : The term 'Get-StartApps' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:1
+ Get-StartApps;Start-Sleep -Seconds 8;
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Get-StartApps:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
(The Start-Sleep -Seconds 8; is only there for debugging purposes, the command errs without it being present just the same.)
I even thought maybe it was a 32-bit vs. 64-bit issue and tried explicitly specifying the PS exe to use
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe
but it made no difference, both failed the same.
Now I've used such an approach to do all sorts of other thing in PS via VBA without issue. This truly is specifically related to the Get-StartApps CmdLet. Is there something special about it that makes it not accessible? Anyone have any ideas? Maybe an alternate way to retrieve a programs AppId, that's what I'm ultimately after?!
Thank you for your help in advance.
Daniel
Feb 09 2024 05:46 AM
Feb 10 2024 09:01 AM
Feb 10 2024 09:48 AM - edited Feb 10 2024 09:48 AM
Get-StartApps is part of the StartLayout module; you could try to import-module StartLayout first.?
Something like this: (Added -noprofile for possible faster loading)
CreateObject("WScript.Shell").Run "powershell -executionpolicy RemoteSigned -noprofile -command ""Import-Module StartLayout ; Get-StartApps | Where-Object name -eq 'Notepad';Start-Sleep -Seconds 8;""", 1, True
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If one of the posts was helpful in other ways, please consider giving it a Like.
Feb 10 2024 01:28 PM
Feb 10 2024 01:39 PM
Feb 10 2024 04:11 PM
Feb 10 2024 04:14 PM
Feb 10 2024 09:25 PM
I have zero experience with Office automation - that's totally not my area, so forgive my ignorance on that front.
However, the only reason a module would not be found is if the search path environment variable does not contain a match, which in this scenario is odd since it's a module that ships with Windows itself.
I've had a strong aversion to anything "per user" for 24-odd years now (courtesy of wearing an application packaging hat many years ago) meaning all my modules are installed per machine, which leaves me unable to replicate this issue - not that I potentially could given this is a pre-canned module.
That leaves me unsure on what the issue could be. But, after availing myself on how to put something very basic (no pun intended) together in VBA, I'd be curious to see what the output for these three functions is in your environment:
Function GetPSVersion()
Set Process = CreateObject("WScript.Shell").Exec("powershell.exe -Command ""& { [PSCustomObject] $PSVersionTable | Write-Host; }""")
Do While Process.Status = 0
Application.Wait (Now() + TimeValue("0:00:01"))
Loop
GetPSVersion = Process.StdOut.ReadAll
End Function
Function GetModulePaths()
Set Process = CreateObject("WScript.Shell").Exec("powershell.exe -Command ""& { (Get-Module -ListAvailable -Name StartLayout).ModuleBase; }""")
Do While Process.Status = 0
Application.Wait (Now() + TimeValue("0:00:01"))
Loop
GetModulePaths = Process.StdOut.ReadAll
End Function
Function GetPath()
Set Process = CreateObject("WScript.Shell").Exec("powershell.exe -Command ""& { $env:Path.Split(\"";\"", [System.StringSplitOptions]::RemoveEmptyEntries); }""")
Do While Process.Status = 0
Application.Wait (Now() + TimeValue("0:00:01"))
Loop
GetPath = Process.StdOut.ReadAll
End Function
This is obviously from my own client.
Cheers,
Lain
Feb 11 2024 01:44 AM - edited Feb 11 2024 01:50 AM
This is what I get returned:
?GetPSVersion
@{PSVersion=5.1.19041.3930; PSEdition=Desktop; PSCompatibleVersions=System.Version[]; BuildVersion=10.0.19041.3930; CLRVersion=4.0.30319.42000; WSManStackVersion=3.0; PSRemotingProtocolVersion=2.3; SerializationVersion=1.1.0.1}
?GetModulePaths
?GetPath
C:\Program Files (x86)\Microsoft Office\Office15\
C:\Program Files (x86)\Common Files\Oracle\Java\javapath
C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\iCLS\
C:\Program Files\Intel\Intel(R) Management Engine Components\iCLS\
C:\Windows\system32
C:\Windows
C:\Windows\System32\Wbem
C:\Windows\System32\WindowsPowerShell\v1.0\
C:\Windows\System32\OpenSSH\
C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL
C:\Program Files\Intel\Intel(R) Management Engine Components\DAL
C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT
C:\Program Files\Intel\Intel(R) Management Engine Components\IPT
C:\Program Files (x86)\NVIDIA Corporation\PhysX\Common
C:\Program Files\Intel\WiFi\bin\
C:\Program Files\Common Files\Intel\WirelessCommon\
C:\Program Files\NVIDIA Corporation\NVIDIA NvDLISR
C:\WINDOWS\system32
C:\WINDOWS
C:\WINDOWS\System32\Wbem
C:\WINDOWS\System32\WindowsPowerShell\v1.0\
C:\WINDOWS\System32\OpenSSH\
C:\Users\Daniel\AppData\Local\Microsoft\WindowsApps
Since GetModulePaths() returned nothing via VBA, I ran the command in the ISE and get:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\StartLayout
Feb 11 2024 02:41 AM
Here's a fourth function to try and some Microsoft documentation you can use to try diagnosing your $env:PSModulePath value.
It seems like under Excel that perhaps the Windows system location (third option from the output below) may be missing, though I cannot begin to fathom why - assuming you're running both the ISE and Excel under the same user account.
But I guess the proof (or contradiction) of that assumption will be in the output from the fourth function.
Function GetPSPath()
Set Process = CreateObject("WScript.Shell").Exec("powershell.exe -Command ""& { $env:PSModulePath.Split(\"";\"", [System.StringSplitOptions]::RemoveEmptyEntries); }""")
Do While Process.Status = 0
Application.Wait (Now() + TimeValue("0:00:01"))
Loop
GetPSPath = Process.StdOut.ReadAll
End Function
I'm assuming that if you run a "Get-Module -ListAvailable -Name StartLayout" from a Windows PowerShell session, not from the ISE, that it does indeed find the StartLayout module?
Cheers,
Lain
Feb 11 2024 04:45 AM
Feb 11 2024 04:56 AM
Okay, so that's an interesting observation.
I never use the Windows PowerShell x86 console, but in checking it now, the StartLayout module is indeed not available, meaning it's only available as x64.
Are you running x32 Office rather than x64?
FWIW, I'm running x64.
Cheers,
Lain
Feb 11 2024 05:06 AM
Feb 11 2024 05:07 AM
Feb 11 2024 05:09 AM
A fifth (and final) function to quickly check if the instance of Windows PowerShell being launched by Excel is 32-bit (False) or 64-bit (True).
Function GetIs64Bit()
Set Process = CreateObject("WScript.Shell").Exec("powershell.exe -Command ""& { [System.Environment]::Is64BitProcess; }""")
Do While Process.Status = 0
Application.Wait (Now() + TimeValue("0:00:01"))
Loop
GetIs64Bit = Process.StdOut.ReadAll
End Function
Cheers,
Lain
Feb 11 2024 05:10 AM
"Furthermore, why wouldn't all CmdLets not be available in both 32 and 64 bitness."
Dunno - above my pay grade.
Cheers,
Lain
Feb 11 2024 05:13 AM
Solution
The only suggestion I have now is replacing your "powershell.exe" statement with a full path alternative of:
C:\Windows\SysNative\WindowsPowerShell\v1.0\powershell.exe
Cheers,
Lain
Feb 11 2024 05:19 AM - edited Feb 11 2024 05:24 AM
Just got that working.
c:\windows\sysnative\WindowsPowerShell\v1.0\powershell.exe
does launch PS x64.
But why????
C:\Windows\sysnative\WindowsPowerShell\v1.0\powershell.exe does not exist.
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe exists and tried that but it still launches PS x32
Where does SysNative come from???? I found it in a discussion, but why wouldn't the real path 'System32' work?
Why does MS do screwy things like this! If I give the path to the exe I want t you, why would MS override that!
Feb 11 2024 05:25 AM
Above my pay grade again (on the why), but here's some doco on it:
On the how, it's a process-level translation, not a physical construct.
Still, as long as you're working, you're good. And you've got a new trick to leverage should this kind of occurrence arise again.
Cheers,
Lain
Feb 11 2024 05:13 AM
Solution
The only suggestion I have now is replacing your "powershell.exe" statement with a full path alternative of:
C:\Windows\SysNative\WindowsPowerShell\v1.0\powershell.exe
Cheers,
Lain