Copy link to clipboard
Copied
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:
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
Copy link to clipboard
Copied
"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).
Copy link to clipboard
Copied
Thanks so much that is useful. I'll give it a try later this week.