This is another post in my recent series of posts regarding using PowerShell to empower any VBA application. Today, I thought I’d look at how PowerShell could offer a solution to launching files. No surprise here, PowerShell easily rose to the occasion!

PowerShell CmdLets

I happily started out developing a function using the Invoke-Item CmdLet, which worked beautifully. Then I had the idea of replacing Access’ Application.FollowHyperlink functionality, so be able to open both files and URLs, and Invoke-Item could deal with URLs.

A little more digging and testing and I was back in business using the Start-Process CmdLet instead and the following procedure was born!

'---------------------------------------------------------------------------------------
' Procedure : PS_OpenItem
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Open a file or URL in the default application
'               Avoids the security warnings/prompts you can see with other techniques
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Requires a copy of the PS_Execute() function
' References: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/start-process?view=powershell-7.1
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sItem     : Item to open, either:
'               Fully qualified path and filename
'               URL
'
' Usage:
' ~~~~~~
' PS_OpenItem("C:\Users\Dev\Documents\CRM.accdb") -> Opens the database
' PS_OpenItem("https:\\www.devhut.net")           -> Opens the website
' PS_OpenItem("www.devhut.net")                   -> Opens the website
' PS_OpenItem("mailto://someone@somewhere.com")   -> Starts an E-mail
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-15              Initial Release
'---------------------------------------------------------------------------------------
Function PS_OpenItem(sItem As String) As Variant
On Error GoTo Error_Handler
    Dim sCmd                  As String

    'Build the necessary PowerShell Command
    sCmd = "Start-Process -FilePath '" & sItem & "'"
    'Execute the command
    Call PS_Execute(sCmd)
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_OpenItem" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

The beauty here is that:

• Unlike Access’ Application.FollowHyperlink method, this procedure will work in any VBA environment
• You don’t receive any security warnings
• Doesn’t require any APIs, so no messing with 32 vs 64-bit declarations…

Do note that the above function requires a copy of my PS_Execute function which can be found in my post VBA – Run PowerShell Command.

Alternative Approaches

If you are not sold on using this VBA/PowerShell implementation, feel free to look at one of the various other approaches I elaborated on in my article VBA – Opening Files and URLs.

A Few Resources on the Subject

• VBA – Run PowerShell Command
• Start-Process (Microsoft.PowerShell.Management) – PowerShell | Microsoft Docs
• Invoke-Item (Microsoft.PowerShell.Management) – PowerShell | Microsoft Docs
• Application.FollowHyperlink method (Access) | Microsoft Docs