How to open a file from VBA

If the file type you want to open is supported by your default web browser, you can open the file like this:

Sub BrowsePDFDocument() ' opens a PDF document in the default web browser
Dim strDocument As String
    strDocument = Application.GetOpenFilename("PDF Files,*.pdf,All Files,*.*", 1, "Open File", , False) ' get pdf document name
    If Len(strDocument) < 6 Then Exit Sub
    ActiveWorkbook.FollowHyperlink strDocument
End Sub

Files can also be opened using the Shell-command like this:

Shell "notepad.exe c:\foldername\filename.txt", vbMaximizedFocus ' open a txt document

You will have to know the name of the executable/application that can open your file.
If the executable is not included in your computers command path you will have to use the full file name like this:

Shell "c:\foldername\notepad.exe c:\foldername\filename.txt", vbMaximizedFocus ' open a txt document

Often the name of the executable can be different depending on the installed version of the software.
The procedures below can help you find the proper executable file name and then open a document in its associated application:

Declare Function GetTempFileName Lib "kernel32" _
    Alias "GetTempFileNameA" (ByVal lpszPath As String, _
    ByVal lpPrefixString As String, ByVal wUnique As Long, _
    ByVal lpTempFileName As String) As Long

Declare Function FindExecutable Lib "shell32.dll" _
    Alias "FindExecutableA" (ByVal lpFile As String, _
    ByVal lpDirectory As String, ByVal lpResult As String) As Long

Function GetExecutablePath(strFileType As String) As String
' returns the full path to the executable associated with the given file type
Dim strFileName As String, f As Integer, strExecutable As String, r As Long
    If Len(strFileType) = 0 Then Exit Function ' no file type
    strFileName = String$(255, " ")
    strExecutable = String$(255, " ")
    GetTempFileName CurDir, "", 0&, strFileName ' get a temporary file name
    strFileName = Application.Trim(strFileName)
    strFileName = Left$(strFileName, Len(strFileName) - 3) & strFileType ' add the given file type
    f = FreeFile
    Open strFileName For Output As #f ' create the temporary file
    Close #f
    r = FindExecutable(strFileName, vbNullString, strExecutable) ' look for an associated executable
    Kill strFileName ' remove the temporary file
    If r > 32 Then ' associated executable found
        strExecutable = Left$(strExecutable, InStr(strExecutable, Chr(0)) - 1)
    Else ' no associated executable found
        strExecutable = vbNullString
    End If
    GetExecutablePath = strExecutable
End Function

Sub OpenPDFDocument()
Dim strDocument As String, strExecutable As String
    strDocument = Application.GetOpenFilename("PDF Files,*.pdf,All Files,*.*", 1, "Open File", , False) ' get pdf document name
    If Len(strDocument) < 6 Then Exit Sub
    strExecutable = GetExecutablePath("pdf") ' get the path to Acrobat Reader
    If Len(strExecutable) > 0 Then
        Shell strExecutable & " " & strDocument, vbMaximizedFocus ' open pdf document
    End If
End Sub

Document last updated 2004-05-13 20:19:25

User comments:

Alan from UK wrote (2006-06-22 17:18:35 CET):

Further to below
Just copy the whole lot below into a new module and call the function
as below (copy, repalce and take out comments as you want)

Private Sub MyButton_click () '- Module for the form your working on
'- with your button/control name
Dim MyFile As String '- fed to the function OpenPdfDocument
MyFile = me.******* '-***** is your forms text box name
'-with the
'-directory and file i.e.
' c:/filename.pdf

OpenPDFDocument(MyFile)
End sub

read the comment when installed in module for further info
Hope this helps

Alan from UK wrote (2006-06-22 17:01:11 CET):

For those looking for a version for MS access
Function GetExecutablePath(strFileType As String) As String
' returns the full path to the executable associated with the given file type
Dim strFileName As String, f As Integer, strExecutable As String, r As Long
If Len(strFileType) = 0 Then Exit Function ' no file type
strFileName = String$(255, " ")
strExecutable = String$(255, " ")
GetTempFileName CurDir, "", 0&, strFileName ' get a temporary file name
strFileName = Trim(strFileName)
strFileName = Left$(strFileName, Len(strFileName) - 3) & strFileType ' add the given file type
f = FreeFile
Open strFileName For Output As #f ' create the temporary file
Close #f
r = FindExecutable(strFileName, vbNullString, strExecutable) ' look for an associated executable
Kill strFileName ' remove the temporary file
If r > 32 Then ' associated executable found
strExecutable = Left$(strExecutable, InStr(strExecutable, Chr(0)) - 1)
Else ' no associated executable found
strExecutable = vbNullString
End If
GetExecutablePath = strExecutable
End Function
Function OpenPDFDocument(MyFile)
Dim strDocument As String, strExecutable As String
strDocument = (MyFile)
If Len(strDocument) < 6 Then Exit Function
strExecutable = GetExecutablePath(".pdf") ' get the path to Acrobat Reader (or other file just change to .xls, .doc etc)
If Len(strExecutable) > 0 Then
Shell strExecutable & " " & strDocument, vbMaximizedFocus ' open pdf document
End If
End Function

Mike from The Great White North... wrote (2005-08-06 05:41:05 CET):

Thank you..!!!!
I was pulling my hair out trying to figure out a way to find the path to an executable with VBA - and I googled to your page - and your solution works perfectly..!!

Thank you so much...!!

Mike
butterfm@hotmail.com