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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,530
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,118,414
Messages
5,571,966
Members
412,429
Latest member
brahmaiah
Top