Excel宏打开DAILY OBSERVATIONS.xltm时文件名追加数字的解决诉求

问题出在当DAILY OBSERVATIONS.xltm已经处于打开状态时，再次用Workbooks.Open调用它，Excel会自动在文件名末尾追加数字来区分重复打开的文件。要解决这个问题，我们需要先检查模板文件是否已经打开，再决定是激活现有窗口还是打开新实例。

修改后的宏代码如下：

Sub SaveWithDate()
    Dim fname As String
    fname = ActiveWorkbook.Name
    Const SubName As String = "SaveWithDate"   'The name of this routine
    Const SheetName As String = "OBSERVATIONS"      'The name of the sheet that can use this sub
    
    If ActiveSheet.Name <> SheetName Then
        MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
        Exit Sub
    End If
    
    If ActiveWorkbook.Name <> "DAILY OBSERVATIONS.xltm" Then
        MsgBox "This macro can only be called from 'DAILY OBSERVATIONS.xltm. You already renamed this workbook.'", vbOKOnly, SubName
        Exit Sub
    End If

    Dim fileName As String
    Dim currentDate As String
    Dim templateWB As Workbook
    Dim templatePath As String
    
    ' Get the current date in the format YYYYMMDD
    currentDate = Format(Now, "yyyymmdd")

    ' Specify the file name with the current date
    fileName = "L:\DAILY OBSERVATIONS\" & currentDate & " DAILY OBSERVATIONS"

    ' Save the workbook with the specified file name
    ThisWorkbook.SaveAs fileName:=fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    ' 定义模板文件路径
    templatePath = "L:\DAILY OBSERVATIONS\DAILY OBSERVATIONS.xltm"
    
    ' 检查模板是否已打开
    On Error Resume Next
    Set templateWB = Workbooks("DAILY OBSERVATIONS.xltm")
    On Error GoTo 0
    
    If Not templateWB Is Nothing Then
        ' 模板已打开，直接激活
        templateWB.Activate
    Else
        ' 模板未打开，执行打开操作
        Set templateWB = Workbooks.Open(templatePath)
    End If
End Sub

关键修改说明 #

• 新增了检查工作簿是否已打开的逻辑：通过Workbooks("DAILY OBSERVATIONS.xltm")尝试获取已打开的模板实例
• 如果模板已经打开，直接激活该工作簿窗口，避免重复打开导致文件名被追加数字
• 如果模板未打开，再执行正常的打开操作

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