4 VBA PowerShell Functions
Microsoft Access MVP Daniel Pineault provides four easy-to-use routines for integrating PowerShell with VBA.
PowerShell is Microsoft's powerful scripting language that provides easy access to the full .NET framework.
It's indispensable for automating certain Microsoft 365 tasks, in particular anything having to do with Microsoft Exchange Online. Unfortunately, there is nothing built in to the standard VBA library to interact with PowerShell. Luckily for us, Microsoft Access MVP Daniel Pineault has written a series of functions to make working with PowerShell from VBA much easier.
Here's a brief description of each function:
PS_Execute(ByVal sPSCmd As String): runs a PowerShell command. Use this when you don't care about the return value.PS_GetOutput(ByVal sPSCmd As String) As String: runs a PowerShell command and returns the output as a string. Use this when you need the return value.PS_Admin_Execute(ByVal sPSCmd As String): runs a PowerShell command with elevated rights. Use this for tasks that require admin privileges. You will receive the typical UAC prompt.PS_Admin_GetOutput(ByVal sPSCmd As String): runs a PowerShell command with elevated rights and returns the output as a string. Use this for admin tasks where you need the return value.
One of the nice things about these functions is that they use late binding, so they require no extra references.
External references
VBA - Run PowerShell Command | DEVelopers HUT
A couple examples of how you can execute a PowerShell command through VBA a retrieve the response.
About Daniel Pineault | DEVelopers HUT
A little bit about Daniel Pineault.
