Ask if you want to replace file

[Jhowss7]

Hello everyone,

I have a code that serves its purpose well. Export a selection to pdf. However it replaces a file with the same name. I need it to ask if you want to replace or not
If it is, don't loop until something is done.

The code:

Sub PDF()

lr = Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious).Row
Range("M1:AE" & lr).Select


Dim pas
pas = Application.GetSaveAsFilename(InitialFileName:=Range("M1") & " " & Format(Now(), "DD-MM-YY hhmm"), _
fileFilter:="PDF, *.pdf", _
Title:="Exportar em PDF")

If TypeName(pas) = "Boolean" Then
Else
Selection.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=pas, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End If
End Sub

 

[Gokhan Aycan]

A messagebox or an inputbox may help perhaps?

 

[veyselemre]

Sub PDF()

    Dim lR&, pas$, fName$, ret

    lR = Range("M:M").Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Range("M1:AE" & lR).Select

    fName = Range("M1") & " " & Format(Now(), "DD-MM-YY hhmm") & ".pdf"

    pas = Application.GetSaveAsFilename(InitialFileName:=fName, _
                                        fileFilter:="PDF, *.pdf", _
                                        Title:="Exportar em PDF")

    If pas <> "False" Then
        al = Dir(fName)
        If Dir(fName) <> "" Then
            ret = MsgBox("There is a file with this name, do you want to overwrite it?", vbYesNo + vbCritical, "Attention !")
            If ret = vbNo Then
                MsgBox "Cancelled..", vbInformation, "File Found!"
                Exit Sub
            Else
                Kill fName
            End If
        End If
        Selection.ExportAsFixedFormat Type:=xlTypePDF, _
                                      Filename:=pas, Quality:=xlQualityStandard, _
                                      IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                      OpenAfterPublish:=False

    Else
        MsgBox "Stopped..", vbInformation, "Cancelled..."
    End If

End Sub