求助：Excel自动更新跨文件引用路径中的最近工作日日期

我来给你两个实用的解决方案，帮你彻底告别手动改日期的繁琐操作，还能避免人为错误：

这个方案纯靠公式实现，不需要写代码，适合对VBA不太熟悉的场景。核心思路是用WORKDAY函数计算前一个工作日，再用TEXT转换为文件名需要的格式，最后通过INDIRECT拼接完整的外部文件引用路径。

最终公式 #

=K10-INDIRECT("'O:\Daily Vols\PDFsourcefiles\[Daily PDF "&TEXT(WORKDAY(Comments!A1,-1),"yyyy.mm.dd")&".xlsm]POWERPDF'!$K$10")

公式拆解 #

• WORKDAY(Comments!A1,-1)：根据Comments工作表A1的当日日期，计算前一个工作日（自动排除周六周日）。如果需要排除法定节假日，可以添加第三个参数，比如WORKDAY(Comments!A1,-1,Sheet2!$A$1:$A$10)（假设Sheet2的A列是你的节假日列表）。
• TEXT(..., "yyyy.mm.dd")：把计算出的前一个工作日转换成文件名需要的日期格式（和你现有文件名的格式完全匹配）。
• INDIRECT(...)：把拼接好的路径字符串转换成可识别的单元格引用。

注意事项 #

• 引用的外部文件（前一个工作日的Daily PDF xxx.xlsm）必须处于打开状态，否则INDIRECT会返回#REF!错误。
• 确保文件名格式严格遵循Daily PDF yyyy.mm.dd.xlsm，如果格式有变化，要同步调整TEXT函数的格式参数。

如果你的文件数量多，或者不想每次都打开外部文件，用VBA宏会更高效稳定。它可以自动遍历目标单元格，批量替换公式里的日期部分，全程无需手动操作。

宏代码示例 #

Sub UpdatePreviousWorkdayFormula()
    Dim currentDate As Date
    Dim prevWorkday As Date
    Dim newDateStr As String
    Dim targetRange As Range
    Dim cell As Range
    
    ' 从Comments表A1获取当日日期
    currentDate = ThisWorkbook.Worksheets("Comments").Range("A1").Value
    
    ' 计算前一个工作日（如需排除法定节假日，添加第三个参数：Worksheets("节假日").Range("A:A")）
    prevWorkday = WorksheetFunction.WorkDay(currentDate, -1)
    
    ' 转换为文件名对应的日期格式
    newDateStr = Format(prevWorkday, "yyyy.mm.dd")
    
    ' 设置需要更新公式的目标范围（请根据你的实际需求修改）
    Set targetRange = ThisWorkbook.ActiveSheet.Range("K10:K100")
    
    ' 遍历每个单元格更新公式
    For Each cell In targetRange
        If cell.HasFormula Then
            ' 替换公式中的旧日期为新日期
            cell.Formula = Replace(cell.Formula, Mid(cell.Formula, InStr(cell.Formula, "Daily PDF ") + 9, 10), newDateStr)
        End If
    Next cell
    
    MsgBox "公式已自动更新完成！", vbInformation
End Sub

使用方法 #

1. 按Alt+F11打开VBA编辑器。
2. 在左侧项目窗口中，右键点击你的工作簿，选择「插入」→「模块」。
3. 将上面的代码粘贴到模块窗口中，调整targetRange为你实际需要更新的单元格范围。
4. 保存工作簿为.xlsm格式（启用宏的工作簿）。
5. 回到Excel界面，按Alt+F8选择UpdatePreviousWorkdayFormula，点击「运行」即可；也可以给这个宏添加一个工作表按钮，一键触发更新。

优势 #

• 不需要打开外部文件就能完成公式更新。
• 支持批量更新多个单元格的公式，效率更高。
• 可以灵活添加法定节假日排除逻辑。

内容的提问来源于stack exchange，提问作者N.Sharp