Results 1 to 3 of 3

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

Threaded View

Pedsdude Check whether a PDF file is... 10-07-2013, 05:42 AM
Dimitrov Re: Check whether a PDF file... 10-09-2013, 01:25 AM
Pedsdude Re: Check whether a PDF file... 10-09-2013, 03:52 AM
  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    Newcastle, UK
    MS-Off Ver
    2010
    Posts
    31

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

    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
    Last edited by Pedsdude; 10-09-2013 at 03:51 AM. Reason: Solved!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if a Word File is Open and If Open Close it with Excel VBA.
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-10-2012, 11:14 AM
  2. Check if file is open and open if closed
    By Jockster in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-22-2010, 08:18 AM
  3. Check to see if file is open, and if it is open, activate it
    By karen000 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-15-2009, 03:59 AM
  4. Crash attempting to open Excel Add-in using .dll file comprised of VB6 code
    By Sirhc M in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-26-2005, 07:05 PM
  5. vba to check presence of file and open/not open
    By fLiPMoD£ in forum Excel General
    Replies: 2
    Last Post: 04-28-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1