Add 001,002 strings to filename

[dantheman9]

Hi I have some files that need to saved in number order with a three number string eg 001, 002, 003 etc.

two questions, in the code below (in the loop) how can i increment the string number by one each time?
secondly, you will see Im using the getsaveasfilename, if there a method to add this number after that option (if so the user cannot remove this part of the file name?

Dim rng As Range
Dim wb As Workbook
Dim Fname As Variant
Dim filenumber As Variant
Set rng = Range("A1:A211")
Set wb = Workbooks.Add
filenumber = "001"
[COLOR=red]If Len(Dir(Path & filenumber & "*")) > 0 Then
Do While Len(Dir(Path & filenumber & "*")) > 0
filenumber = filenumber + 1
Loop
[/COLOR]End If

With wb
rng.Copy
.Worksheets(1).Range("A1").PasteSpecial Paste:=xlValues
' open path as set in path option box
ChDir (Path)
Application.DisplayAlerts = False
' set file name from textbox 1 (default is enter file name
Fname = Application.GetSaveAsFilename( _
[COLOR=red]InitialFileName:=filenumber & " - " & TextBox1,[/COLOR] _
[COLOR=red]' can i add the filenumber after this so the user cannot mod it?
[/COLOR]filefilter:="Text File, *.txt", _
Title:="Save File To...")
If Fname <> False Then
.SaveAs Fname, xlTextMSDOS
End If
.Close False

 

[Ralajer]

To get the filenumber to increment with the padded zeros, 001, 002, 003 ..., you can use the Format function. So

    [COLOR="red"]Dim filenumber As Integer
    filenumber = 1[/COLOR]
    
    If Len(Dir(Path & [COLOR="red"]Format(filenumber, "000")[/COLOR] & "*")) > 0 Then
        Do While Len(Dir(Path & [COLOR="red"]Format(filenumber, "000")[/COLOR] & "*")) > 0
            filenumber = filenumber + 1
        Loop
    End If

I assume the above is to find the lowest unused file in the Path Folder.

To have the file number add after the save as dialog box you could do this.

        ' set file name from textbox 1 (default is enter file name
        Fname = Application.GetSaveAsFilename( _
            InitialFileName:=[COLOR="Red"]TextBox1[/COLOR], _
            FileFilter:="Text File, *.txt", _
            Title:="Save File To...")
            
        If Fname <> False Then
           [COLOR="Red"] Fname = Format(filenumber, "000") & " - " & Fname[/COLOR]
            .SaveAs Fname, xlTextMSDOS

Where the File number is added after the save as dialog. I also removed the file number & " - " from the default file name to avoid doubling the file number (ex. 001 - 001 - File Name.txt)

You may not need the GetSaveAsFilename method instead you could use the value from the textbox1. That of course depends on your needs.