我在 VBA 作業。我想用我的作業表中的值保存 excel 檔案。但是，可以存在相同檔案名的重復。如果重復相同的檔案名，我希望 VBA 將其保存為不同的版本號。例如，如果檔案名為 CAT DOG 并且有第二個檔案保存為 CAT DOG，我希望 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 & ")"
uj5u.com熱心網友回復：
不要覆寫保存的檔案（版本控制）
調整常量部分中的值。
使用當前設定，它將創建具有以下名稱的檔案：
CAT DOG.xlsm CAT DOG (V-2).xlsm CAT DOG (V-3).xlsm etc.在
Test驅動器上的檔案夾中C。
編碼
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/480555.html
上一篇：訪問VBA串列不填充資料