Excel引用外部文件无对应工作表时如何避免弹窗并自动处理

你遇到的弹窗是Excel处理外部引用缺失工作表时的内置交互逻辑，Application.DisplayAlerts = False无法拦截该类弹窗，因为它不属于普通的运行告警范畴。

方案1：缺失工作表时直接返回错误值，无交互弹窗 #

通过VBA先校验目标文件是否包含指定工作表，校验过程无需打开文件，确认存在后再写入公式，不存在则直接给单元格赋值错误值：

Sub SumWithoutAlert()
    Dim targetFolder As String, fileName As String
    Dim ws As Worksheet, resultCell As Range
    Dim objShell As Object, objFolder As Object, objFile As Object
    
    ' 配置参数
    targetFolder = "你的文件目录路径\" ' 注意末尾加反斜杠
    Set resultCell = ThisWorkbook.Sheets("汇总表").Range("A1") ' 结果写入起始单元格
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(targetFolder)
    
    Application.ScreenUpdating = False
    ' 遍历所有xlsx文件
    fileName = Dir(targetFolder & "*.xlsx")
    Do While fileName <> ""
        ' 跳过汇总文件自身
        If fileName <> ThisWorkbook.Name Then
            ' 校验工作表是否存在
            Dim sheetExist As Boolean
            sheetExist = False
            On Error Resume Next
            ' 无需打开文件即可检测工作表是否存在
            Dim testVal
            testVal = ExecuteExcel4Macro("'" & targetFolder & "[" & fileName & "]Interest table'!R1C1")
            If Err.Number = 0 Then sheetExist = True
            Err.Clear
            On Error GoTo 0
            
            If sheetExist Then
                ' 存在则写入原公式
                resultCell.Formula = "='" & targetFolder & "[" & fileName & "]Interest table'!$E$1"
            Else
                ' 不存在则写入错误值
                resultCell.Value = CVErr(xlErrNA)
            End If
            Set resultCell = resultCell.Offset(1, 0)
        End If
        fileName = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

方案2：缺失目标工作表时自动引用文件第一个工作表 #

只需修改上述代码中不存在工作表的分支逻辑，后台打开文件获取第一个工作表名后构造公式即可：

' 替换方案1中sheetExist=False后的分支代码
Else
    ' 后台打开文件获取第一个工作表名称
    Dim firstSheetName As String
    Dim objExcel As Object
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    Dim wb As Object
    Set wb = objExcel.Workbooks.Open(targetFolder & fileName, ReadOnly:=True)
    firstSheetName = wb.Sheets(1).Name
    wb.Close SaveChanges:=False
    objExcel.Quit
    Set objExcel = Nothing
    ' 构造引用第一个工作表的公式
    resultCell.Formula = "='" & targetFolder & "[" & fileName & "]" & firstSheetName & "'!$E$1"
End If

• 路径配置时务必确保末尾带反斜杠，否则会出现引用路径错误
• 若目标文件处于被占用状态，会导致读取失败，可根据需求新增错误捕获逻辑处理该场景

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