Q：如何使用VBA在工作簿文件名稱(chēng)后添加文件保存時(shí)間的后綴，并且能不斷的隨著保存操作更新這個(gè)后綴？
A：在Thisworkbook里面加入下面的代碼即可實(shí)現(xiàn)，并且能更新后綴，使得我們一眼就能看出文件的最后保存時(shí)間。
方法一 By 軒轅軾軻
- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- On Error Resume Next
- Application.DisplayAlerts = False
- Fold = ThisWorkbook.FullName
- Fpath = ThisWorkbook.Path
- ThisWorkbook.Save
- Application.DisplayAlerts = False
- T = Format(Now, " YYYY年MM月DD日HH時(shí)MM分SS秒")
- If Mid(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4, 1) = "秒" Then
- Temp = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 25)
- Else
- Temp = Split(ThisWorkbook.Name, ".")(0)
- End If
- Fname = Fpath & "\" & Temp & T & ".xls"
- ThisWorkbook.ChangeFileAccess xlReadOnly
- Name Fold As Fname
- Application.Workbooks.Open Fname, False
- ThisWorkbook.Close False
- Application.DisplayAlerts = False
- Cancel = False
- End Sub
方法二 By KevinChengCW
- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
- Dim mPath$, FN$, nName$
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- FN = ThisWorkbook.Name
- mPath = ThisWorkbook.Path
- With ThisWorkbook
- .ChangeFileAccess xlReadOnly
- If InStr(FN, "-Ver") > 0 Then
- .SaveAs mPath & "\" & Split(FN, "-Ver")(0) & "-Ver" & Format(Now, "yyyymmddhhmmss") & "." & Right(FN, Len(FN) - InStrRev(FN, ".")), ThisWorkbook.FileFormat
- Else
- .SaveAs mPath & "\" & Left(FN, InStrRev(FN, ".") - 1) & "-Ver" & Format(Now, "yyyymmddhhmmss") & "." & Right(FN, Len(FN) - InStrRev(FN, ".")), ThisWorkbook.FileFormat
- End If
- End With
- Kill mPath & "\" & FN
- Cancel = True
- Application.EnableEvents = True
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
|
