如何调整文件命名字符串顺序？VBA备份文件名排序修改需求

Got it, let's tweak your code so the original filename comes first, followed by the timestamp—no more awkward timestamp prefixes. The key fix here is splitting the workbook's name into its base name and file extension, otherwise we'd end up with broken extensions like Book1.xlsx2024_05_20_14_30_00 that Excel can't recognize.

Here's the updated, fully functional code:

Sub SaveToLocations()
' 将活动文件保存至当前位置及另外两个备份位置，文件名在前、日期时间戳在后
Dim datim As String
Dim wbName As String
Dim wbBaseName As String
Dim wbExt As String

' 生成日期时间戳（保留格式，去掉末尾多余下划线）
datim = Format(Now, "yyyy_mm_dd_hh_mm_ss")

' 获取当前工作簿的完整文件名
wbName = ActiveWorkbook.Name

' 拆分文件名：分离主名称和扩展名
If InStr(wbName, ".") > 0 Then
    wbBaseName = Left(wbName, InStrRev(wbName, ".") - 1)
    wbExt = Right(wbName, Len(wbName) - InStrRev(wbName, "."))
Else
    ' 兼容无扩展名的文件（Excel文件一般不会出现，但留个兜底）
    wbBaseName = wbName
    wbExt = ""
End If

' 构建新的备份文件名：主名称_时间戳.扩展名
Dim backupFileName As String
If wbExt <> "" Then
    backupFileName = wbBaseName & "_" & datim & "." & wbExt
Else
    backupFileName = wbBaseName & "_" & datim
End If

' 保存到两个备份位置
ActiveWorkbook.SaveCopyAs "I:\FilesBackupCS\" & backupFileName
ActiveWorkbook.SaveCopyAs "E:\Coconut Shade Docs\FilesBackupCS\" & backupFileName

' 保存原文件
ActiveWorkbook.Save
End Sub

Key Changes Breakdown: #

• Split Filename Components: We added logic to separate the workbook's base name (e.g., QuarterlyReport) from its extension (e.g., xlsx). This ensures the timestamp gets inserted before the extension, not after.
• Adjusted Naming Order: Replaced the old [Timestamp][OriginalName] pattern with [BaseName]_[Timestamp].[Extension] for backups.
• Cleaned Up Timestamp: Removed the trailing underscore from datim since we're explicitly adding an underscore between the base name and timestamp—no more accidental double underscores.
• Error Resilience: Added a fallback for files without extensions to avoid runtime errors, even though this scenario is rare for Excel workbooks.

内容的提问来源于stack exchange，提问作者Mike