VBA: Print to PDF and Overwrite Existing file

[pascawali]

Hi all

Just a quick one (hopefully). Having trouble with establishing an overwrite protocol when saving something as PDf.

The current code I am utilising is below, however it does not prompt to overwrite, instead not saving at all (I presume due to the error in the 4th line)
Code is as follows:

Sub PrintPDFTaxRecTrust()
    Dim FileName As String
    Application.DisplayAlerts = False
    On Error Resume Next

    sPath = ThisWorkbook.Path
    CurrentYear = Sheets("WP Index").Range("K6").Value
    
    With Worksheets("Tax Rec")
        sName = CurrentYear & " Tax Rec - " & .Range("A1").Value

        FileName = ThisWorkbook.Path & "\" & sName & ".pdf"

        Range("A1:H80").Select
        ActiveSheet.PageSetup.PrintArea = "A1:H80"
        .FitToPagesTall = False
        .FitToPagesWide = 1


        .ExportAsFixedFormat _
                Type:=xlTypePDF, _
                FileName:=FileName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
    End With

End Sub

Thank you in advance!

[Eastw00d]

Hi, in the first place I would check if the file already exists, because as you said, no warning is given,
secondly I would rewrite this, as this gives an error (you ruled that out in line 4):

        ActiveSheet.PageSetup.PrintArea = "A1:H80"
        .FitToPagesTall = False
        .FitToPagesWide = 1

into

        ActiveSheet.PageSetup.PrintArea = "A1:H80"
        With ActiveSheet.PageSetup
          .FitToPagesTall = False
          .FitToPagesWide = 1
        End With

Cheers
Erwin

[pascawali]

Gday Erwin

Thanks for your reply mate, really do appreciate it.
Great changes, I have now updated this!

In respect to check if the file exists and overwrite it - any ideas?

[Eastw00d]

Yes, you can add this into your code:

        Filename = ThisWorkbook.Path & "\" & sName & ".pdf"
        FileCheck (Filename)
        Select Case FileCheck(Filename)
        Case 1
            MsgBox ("The file " & Filename & " exist")
            'your code
        Case -1
            MsgBox ("The file " & Filename & " is already open")
            'your code
        Case 0
            MsgBox ("The file " & Filename & " is not in current directory")
            'so you can put the rest of your code here
        End Select

and below your macro:

Function FileCheck(ByVal sFull As String) As Integer
'returnvalue:
' -1 is open
' 1 is ondisk
' 0 is not on disk/error

    Dim iRes%, sName$
    Dim wb As Workbook
    sName = Dir(sFull)
    iRes = -(Len(sName) > 0)
    If iRes = 1 Then
        For Each wb In Application.Workbooks
            If StrComp(wb.Name, sName, vbTextCompare) = 0 Then
                iRes = -1
                Exit For
            End If
        Next
    End If
    FileCheck = iRes
End Function

Cheers
Erwin

[pascawali]

Hi Eastwood,
Just wanted to say a huge thank you for your assistance and coding.
The code worked exactly as required; I simply changed a few things for my needs.

Really do appreciate it

cheers