Save Excel File as PDF With Duplicate Name Check

[tim220225]

Hello all,

I have the code below to convert and save the active worksheet as a PDF in a specific file location. Works great for what I am doing. How can this be amended to prompt if the file name being saved already exists in the folder to overwrite the existing folder or cancel the operation?


Sub SaveAsPDF()

'Saves active worksheet as pdf using Range

'of AX2,AX4'

Dim fName As String

With ActiveSheet

fName = .Range("AX2").Value & "." & .Range("B14").Value & " " & .Range("AX4").Value

.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

"C:\Users\Tim M\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fName, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End With

End Sub

 

[John_w]

Replace the ExportAsFixedFormat line with this code:

    If Dir("C:\Users\Tim M\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fname) <> vbNullString Then
        If MsgBox(fname & " exists.  Do you want to overwrite it?", vbYesNo) = vbYes Then
            'Put ExportAsFixedFormat line here
        End If
    End If

 

[tim220225]

Replace the ExportAsFixedFormat line with this code:

    If Dir("C:\Users\Tim M\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fname) <> vbNullString Then
        If MsgBox(fname & " exists.  Do you want to overwrite it?", vbYesNo) = vbYes Then
            'Put ExportAsFixedFormat line here
        End If
    End If

I believe I did as you suggested. When I run the code I get the Sub SaveAsPDF() line highlighted in yellow with the compile error: Expected End Sub Microsoft VBA message. What am I doing wrong here?


Sub SaveAsPDF()
'Saves active worksheet as pdf using Range
'of AX2,AX4'

Dim fName As String
With ActiveSheet
fName = .Range("AX2").Value & "." & .Range("B14").Value & " " & .Range("AX4").Value

If Dir("C:\Users\Tim M\Dropbox\PCL\PCL Work Orders\Tractor Internal Work Orders\" & fName) <> vbNullString Then
If MsgBox(fName & " exists. Do you want to overwrite it?", vbYesNo) = vbYes Then

.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Tim McLeod\Dropbox\Port City Logistics\PCL Work Orders\Tractor Internal Work Orders\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
End If

 

[John_w]

The End With and End Sub are missing.