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.
精彩评论