VBA Check if a file with the given name exists and create a new one with the version number if so

Question

I am trying to write code that checks if a file with a given name exists and if it does it creates a new one with name that ends v2, then v3 and so on.

Sub checkFileExists()

    Dim strFileExists, strFileName As String
    Dim nextDay as Date
    Dim i As Integer
    
    strFileName = file_name
    strFileExists = Dir(strFileName)

    If strFileExists = vbNullString Then
            strFileName = file_name
    Else
        i = 1
        Do
            i = i + 1
            strFileName = file_name & "_v" & i
            strFileExists = Dir(strFileName)
        Loop Until strFileExists = vbNullString

        strFileName = file_name & "_v" & i
    End If
 
End Sub

Unfortunatelly above code create almost always file name strFileName = "Plan_" & Format(nextDay, "yyyy-mm-dd") even if this file really exits.

Answer 1

This function will return the next unused filename version:

Function GetNextUnUsedVersionName(FilePath As String, Optional hasExtension As Boolean = True)
    Dim Result As String
    Dim Extension As String
    If (hasExtension) Then Extension = Mid(FilePath, InStrRev(FilePath, "."))
    Result = FilePath
    If Len(Dir(Result)) > 0 Then
        Dim n As Long
        n = 1

        Do
            n = n + 1
            Result = Left(FilePath, Len(FilePath) - Len(Extension)) & "_v" & n & Extension
            DoEvents
        Loop Until Len(Dir(Result)) = 0
    End If
    
    GetNextUnUsedVersionName = Result
End Function