Save Excel sheet as .pdf but rename if file allready exists

[VeKa27]

Hi all,

I have an Excelfile that has to save one of his sheets as a PDF file on command with a macro in VBA. It works fine but when i save the file again (this can happen with the exact same documentname) the code overwrites automaticly the old PDF file with the new one. My wish is that the code sees that the PDF allready exists and give an number at the end of the documentname of the second file. So that means that if i save 4 times with the same documentname, the new code results in
Test.pdf
Test(1).pdf
Test(2).pdf
Test(3).pdf
Can someone help me on this one? I will be very, very thankfull..

This is my code:

Private Sub CommandButton3_Click()
Sheets("TEST").ExportAsFixedFormat Type:=xlTypePDF, _
Quality:=xlQualityStandard, _
fileName:="C:\Documents\Testfolder" & Range("AA6")
End Sub

 

[John_w]

You could call the GetNextFileName routine at https://www.mrexcel.com/forum/excel...uence-number-vba-post5092591.html#post5092591, with your fileName 'template'.

 

[VeKa27]

Hi John, Thx for your answer. Problem with the GetNextFileName routine is that I don't use always the same documentfile to save. The Range("AA6") cell has variable data that can be chosen by the user. Every time when this is different data, there is no problem for saving because de documentname is different. But when the user selects 2 times the same data in cell AA6, means that the documentname will be the the same as an before saved document. If then the user saves his last document, the code overwrites the old version and that I don't want. The code has to detect that there is already a document with that name and has to rename it with a number behind the original documentname. Do you see what I mean? Again thx to think with me..

 

[John_w]

Try this:

Private Sub CommandButton3_Click()
    Dim PDFfileName As String, p As Long
    PDFfileName = Range("AA6").Value
    p = InStrRev(PDFfileName, ".")
    PDFfileName = Left(PDFfileName, p - 1) & "|n|" & Mid(PDFfileName, p)
    PDFfileName = GetNextFileName("C:\Documents\Testfolder\" & PDFfileName)
    Sheets("TEST").ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, fileName:=PDFfileName
    MsgBox "Created " & PDFfileName
End Sub


Public Function GetNextFileName(filePathTemplate As String) As String

    Dim n As Integer
    
    n = 0
    GetNextFileName = Replace(filePathTemplate, "|n|", "")
    While Dir(GetNextFileName) <> vbNullString
        n = n + 1
        GetNextFileName = Replace(filePathTemplate, "|n|", "(" & n & ")")
    Wend
    
End Function

 

[VeKa27]

Hi John,

You.... are.... my.... hero... !! You cannot imagine how happy i am at this moment.
Your code works exactly as i wanted.

So, Thank you for your input. respect..