Check whether a PDF file is open before attempting to overwrite?

[Pedsdude]

I have some VBA code to save an Excel Worksheet as a PDF file. Within this code, I've included two checks - the first to ensure that a 'legitimate' filename has been provided, the second to ensure that the user does not accidentally overwrite a pre-existing report PDF.

Both of these checks work as intended, however, I have come across one issue.

If the user decides to overwrite a pre-existing PDF, but has that PDF file open at the time of exporting, then the following run-time error arises:

Run-time error '1004':

Document not saved. The document may be open, or an error may have been encountered when saving.

So my question is - is it possible to check whether the PDF file is already open?

If it is not possible, what would be the best solution? The only idea I have currently is to add an "On Error GoTo" line directly before the "ActiveSheet.ExportAsFixedFormat ..." line, and work on the assumption that the only error likely to arise will be as a result of this.

Below is the code:

Sub report_as_pdf()

the_drive = Left(ThisWorkbook.Path, WorksheetFunction.Find(":", ThisWorkbook.Path, 1) - 1)
the_directory = "" & ThisWorkbook.Path & Application.PathSeparator & ""
ChDrive the_drive
ChDir the_directory
start:

input_document = Application.GetSaveAsFilename(fileFilter:="PDF file (*.pdf),")

If input_document = False Then                                          ' Check if filename is blank (or just spaces)
    MsgBox "You must enter a filename."
    Exit Sub
ElseIf Dir(input_document) <> "" Then                                   ' Check if filename already exists (avoid overwrite with other PDFs)
    MSG1 = MsgBox("Filename already exists, overwrite anyway?", vbYesNo, "Confirm")
    If MSG1 = vbNo Then
        GoTo start
    End If
End If

Application.DisplayAlerts = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=input_document, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Application.DisplayAlerts = True

End Sub

[Dimitrov]

How about using On Error Resume Next? After the line where you set your display alerts to false, put in the following:

On Error Resume Next

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=input_document, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

If Err = 1004 Then
    Msgbox "Document not saved. There may already be an existing PDF file open with that name."
    Exit Sub
End if

On Error GoTo 0

Application.DisplayAlerts = True

This is not all that much different from the error code you are receiving, but at least with this error trapping scheme, the warning you get does not seem as dreadful as the VBA error that comes up, and the code keeps executing.

[Pedsdude]

Aha, thank you very much!