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:
VBA code in Excel
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
Output
This is obviously from my own client.
Cheers,
Lain
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.
Fourth Excel 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
Output
Microsoft documentation on search locations
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
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).
Fifth function (for is64Bit)
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
Output
Cheers,
Lain
Accepted Solutions