Save Excel File as PDF With Duplicate Name Check

tim220225

New Member
Joined
Jun 4, 2012
Messages
21
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
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,546
Replace the ExportAsFixedFormat line with this code:
VBA 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

New Member
Joined
Jun 4, 2012
Messages
21
Replace the ExportAsFixedFormat line with this code:
VBA 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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,119
Messages
5,576,205
Members
412,706
Latest member
msousa25
Top