如何通过VBA宏应用Filter-Filter-Offset跨工作簿取数公式

问题说明 #

需要在工作簿1的VBA宏中，通过嵌套FILTER+OFFSET公式从工作簿2提取数据。手动在工作簿1的A7单元格写入以下公式可成功取数：

=FILTER(FILTER(OFFSET('[Glass Order Form Template.xlsm]Glass Schedule'!$C$8,0,0,3001,16),'[Glass Order Form Template.xlsm]Glass Schedule'!$D$8:$D$3008<>""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})

但VBA中直接硬编码工作簿名称的方式缺乏灵活性，尝试用变量替换工作簿/工作表名称时失败，导致跨工作簿引用出错。

修正后的VBA代码 #

Sub ImportData()
    Dim dataFilePath As String
    Dim dataWorkbook As Workbook
    Dim AnswerYes As VbMsgBoxResult ' 修正变量类型，原String类型错误
    
    Dim sourceSheet As Worksheet    ' 数据源工作表：Glass Schedule
    Dim targetSheet As Worksheet    ' 目标工作表：QC
    
    ' 让用户选择数据源文件
    dataFilePath = Application.GetOpenFilename("Excel Files (*.xlsm), *.XLSM")
    If dataFilePath = "False" Then Exit Sub ' 用户取消选择则退出
    
    ' 打开选中的数据源工作簿
    Set dataWorkbook = Workbooks.Open(dataFilePath)
    
    ' 绑定数据源和目标工作表
    Set sourceSheet = dataWorkbook.Worksheets("Glass Schedule")
    Set targetSheet = ThisWorkbook.Worksheets("QC")
    
    ' 复制基础信息：Job Number和Job Name
    sourceSheet.Range("D1").Copy
    targetSheet.Range("B2").PasteSpecial xlValues
    
    targetSheet.Range("B3").UnMerge ' 取消合并单元格（如果之前是合并状态）
    sourceSheet.Range("D2").Copy
    targetSheet.Range("B3").PasteSpecial xlValues
    
    ' 动态构建跨工作簿的FILTER公式
    With targetSheet
        .Range("A7").Formula = "=FILTER(FILTER(OFFSET('[" & dataWorkbook.Name & "]" & sourceSheet.Name & "'!$C$8,0,0,3001,16),'[" & dataWorkbook.Name & "]" & sourceSheet.Name & "'!$D$8:$D$3008<>""""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})"
    End With
    
    ' 询问用户是否关闭数据源工作簿
    AnswerYes = MsgBox("Close Glass Take Off Sheet?", vbQuestion + vbYesNo, "User Response")
    If AnswerYes = vbYes Then
        dataWorkbook.Close SaveChanges:=False ' 关闭时不保存，避免意外修改
    End If
End Sub

关键修正点 #

• 动态构建公式字符串：用dataWorkbook.Name和sourceSheet.Name替代硬编码的工作簿/工作表名称，确保无论用户选择的文件名称是什么，公式都能正确引用数据源
• 引号转义处理：VBA中字符串里的双引号需要用两个双引号（""）来表示一个实际的双引号，所以公式里的<>""要写成<>""""
• 变量类型修正：原代码中AnswerYes定义为String类型，实际应该用VbMsgBoxResult类型，避免类型不匹配问题
• 关闭工作簿优化：添加SaveChanges:=False，防止误保存数据源文件的修改

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