VBA – Opening Files and URLs

Have you seen my previous post?!

Before going any further, have you seen my previous post regarding opening a URL in the browser of your choice entitled VBA – Open a URL in FireFox/Chrome, check out the section entitled ‘A Procedure to Control Them All!‘

So what does a developer do when he can’t sleep? He explores what options can be used to open files and/or URLs, of course!

In this article I will attempt to explore multiple approaches that can be used to open Files and URLs using VBA, the choice of which one to use is left to you to decide! That said, I will expand on:

Access’ FollowHyperlink Method
FileProtocolHandler API
ShellExecute API
Shell Object
WScript Object
Shell Function

Access’ FollowHyperlink Method

In Access, the easiest technique to open a file or navigate to a URL is to simply use the Application.FollowHyperlink method.

In one simple line of code, as shown below, you can open any file or URL in the user’s default associated application.

'OpenURL1 "https://www.google.ca"
'OpenURL1 "C:\Users\Microsoft\Documents\Test.pdf" -> launches in system default app
'OpenURL1 "C:\Users\Microsoft\Documents\Image.jpg" -> launches in system default app
Function OpenURL1(ByVal sURL As String)
    On Error GoTo Error_Handler

    Application.FollowHyperlink sURL

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    If Err.Number <> 16388 Then    ' Cancelled by user
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: OpenURL1" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
    End If
    Resume Error_Handler_Exit
End Function

The only problem being that depending on Microsoft’s mood, registry settings, … some users will get the following warning which can be alarming to some (in newer versions of Office this is now standard behavior, but this wasn’t historically!):

Hyperlinks can be harmful to your computer and data. To protect your computer, click only those hyperlinks from trusted sources. Do you want to continue?

Or perhaps:

What’s maddening is the files with which FollowHyperlink displays such a message and not others. On my system it flags files such as .sql, .xml, … as risks! Seriously?!

Now, there are ways to deactivate such messages, but then you have to get into hacking the registry and not everyone has the knowledge or even the necessary permissions, … but should wish to explore this more in detail, feel free to look over:

Enable or disable hyperlink warning messages in Office programs - Microsoft 365

Describes how to enable or to disable hyperlink warning messages in Microsoft Office programs.

Disable “Hyperlinks can be harmful to your computer and data” Message in Office 365

Prevent a common warning from appearing when you open files via a hyperlink within Microsoft Office 365 files.

Since, IMHO, we shouldn’t need registry hacks to be able to simply open files or URLs from our programs, this lead me to start exploring what other options existed, and that is where the rest of this article originated from.

FileProtocolHandler API

Another option is to use the FileProtocolHandler API.

#If VBA7 Then
    Public Declare PtrSafe Function FileProtocolHandler Lib "url.dll" _
           Alias "FileProtocolHandlerA" ( _
           ByVal hwnd As LongPtr, _
           ByVal hinst As LongPtr, _
           ByVal lpszCmdLine As String, _
           ByVal nShowCmd As Long _
           ) As Long
#Else
    Public Declare Function FileProtocolHandler Lib "url.dll" _
           Alias "FileProtocolHandlerA" ( _
           ByVal hwnd As Long, _
           ByVal hinst As Long, _
           ByVal lpszCmdLine As String, _
           ByVal nShowCmd As Long _
           ) As Long
#End If


'OpenURL2 "https://www.google.ca"
'OpenURL2 "C:\Users\Microsoft\Documents\Test.pdf"
'OpenURL2 "C:\Users\Microsoft\Documents\Image.jpg"
Public Sub OpenURL2(ByVal sURL As String)
    On Error GoTo Error_Handler

    FileProtocolHandler 0, 0, sURL, 1

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: OpenURL2" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

ShellExecute API

Another option is to use the ShellExecute API. I’ve supplied all the constants according to the available documentation, but obviously you don’t need to carry all of them around if you don’t plan on ever using them. All that to say, the code can be somewhat simplified.

'https://docs.microsoft.com/en-us/windows/win32/api/shellapi/nf-shellapi-shellexecutea
#If VBA7 Then
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" ( _
        ByVal hwnd As LongPtr, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long _
        ) As LongPtr
