• Opening & Closing PDF files from Excel VBA

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Opening & Closing PDF files from Excel VBA

    • This topic has 1 reply, 1 voice, and was last updated 5 months ago.

    Hi there,

    To open a PDF file from Excel VBA, I have used the code at the bottom of this message.

    The IsFileOpen routine will not be valid if the PDF is open in the user’s default browser.

    The Shell will open the PDF in the default application for opening a PDF, whether that be Adobe Reader, Adobe Acrobat or if neither of those installed the default browser, which should be Microsoft Edge.

    I would like to know how to do the following:

    1. Work out which application will be used to open the PDF by the Shell command (I imagine through API calls.)
    2. I need code for checking if the file is open
      1. In Adobe Reader (if installed)
      2. In Adobe Acrobat (if installed)
      3. In Microsoft Edge
      4. In Chrome
      5. In Internet Explorer

    I would be so grateful if someone could provide code stubs for such.

    Public Sub OpenPDF()

    On Error GoTo ErrorHandler

    Dim Ret As Boolean
    Dim strFile As String
    Dim oFSO As New FileSystemObject
    Dim oShell As Object

    strFile = Resources.PathToPDFExpenseHelp

    Ret = oFSO.FileExists(strFile)
    If Not Ret Then
    MsgBox “The file ” & strFile & ” does not exist.”, vbOKOnly + vbInformation, “Expense Help”
    GoTo CleanUp
    End If

    Ret = IsFileOpen(strFile)

    If Ret Then
    MsgBox “The file ” & strFile & ” is open.”, vbOKOnly + vbInformation, “Expense Help”
    Set oShell = CreateObject(“WScript.Shell”)
    oShell.Run strFile
    End If

    Set oFSO = Nothing
    Set oShell = Nothing

    Exit Sub
    MsgBox “An unexpected error has occurred ” & Err.Number & ” ” & Err.Description, vbOKOnly + vbInformation
    GoTo CleanUp

    End Sub

    Public Function IsFileOpen(FileName As String) As Boolean

    Dim ff As Long, ErrNo As Long
    IsFileOpen = False

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0: IsFileOpen = False
    Case 70: IsFileOpen = True
    Case Else: Error ErrNo
    End Select

    End Function

    Viewing 0 reply threads
    Viewing 0 reply threads
    Reply To: Opening & Closing PDF files from Excel VBA

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: