If file exists add a number to filename

[Jaye7]

I got the following code a long time ago and if worked perfectly for the workbook that it related too however I have tried to use it in another workbook and instead of adding a number to the filename it is adding .xls, so if the file exists it ends up as i.e. testbook.xls.xls whereas it should be testbook2.xls, any help would be appreciated.

original code:

 Dim n As Integer
    Dim filename As String
 
    n = 0
    Do
        filename = "C:\Temp Forms\Xtemplate" & IIf(n = 0, "", n) & ".xls"
        n = n + 1
    Loop Until Dir(filename) = ""
 
    ActiveWorkbook.SaveAs filename

adapted code
textbox27 is the activeworkbook name including .xls
I tried to remove the .xls but it ends up as .xls1.xls

 Dim n As Integer
    Dim filename As String
 
    n = 0
    Do
        filename = "H:\" & textbox27.value & IIf(n = 0, "", n) & ".xls"
        n = n + 1
    Loop Until Dir(filename) = ""
 
    ActiveWorkbook.SaveAs filename

 

[JoeMo]

For

textbox27.value
Try substituting
Left(textbox27.value,len(textbox27.value) - 4)

 

[Jaye7]

Hi JoeMo,

Unfortunately the code you provided is not working as then it tells you that the file already exists whereas it should automatically save the file with a number on the end.

 

[JoeMo]

I can't reproduce your problem - this works fine for me. If file testbook.xls already exists, then the loop should continue to the next iteration and produce testbook1.xls and so on. This is what your code with my alteration does for me.

Here's the code I tested:

Sub testFile()
Dim n As Integer
Dim filename As String
n = 0
Do
    filename = "H:\" & Left(textbox27.Value, Len(textbox27.Value) - 4) & IIf(n = 0, "", n) & ".xls"
    n = n + 1
Loop Until Dir(filename) = ""

ActiveWorkbook.SaveAs filename
End Sub

 

[Kenneth Hobson]

If you want to use the Windows convention of filename (2).xls, see my api method at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1041

 

[Jaye7]

JoeMo,

I copied your code into my form and tried it on a new book [book1] it saved it as B.xls, I ran the code again and it saved the B.xls as B1.xls, I ran it again it is saved B1.xls as B12.xls, ran it again and it saved B12.xls as B121.xls.

Not sure what is happening, but it just isn't working for me.

Kenneth, I will play around with the scripts that you provided on your link and see if I can get it to work the way I want, thanks.

 

[JoeMo]

JoeMo,

I copied your code into my form and tried it on a new book [book1] it saved it as B.xls, I ran the code again and it saved the B.xls as B1.xls, I ran it again it is saved B1.xls as B12.xls, ran it again and it saved B12.xls as B121.xls.

Not sure what is happening, but it just isn't working for me.

Kenneth, I will play around with the scripts that you provided on your link and see if I can get it to work the way I want, thanks.

Thr reason it saves it as B, B1, ... is that you said there was an extra .xls on your files. I set the code so that the last 4 characters (which for your double xls problem would be .xls) are removed. In your test case, you have only Book1 so removing the last 4 characters yields B. In your original post you said textbox27.value includes the .xls extension. This is what you want to remove.

 

[tfaulkes]

Sub createnewfile()
Dim ofso As New FileSystemObject
Dim sfile As String
Dim sext As String
sfile = "C:\Documents and Settings\All Users\Desktop\testfile.xls"
lfile_count = 1
If ofso.FileExists(sfile) Then
    sext = ofso.GetExtensionName(sfile)
    sfile = Left(sfile, InStr(1, sfile, s) - 2)
    
    Do Until Not ofso.FileExists(sfile & "." & sext)
        sfile = sfile & lfile_count
        lfile_count = lfile_count + 1
    Loop
End If
ActiveWorkbook.SaveAs filename:=sfile, FileFormat:=xlNormal
    
End Sub

 

[Jaye7]

Thanks for your help everyone,

I got around it by adding a textbox with a value of 1 which increases by 1 if the file exists it adds a one to the number i.e. 1 becomes 2 and it adds it to the file name and then checks if the file exists, if it exists then it loops and increases the textbox value by 1 again until finally no file exists and then it runs the next macro.

Thanks again.