Macro to save and version control file

I want the macro to save my Excel file, but to check first if a file with the same name already exists. If yes, then macro to save the file with a version after. E.g. if Testworkbook.xls already exists, t开发者_如何学Pythonhen macro to save the new file as Testworkbook_v1.xls and so on. Below is the code that I tried but it's giving an error.

Sub SaveAs()    
    ChDir "C:\Documents and Settings\Sae\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Sae\Testworkbook" & v + 1 & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

---

This code will work whenever you save the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim filePath As String, wbName As String, version As Integer

    filePath = "C:\Documents and Settings\Sae\Desktop" //Change as appropriate
    wbName = "Testworkbook - v"

    If Dir(filePath & Application.PathSeparator & ThisWorkbook.Name) = "" Then // Workbook does not exist
        ThisWorkbook.SaveAs fileName:=filePath & Application.PathSeparator & wbName & 1
    Else
        version = CInt(VBA.Right$(VBA.Left$(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), VBA.Len(VBA.Left$(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)) - Len(wbName))) + 1
        ThisWorkbook.SaveAs fileName:=filePath & Application.PathSeparator & wbName & version
    End If
End Sub

Note that this assumes that .xls is the file extension

---

Dir() will help you determine if a file exists already.

Workbook.SaveAs() will help you save the file out.

You'll need to concatenate your strings together with the & operator (to add on the V1).

Edit:

Thanks for posting what you have. I came up with

Sub SaveNumberedFile()

    Static count As Integer
    Dim filestr As String
    filestr = "C:\Users\Me\Documents\Code\TestWkbk"
    ThisWorkbook.Activate 'Activate whichever workbook you need
    If count = 0 Then
        ActiveWorkbook.SaveAs (filestr & ".xls")

    ElseIf count > 0 And Dir(filestr & "v" & count - 1 & ".xls") = "" Then
        ActiveWorkbook.SaveAs (filestr & "v" & count & ".xls")
    End If

    count = count + 1

End Sub

I used a static variable to keep track of the numbers. This will retain the value of count between calls to the macro. It will not keep track of count after you close the workbook. In that situation, you can check to see what the last file number was using the Dir() command, and initialize the static variable to that value.

I did not add all of the frills to the SaveAs command, but you seem to know which ones you need.

Try it out, it may need some tweaking.