- Joined
- Oct 24, 2022
- Messages
- 1
- Reaction score
- 0
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 a 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:
<ol>
<li>Work out which application will be used to open the PDF by the Shell command (I imagine through API calls.)</li>
<li>I need code for checking if the file is open
<ol>
<li>In Adobe Reader (if installed)</li>
<li>In Adobe Acrobat (if installed)</li>
<li>In Microsoft Edge</li>
<li>In Chrome</li>
<li>In Internet Explorer</li>
</ol>
</li>
</ol>
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
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 a 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:
<ol>
<li>Work out which application will be used to open the PDF by the Shell command (I imagine through API calls.)</li>
<li>I need code for checking if the file is open
<ol>
<li>In Adobe Reader (if installed)</li>
<li>In Adobe Acrobat (if installed)</li>
<li>In Microsoft Edge</li>
<li>In Chrome</li>
<li>In Internet Explorer</li>
</ol>
</li>
</ol>
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