MS Access VBA – Open a File

It can be very useful at times to store the paths of files within a database such as word document, excel spreadsheets, etc. Most often it is then necessary to provide the user a method of opening these files without needing to personally navigating to each file themselves. The following line of code will open the given file in the default application associated with it.

Application.FollowHyperlink Method

Application.FollowHyperlink "FullPath&FileName"

Example:

Application.FollowHyperlink "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg"

You can use the Application.FollowHyperlink can also be used to open a folder (not just files)

This technique has always worked very well for me. That said, certain updates, have caused new security warning message to now appear rather than simply open the file. As such, you may wish to use Allen Browne’s GoHyperlink() function instead as it eliminates these messages and make for a more seamless approach.
 

Custom Procedure Method Employing the ShellExecute API

Another very good alternative is to use the ExecuteFile sub courtesy of Graham Seach (Access MVP). A nice feature is that not only can you open the file, but you can also choose to print the file and control the appearance of the windowstyle of the given application.

'Source: http://www.pacificdb.com.au/MVP/Code/ExeFile.htm
Public Const SW_HIDE = 0
Public Const SW_MINIMIZE = 6
Public Const SW_RESTORE = 9
Public Const SW_SHOW = 5
Public Const SW_SHOWMAXIMIZED = 3
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMINNOACTIVE = 7
Public Const SW_SHOWNA = 8
Public Const SW_SHOWNOACTIVATE = 4
Public Const SW_SHOWNORMAL = 1

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

Public Sub ExecuteFile(sFileName As String, sAction As String)
    Dim vReturn As Long
    'sAction can be either "Open" or "Print".
    
    If ShellExecute(Access.hWndAccessApp, sAction, sFileName, vbNullString, "", SW_SHOWNORMAL) < 33 Then
        DoCmd.Beep
        MsgBox "File not found."
    End If
End Sub

Example:
To open a file in the default associated program:

Call ExecuteFile("C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg", "Open")

To print a file:

Call ExecuteFile("C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg", "Print")

16 responses on “MS Access VBA – Open a File”

  1. Tom Boyce

    If i needed to open a specific file based an a staff id. Would this app follow hyperlink work for me? If i stored the full path & file name in the staff table, how could i code the command button to act on it? Many thanks

    1. admin Post author

      You should be able to modify the code to suit your needs.

      For instance, if you were on a form displaying a specific staff member’s id in a control named ‘Responsible’, then you could call the value as a variable in the FollowHyperlink method. Something along the lines of:

      Dim sBasePath As String
Dim sBaseFileName As String

sBasePath = "C:\...\...\"
sBaseFileName = "Staff_"

Application.FollowHyperlink sBasePath & sBaseFileName & Me.Responsible & ".xls"

      As you mentioned, you could also build a table and then pull the values from there using DLookup or a query of some sorts. But the sort answer is yes, the FollowHyperlink method can be used with variable filename.

  2. Tom Boyce

    Thanks for replying to me. Is the base path/file name the database address or the folder where my JPG’s are stored? How would it pick up the Staff ID Number to pass over to the hyperlink? Many Thanks

    1. admin Post author

      sBasePath is the full path to where you house the document(s) you wish to hyperlink

      As for picking up your Staff ID Number, this would be done by concatenating the value from the active form (as per the previous example), such as: Me.Responsible or Me.StaffId.

  4. Krishna Vasudevan

    Dear Dan
    Once again when searching for something I have found your website. And as in all previous occasions, I have learned something new, found something useful or you have saved me a ton of time. (Is time measured in tons? Would that be a massive clock? I digress.)
    The details do not matter. I am writing this as a general comment. Thank you so much for being what the military calls a force multiplier. Your work is awesome!

  5. Ali Grailly

    Do You Know How Can I Shift Tab In Ribbon To Right Side ?!!!

    Means Starting From Right Window Instead Of Left

    Thanks

  7. Mark

    Mate, this has saved me a tonne of time ( we also have large clocks ). Thanks so much for putting this together.

    MT

  8. papay

    hello,
    this command is greatly successful.
    thank you so much for giving us this command that i’ve never thought.
    i’m so happy. blessed are you!!

  9. Gino Latilla

    This works only in Excel..Access does not recognize Application. Stupid bastard.

  10. Wasser

    Hello friends of VBA Access
    I think the editor is a disaster. Who can help?
    Application.FollowHyperlink “C:\PathName works! No Problem !
    But not if a string variable is to be used, e.g.
    Application.FollowHyperlink “Z”
    Application.FileSearch , (“Z”) this was an other Attempt…
    Where Z is the PathName.
    I declared Z as a string variable as well as a hyperlink.
    Simply, I just want to be able to open any file outside of Access. Who can help? Thanks in advance The VBA editor is terrible.

    1. Daniel Pineault Post author

      I’m not sure I follow properly. It would have helped if you posted your actual code or use a Forum for help.

      If I do understand you are doing something like:

      Dim Z As String
Z = "C:\YourPath\YourFileName.Ext"
Application.FollowHyperlink "Z"

      No, that won’t work because you’ve put your variable in quotes making it a literal. Instead, try:

      Dim Z As String
Z = "C:\YourPath\YourFileName.Ext"
Application.FollowHyperlink Z

      That said, give your variables meaningful names!

      Dim sFile As String
sFile = "C:\YourPath\YourFileName.Ext"
Application.FollowHyperlink sFile

      If you are pulling the Path/File from a form, you can do:

      If IsNull(Me.ControlName) = False Then Application.FollowHyperlink Me.ControlName

      Also, you should prefix files being opened via FollowHyperlink with “file://”, you may also like to look over: https://www.devhut.net/avoiding-the-followhyperlink-security-warning/ for more information on the subject.

      Hopefully, that is what you were after.

  11. Jeremy

    Is there a way to open a file using a program that is not the default? For example, say I want to have a button toe open a .bat file with a text editor like UltraEdit. Is it possible to program a file to open with a specific application?

    1. Daniel Pineault Post author

      It depends.

      You can start looking into whether or not the program exposes command line switches, if so then you can use Shell to launch it.

      If not, you can get into Send Keys (which is unreliable), and/or advanced APIs, …