VBA text file increment

[Ben85]

I am trying to improve my overall coding. It took me a bit of time to get the code working properly and was wondering if there was a better way of doing what I did to handle if there is a duplicate file (goes to errtest: and keeps incrementing untill there isn't a duplicate file name.."

Thanks ahead of time for any constructive criticism.

As a note the all the "tb_" (textbox) in the code are being pulled from a user form that feeds this code additionally

Private Sub btn_order_click()
    Dim fs As Object
    Dim stream As Object
    Dim q As Long
    Dim file_name As String
    Dim file_path As String
    Dim file_type As String
    
    If tb_order = 0 Then 'tb_order is a text box from a form.
        MsgBox "Order Quantity must be above ZERO"
        Exit Sub
    End If
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    file_path = "c:\BGKTEST\"
    file_name = "TEST"
    file_type = ".txt"
    
    On Error GoTo errtest
        q = 0
        Set stream = fs.CreateTextFile(file_path & file_name & q & file_type, False, True)

cont:
    stream.WriteLine ("Please order the following...")
    stream.WriteLine
    stream.WriteLine ("CON Number: " & tb_con)
    stream.WriteLine ("Re-Order Quantity: " & tb_reorder)
    stream.WriteLine ("Quantity: " & tb_order)
    stream.WriteLine ("Description: " & tb_des)
    stream.Close
    Unload Me
    Exit Sub
        
errtest:
    On Error GoTo -1
    q = q + 1
    Set stream = fs.CreateTextFile(file_path & file_name & q & file_type, False, True)
    GoTo cont
End Sub

 

[John_w]

Change the duplicate file checking and creation code to this:

    Dim n As Long
    Dim fileSpec As String, newFile As String
    
    fileSpec = "C:\BGKTEST\TEST|n|.txt"
    
    Set FS = New Scripting.FileSystemObject
    
    n = 0
    Do
        newFile = Replace(fileSpec, "|n|", n)
        n = n + 1
    Loop While FS.FileExists(newFile)
    
    Set stream = FS.CreateTextFile(newFile)
    stream.WriteLine "Please order the following..."
    stream.WriteLine
    stream.WriteLine "CON Number: " & tb_con
    stream.WriteLine "Re-Order Quantity: " & tb_reorder
    stream.WriteLine "Quantity: " & tb_order
    stream.WriteLine "Description: " & tb_des
    stream.Close<n><n>

</n></n>

 

[Ben85]

Thank you John,
I like how you handled the increment. I did run into the error User-defined type not defined, but I just had to add the Reference "Microsoft Scripting Runtime" to my VBAProject and then that was fixed.
I did break up fileSpec into a file directory, name, and type just due to personal preference.