VBA: Saving without overwriting existing files

Question

how can I make sure that my VBA code is not overwriting existing files while saving?

Example: I'm saving every sheet as a new workbook, and want to have v1, v2, v3 etc. With the code below I'm always overwriting the existing file, as every file I save has the same file name with "_V1" ending...

    NewWbName = Left(wbSource.Name, InStr(wbSource.Name, ".") - 1)

For i = 1 To 9
    'check for existence of proposed filename
    If Len(Dir(wbSource.Path & Application.PathSeparator & NewWbName & "_V" & i & ".xlsx")) = 0 Then
        wbTemplate.SaveAs wbSource.Path & Application.PathSeparator & NewWbName & "_V" & i & ".xlsx"
        Exit For
    End If
Next i

If i > 9 Then
    '_V1.xlsx through _V9.xlsx already used; deal with this situation
    MsgBox "out of options"

      wbTemplate.Close False 'close template
    Next wsSource

    wbSource.Close False 'close source


End If
End Sub

Check if the file exists first e.g. https://stackoverflow.com/questions/16351249/vba-check-if-file-exists There are loads of examples of this on SO. If it already exists you then need to decide what to do...e.g. rename or add a numerical versioning etc. — QHarr, Sep 25 '18 at 07:25
Possible duplicate of [VBA check if file exists](https://stackoverflow.com/questions/16351249/vba-check-if-file-exists) — Paul, Sep 25 '18 at 08:32

score 3 · Accepted Answer · edited Sep 25 '18 at 07:38

3

Loop through various _Vn.xlsx variations until you find one that isn't there.

dim i as long, NewWbName as string

NewWbName = Left(wbSource.Name, InStr(wbSource.Name, ".") - 1)

for i=1 to 9
    'check for existence of proposed filename
    if len(dir(wbSource.Path & Application.PathSeparator & NewWbName & "_V" & i & ".xlsx")) = 0 then
        wbTemplate.SaveAs wbSource.Path & Application.PathSeparator & NewWbName & "_V" & i & ".xlsx"
        exit for
    end if
next i

if i>9 then
    '_V1.xlsx through _V9.xlsx already used; deal with this situation
    msgbox "out of options"
end if

If you are going to raise the loop into double digits, perhaps ... & "_V" & Format(i, "00") & ".xlsx would be better so that a folder sorted by name puts them in the correct order.

edited Sep 25 '18 at 07:38

Chronocidal

6,827
1
12
26

answered Sep 25 '18 at 07:32

thanks @Jeeped, I've updated my code with your code snippet and addded the "close workbook" part of it - but I'm getting an error message - "next without for"... – PlutoX Sep 25 '18 at 08:32
I've got it to work. But I had to delete the "if i > 9 then.." part. I'm not sure why it caused the issue. – PlutoX Sep 25 '18 at 08:47
If you run through all i (1 to 9) without finding a version to saveas then i is 10 when it exits the loop. This means that all possible versions have already been created. I'm not sure what the problem is. – Sep 25 '18 at 09:06

score 0 · Answer 2 · answered Sep 26 '18 at 01:16

Recommend using a date and time stamp for so many versions.

“V” & Format(date, “yyyymmdd”) & format(time, “hhmmss”) & “.xlsx”

Yes, you may still want to check for an existing file, but it’s seldom the user will submit input in less than a second

VBA: Saving without overwriting existing files

2 Answers2

Linked