Save file PDF, if exist add number

[Infine]

Hello,

I am saving Excel files to PDF and everything works. However, I want to add that if the file exists already it should add 1 number. Does anyone know how do do a "check" if it exists and add "n +1" or something?

Here is the code I am using:

'Create and assign variables
Dim saveLocation As String
saveLocation = "Y:\UTFLYTT\" & varDepot & ".STF.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

n = 0
Like IF Y:\UTFLYTT\" & varDepot & ".STF.pdf exists, Then Y:\UTFLYTT\" & varDepot & ".STF ". N+1 .".pdf
and if that also exists (n = 1. then do n=2 etc)

 

[Sequoyah]

Hi Infine,
try this code

Sub TEST()
'Create and assign variables
Dim saveLocation As String

saveLocation = "Y:\UTFLYTT\" & varDepot & ".STF.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=GetNextAvailableName(saveLocation)

End Sub

Function GetNextAvailableName(ByVal strPath As String) As String

    With CreateObject("Scripting.FileSystemObject")

        Dim strFolder As String, strBaseName As String, i As Long
        strFolder = .GetParentFolderName(strPath)
        strBaseName = .GetBaseName(strPath)
      
        Do While .FileExists(strPath)
            i = i + 1
            strPath = .BuildPath(strFolder, strBaseName & " " & i & ".pdf")
        Loop

    End With

    GetNextAvailableName = strPath

End Function