使用SUMPRODUCT等跨工作簿函数需打开文件？求VBA宏解决方案

核心问题是公式引用外部工作簿时，源文件未打开会导致Excel无法解析引用路径，返回#REF!。用VBA可以直接读取关闭状态下的外部工作簿数据，彻底避开这个问题。

以下是两种实用的VBA实现方案：

1. 读取单个关闭工作簿的指定单元格数据 #

这个通用函数可以直接获取指定路径下工作簿的单元格值，不需要打开文件：

Function GetClosedWorkbookValue(filePath As String, sheetName As String, cellAddress As String) As Variant
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    On Error GoTo ErrorHandler
    ' 构建查询语句，读取指定单元格
    rs.Open "SELECT * FROM [" & sheetName & "$" & cellAddress & ":" & cellAddress & "]", _
            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & _
            ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
    
    If Not rs.EOF Then
        GetClosedWorkbookValue = rs.Fields(0).Value
    Else
        GetClosedWorkbookValue = "" ' 无数据时返回空
    End If
    
    rs.Close
    Set rs = Nothing
    Exit Function
    
ErrorHandler:
    GetClosedWorkbookValue = "读取失败" ' 捕获错误返回提示
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
End Function

使用方法 #

在汇总表单元格中输入：
=GetClosedWorkbookValue("C:\财务数据\销售部.xlsx", "月度数据", "B5")
即可直接获取对应文件指定单元格的值，不需要打开源工作簿。

2. 批量汇总指定文件夹下的所有财务工作簿 #

如果需要批量汇总文件夹内所有同类工作簿的指定数据，可以用这个子过程：

Sub BatchSumFinancialData()
    Dim folderPath As String
    Dim fileName As String
    Dim totalSum As Double
    Dim targetSheet As Worksheet
    Dim sourceValue As Variant
    
    ' 设置汇总表目标工作表和文件夹路径
    Set targetSheet = ThisWorkbook.Worksheets("汇总表")
    folderPath = "C:\财务数据\" ' 替换为你的财务工作簿所在文件夹
    totalSum = 0
    
    ' 遍历文件夹内的所有xlsx文件
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        ' 跳过汇总工作簿本身
        If fileName <> ThisWorkbook.Name Then
            ' 读取每个工作簿的指定单元格（这里示例为"月度数据"工作表的B5单元格）
            sourceValue = GetClosedWorkbookValue(folderPath & fileName, "月度数据", "B5")
            ' 判断是否为数值，累加到总和
            If IsNumeric(sourceValue) Then
                totalSum = totalSum + sourceValue
            End If
        End If
        fileName = Dir
    Loop
    
    ' 将结果写入汇总表的指定单元格（示例为A1）
    targetSheet.Range("A1").Value = totalSum
    MsgBox "汇总完成，总金额：" & totalSum, vbInformation
End Sub

使用说明 #

1. 先把上面的GetClosedWorkbookValue函数复制到VBA模块中
2. 修改folderPath为你的财务工作簿所在文件夹路径
3. 修改要读取的工作表名（"月度数据"）和单元格地址（"B5"）
4. 修改汇总结果写入的单元格（targetSheet.Range("A1")）
5. 运行BatchSumFinancialData宏即可完成批量汇总

注意事项 #

• 确保源工作簿的结构一致（工作表名、目标单元格位置统一），否则会读取失败
• 如果是旧版Excel（.xls格式），需要修改OLEDB连接字符串中的Excel 12.0 Xml为Excel 8.0
• 运行宏前需要确保Excel启用了宏功能，并且文件夹路径正确
• 若遇到权限问题，检查文件夹是否允许读取访问

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