VBA:自动将excel表保存为V-1、V-2和V-3，这取决于文件夹中是否已经有具有此名称的文件。

Question

我在VBA工作。我想从工作表中保存带有值的excel文档。但是，可以存在相同文件名的重复。如果重复相同的文件名，我希望VBA将其保存为不同的版本号。例如，如果文件名为猫狗，并且有第二个文件保存为猫狗，我希望VBA自动将其保存为V-2。如果已经有一个V-2，要比保存为V-3等等.这是我到目前为止掌握的密码。它节省了很大的正常，但我有困难，以获得版本号添加。到目前为止，我已经附上了代码的图像。

“

path = ""
filename1 = ws.Range("D5").Text & 
ws.Range("O3").Text`e`ws.Range("D6").Text
If filename1(path & filename1 & ".xlsm") = False Then
ActiveWorkbook.SaveAs Filename:=(path & filename1 & ".xlsm"), 
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Exit Sub
End If


Do While Saved = False
 If filename1(path & filename1 & x & ".xlsm") = False Then
  ActiveWorkbook.SaveAs Filename:=(path & filename1 & x & ".xlsm"), 
 FileFormat:=xlOpenXMLWorkbookMacroEnabled
  Saved = True
 Else
   x = x + 1
 End If
Loop
 MsgBox "New file version saved (version " & x & ")"

Answer 1

不要覆盖保存的文件(版本控制)

• 调整常量部分中的值.

使用当前设置的

• ，它将创建具有以下名称的文件：

猫DOG.xlsm猫狗(V-2).xlsm猫狗(V-3).xlsm等.

在驱动器C.上的Test文件夹中

代码

Option Explicit

Sub DoNotOverWrite()
    
    Const dFolderPath As String = "C:\Test\"
    Const dBaseName As String = "CAT DOG"
    Const dLeft As String = " (V-"
    Const dFirstNumber As Long = 2
    Const dRight As String = ")"
    Const dExtension As String = ".xlsm"
    
    Dim dFilePath As String: dFilePath = dFolderPath & dBaseName & dExtension
    Dim dFileName As String: dFileName = Dir(dFilePath)
    Dim n As Long: n = dFirstNumber - 1
     
    Do Until Len(dFileName) = 0
        n = n + 1
        dFilePath = dFolderPath & dBaseName & dLeft & n & dRight & dExtension
        dFileName = Dir(dFilePath)
    Loop
    
    ' If the workbook is the one containing this code, use 'ThisWorkbook'.
    ActiveWorkbook.SaveAs dFilePath, xlOpenXMLWorkbookMacroEnabled
    
    If n < dFirstNumber Then
        MsgBox "File saved.", vbInformation
    Else
        MsgBox "New file version saved (version " & n & ")", vbInformation
    End If

End Sub