#Else
    Public Declare Function ShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" ( _
        ByVal hwnd As Long, _
        ByVal lpOperation As String, _
        ByVal lpFile As String, _
        ByVal lpParameters As String, _
        ByVal lpDirectory As String, _
        ByVal nShowCmd As Long _
        ) As Long
#End If

'ShellExecute nShowCmd values
Public Const SW_HIDE = 0
Public Const SW_SHOWNORMAL = 1
Public Const SW_NORMAL = 1
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMAXIMIZED = 3
Public Const SW_MAXIMIZE = 3
Public Const SW_SHOWNOACTIVATE = 4
Public Const SW_SHOW = 5
Public Const SW_MINIMIZE = 6
Public Const SW_SHOWMINNOACTIVE = 7
Public Const SW_SHOWNA = 8
Public Const SW_RESTORE = 9
Public Const SW_SHOWDEFAULT = 10
Public Const SW_MAX = 10
'ShellExecute Return Codes
'0 'The operating system is out of memory or resources.
Private Const ERROR_FILE_NOT_FOUND = 2    'The specified file was not found.
Private Const ERROR_PATH_NOT_FOUND = 3    'The specified path was not found.
Private Const ERROR_BAD_FORMAT = 11    'The .exe file is invalid (non-Win32 .exe or error in .exe image).
Private Const SE_ERR_ACCESSDENIED = 5    'The operating system denied access to the specified file.
Private Const SE_ERR_ASSOCINCOMPLETE = 27    'The file name association is incomplete or invalid.
Private Const SE_ERR_DDEBUSY = 30    'The DDE transaction could not be completed because other DDE transactions were being processed.
Private Const SE_ERR_DDEFAIL = 29    'The DDE transaction failed.
Private Const SE_ERR_DDETIMEOUT = 28    'The DDE transaction could not be completed because the request timed out.
Private Const SE_ERR_DLLNOTFOUND = 32    'The specified DLL was not found.
Private Const SE_ERR_FNF = 2    'The specified file was not found.
Private Const SE_ERR_NOASSOC = 31    'There is no application associated with the given file name extension. This error will also be
                                     '  returned if you attempt to print a file that is not printable.
Private Const SE_ERR_OOM = 8    'There was not enough memory to complete the operation.
Private Const SE_ERR_PNF = 3    'The specified path was not found.
Private Const SE_ERR_SHARE = 26    'A sharing violation occurred.

'OpenURL3 "https://www.google.ca"
'OpenURL3 "C:\Users\Microsoft\Documents\Test.pdf"
'OpenURL3 "C:\Users\Microsoft\Documents\Image.jpg"
'***Reports of issues in which URL is passed as lowercase causing issues with certain case sensitive URLs***
Public Sub OpenURL3(ByVal sURL As String)
    #If VBA7 Then
        Dim lRetVal As LongPtr
    #Else
        Dim lRetVal As Long
    #End If

    'lpOperation can be: edit, explore, find, open, print, runas
    lRetVal = ShellExecute(0, "open", sURL, "", "", SW_SHOWNORMAL)
    Select Case lRetVal
        Case Is > SE_ERR_DLLNOTFOUND 'If the function succeeds, it returns a value greater than 32
            'Success
        Case ERROR_FILE_NOT_FOUND
            MsgBox "File not found"
        Case ERROR_PATH_NOT_FOUND
            MsgBox "Path not found"
        Case ERROR_BAD_FORMAT
            MsgBox "Bad format."
    End Select
End Sub

Be Careful!

Although ShellExecute is a great API to be aware of, there have been numerous reports of it not handling case sensitive URLs properly for some users, yet work fine for others.

Shell Object

Another option is to use the Shell Object to call the ShellExecute method.

'OpenURL4 "https://www.google.ca"
'OpenURL4 "C:\Users\Microsoft\Documents\Test.pdf"
'OpenURL4 "C:\Users\Microsoft\Documents\Image.jpg"
Public Sub OpenURL4(ByVal sURL As String)
    'https://docs.microsoft.com/en-us/windows/win32/shell/shell-shellexecute
    Dim oShell As Object

    Set oShell = CreateObject("Shell.Application")
    oShell.ShellExecute sURL, "", "", "open", 1
    If Not oShell Is Nothing Then Set oShell = Nothing
