Checking if PDF files are open via VBA

[Fiona25546758ojap]

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”
Else
Set oShell = CreateObject(“WScript.Shell”)
oShell.Run strFile
End If

CleanUp:
Set oFSO = Nothing
Set oShell = Nothing

Exit Sub
ErrorHandler:
MsgBox “An unexpected error has occurred ” & Err.Number & ” ” & Err.Description, vbOKOnly + vbInformation
Err.Clear
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

[Test Screen Name]

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

No, it will be valid. It just won't be what you seem to expect, which is that it tells you whether the document is visible in a window. But that's not what it checks. IsFileOpen checks the system status - it's mainly used to see if it would be possible to overwrite or delete a file. Some apps will open a PDF and keep it open as long as it is visible - Acrobat is like that. Other apps will read a PDF entirely into memory, then close it - in this case IsFileOpen will return true briefly, then return to false.

If you want to know which app would open a given filename on double click, the useful API is FindExecutable. But never use Shell to open with the default app! If you want to open the file, just use ShellExecute on the PDF filename. This is important because opening the file might not actually want to run an executable; if an app is already running it might instead send system messages (DDE).

[Fiona25546758ojap]

Thanks so much that is useful. I'll give it a try later this week.