Erlandsen Data Consulting

How to open a file from VBA

2004-05-13 File access 0 206

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 = "PDF Files,*.pdf,All Files,*.*" ' file type options
    ' get pdf document name
    strDocument = Application.GetOpenFilename(strDocument, 1, "Open File", , False) 
    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, " ")
    ' get a temporary file name
    GetTempFileName CurDir, vbNullString, 0&, strFileName 
    strFileName = Trim(strFileName)
    ' add the given file type
    strFileName = Left$(strFileName, Len(strFileName) - 3) & strFileType 
    f = FreeFile
    Open strFileName For Output As #f ' create the temporary file
    Close #f
    ' look for an associated executable
    r = FindExecutable(strFileName, vbNullString, strExecutable)
    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 = "PDF Files,*.pdf,All Files,*.*" ' file type options
    ' get pdf document name
    strDocument = Application.GetOpenFilename(strDocument, 1, "Open File", , False) 
    If Len(strDocument) < 6 Then Exit Sub
    strExecutable = GetExecutablePath("pdf") ' get the path to Acrobat Reader
    If Len(strExecutable) > 0 Then
        ' open pdf document
        Shell strExecutable & " " & strDocument, vbMaximizedFocus 
    End If
End Sub