End Sub

Another Shell Object approach for opening files/urls is:

Public Function OpenURL5(ByVal sURL As String) As Boolean
    On Error GoTo Error_Handler
    Dim oShell                As Object
    Dim oFolder               As Object
    Dim oFile                 As Object
    Dim sFolderPath           As String
    Dim sFileName             As String

    Set oShell = CreateObject("Shell.Application")

    If Left(sURL, 4) = "www." Then sURL = "https://" & sURL
    If Left(sURL, 4) = "http" Or Left(sURL, 7) = "mailto:" Then
        ' Open URL
        oShell.NameSpace(0).ParseName(sURL).InvokeVerb "Open"
    Else
        ' Open file
        sFolderPath = Left(sURL, InStrRev(sURL, "\"))
        sFileName = Mid(sURL, InStrRev(sURL, "\") + 1)

        Set oFolder = oShell.NameSpace((sFolderPath))
        If Not oFolder Is Nothing Then
            Set oFile = oFolder.ParseName(sFileName)
            If Not oFile Is Nothing Then
                oFile.InvokeVerb "Open"
            Else
                Debug.Print "File not found: " & sURL
                GoTo Error_Handler_Exit
            End If
        Else
            Debug.Print "Folder not found: " & sFolderPath
            GoTo Error_Handler_Exit
        End If
    End If

    OpenURL5 = True

Error_Handler_Exit:
    On Error Resume Next
    Set oFile = Nothing
    Set oFolder = Nothing
    Set oShell = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: OpenURL5" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Yet another Shell Object approach is:

Public Sub OpenURL6(ByVal sURL As String)
    Dim oShell As Object
    
    ' Perhaps add a check that the file exists here first

    Set oShell = CreateObject("Shell.Application")
    oShell.Open (sURL)
    If Not oShell Is Nothing Then Set oShell = Nothing
End Sub

All three are similar to the ShellExecute API, but you don’t need to carry around the API declaration! These would appear to be the simplest solutions and none or them suffers from the warning messages that the FollowHyperlink method does.

WScript Object

Another possibility would be to use Wscript Shell’s Run command:

Sub OpenURL8(ByVal sURL As String)
    CreateObject("WScript.Shell").Run """" & sURL & """"
End Sub

Shell Function

We can also utilize the built-in VBA Shell function by doing:

Sub OpenURL7(ByVal sURL As String)
    Shell "cmd /c start """" """ & sURL & """", vbHide 'vbNormalFocus
End Sub

Speed Comparison

Now I did some very quick speed testing, certainly not a thorough test, but below were my results:

So, based on my quick testing the built-in Shell function was the fastest. As you can see, we see we seem to have 4 performance grouping:

the built-in Shell function
Shell.Application Execute, WScript.Shell Run, ShellExecute API, FollowHyperlink and the FileProtocolHandler API
Shell.Application Open and Shell.Application InvokeVerb
PowerShell

That said, I will see about doing more in depth testing in the future.

Some Final Thoughts

Which approach you use is ultimately up to you and may depends on a multitude of factors.

Previously, I privileged FollowHyperlink, but because over the years Microsoft has made an alarming security dialog appear whenever it is used I now avoid it completely.

Also, although they work just fine, I also avoid the API approaches (FileProtocolHandler, ShellExecute) just because I try and avoid API declarations when possible.

So, at the end of the day, I’d recommend using either: the built-in Shell function, Shell Object or the WScript Object. They all work and are simple!

Also, in the case of the Shell Object & WScript Object seriously consider implementing Self-Healing Object Variables (SHOV) to improve your application performance.

Other Resources

Page History

Date	Summary of Changes
2020-04-30	Initial Release
2025-04-30	Added 2 more Shell.Application approaches Added WScript section Added Shell Function section Made procedures both 32 and 64-bit compatible
2025-05-01	Added Speed Comparison section Added Some Final Thoughts section