如何修改VBA代码实现Excel文件按A2值加指定后缀自动命名保存

你只需要修改文件名拼接逻辑，同时修复原代码的小问题即可实现需求，调整后的完整代码如下：

Public Sub newFile()
    'Save "file 2" as new workbook with student name from File1
    
    'Set a variable for the file name (INCLUDING PATH) to File2
    Dim File2 As String: File2 = "C:\test\File2.xlsx"
    
    'Set a variable for the path the folder you want to save the new file in.
    Dim NewFilePath As String: NewFilePath = "C:\Test_New\"
    
    'Set the cell with the name you want the new file to have. Replace "Sheet1" and "A2" with the appropriate worksheet/cell
    Dim NewFileName As String: NewFileName = ThisWorkbook.Sheets("Sheet1").Range("A2").Value

    ' 新增空值判断，避免A2为空时生成非法文件
    If Trim(NewFileName) = "" Then
        MsgBox "A2单元格内容为空，请输入内容后重试", vbExclamation
        Exit Sub
    End If
    
    'Save Workbook and disable alerts so the new file saves without pop-ups opening
    Application.DisplayAlerts = False
    Workbooks.Open Filename:=File2
    ' 核心修改：拼接固定后缀" date"
    ActiveWorkbook.SaveAs Filename:=NewFilePath & NewFileName & " date.xlsx", FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    ' 修复原代码问题：操作完成后恢复提示弹窗
    Application.DisplayAlerts = True
End Sub

关键修改说明 #

• 核心命名逻辑调整：在SaveAs的文件名参数中，新增了 " date" 拼接段，完全匹配你要求的「A2内容+固定后缀」规则，比如A2输入Apple，生成的文件就是Apple date.xlsx
• 如果你示例中的date是指当前系统日期，把拼接段替换为 " " & Format(Date, "yyyy-mm-dd") 即可，会自动生成带当日日期的文件名，比如Apple 2024-05-20.xlsx
• 修复了原代码执行完成后未将Application.DisplayAlerts恢复为True的问题，避免后续Excel使用时无提示弹窗
• 新增A2空值判断逻辑，避免单元格为空时生成无效名称的文件

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