修正Personal.xlsb中Workbook_Open宏的工作簿引用问题

我写了一个修改Excel页眉页脚日期时间字段的宏，希望它对特定用户打开的所有工作簿都生效，但当前宏里的For Each sht In ThisWorkbook.Sheets只会修改Personal.xlsb本身，无法作用到其他打开的文件，该怎么修正？

原代码：

Option Explicit

Sub Workbook_Open()

Dim arrTargetFields As Variant
Dim strTimeReplacement As String
Dim strDateReplacement As String
Dim arrReplacements As Variant
Dim sht As Worksheet
Dim i As Integer

arrTargetFields = Array("&D", "&T")
strTimeReplacement = "TimeSampleText"
strDateReplacement = "DateSampleText" 
arrReplacements = Array(strDateReplacement, strTimeReplacement)

For Each sht In ThisWorkbook.Sheets
    For i = 0 To 1
        With sht.PageSetup
            .LeftHeader = Replace(.LeftHeader, arrTargetFields(i), arrReplacements(i))
            .CenterHeader = Replace(.CenterHeader, arrTargetFields(i), arrReplacements(i))
            .RightHeader = Replace(.RightHeader, arrTargetFields(i), arrReplacements(i))
            'Replace Headers
            .LeftFooter = Replace(.LeftFooter, arrTargetFields(i), arrReplacements(i))
            .CenterFooter = Replace(.CenterFooter, arrTargetFields(i), arrReplacements(i))
            .RightFooter = Replace(.RightFooter, arrTargetFields(i), arrReplacements(i))
            'Replace Footers
        End With
    Next i
Next sht

Set sht = Nothing

End Sub

核心是把遍历范围从ThisWorkbook（仅当前宏所在的Personal.xlsb）改成遍历所有打开的工作簿，同时排除Personal.xlsb本身避免重复操作，修正后的代码如下：

Option Explicit

Sub Workbook_Open()
    Dim arrTargetFields As Variant
    Dim strTimeReplacement As String
    Dim strDateReplacement As String
    Dim arrReplacements As Variant
    Dim wb As Workbook
    Dim sht As Worksheet
    Dim i As Integer
    
    arrTargetFields = Array("&D", "&T")
    strTimeReplacement = "TimeSampleText"
    strDateReplacement = "DateSampleText"
    arrReplacements = Array(strDateReplacement, strTimeReplacement)
    
    ' 遍历所有打开的工作簿
    For Each wb In Application.Workbooks
        ' 排除宏所在的Personal.xlsb，避免重复修改自身
        If wb.Name <> ThisWorkbook.Name Then
            ' 遍历当前工作簿的所有工作表
            For Each sht In wb.Sheets
                For i = 0 To 1
                    With sht.PageSetup
                        .LeftHeader = Replace(.LeftHeader, arrTargetFields(i), arrReplacements(i))
                        .CenterHeader = Replace(.CenterHeader, arrTargetFields(i), arrReplacements(i))
                        .RightHeader = Replace(.RightHeader, arrTargetFields(i), arrReplacements(i))
                        .LeftFooter = Replace(.LeftFooter, arrTargetFields(i), arrReplacements(i))
                        .CenterFooter = Replace(.CenterFooter, arrTargetFields(i), arrReplacements(i))
                        .RightFooter = Replace(.RightFooter, arrTargetFields(i), arrReplacements(i))
                    End With
                Next i
            Next sht
        End If
    Next wb
    
    Set sht = Nothing
    Set wb = Nothing
End Sub

• 扩大遍历范围：新增For Each wb In Application.Workbooks循环，遍历Excel中所有已打开的工作簿
• 排除自身：通过If wb.Name <> ThisWorkbook.Name判断，跳过宏所在的Personal.xlsb，避免不必要的重复修改
• 调整循环层级：先循环工作簿，再循环每个工作簿下的工作表，确保所有打开文件的每个工作表都被处理

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