VBA中如何仅通过表名用ADODB连接Excel ListObject查询数据？

核心问题原因 #

ADODB连接Excel时，默认只能识别工作表和用户定义的命名范围，Excel的ListObject（手动创建的经典表）属于Excel对象模型专属元素，OLEDB驱动不会直接将其视为可查询的表，所以直接用表名会无法识别。

可行解决方案 #

方案1：动态获取ListObject的范围，再构建ADODB查询

先后台打开目标工作簿，读取ListObject的当前范围，再基于这个动态范围执行ADODB查询，完美适配范围变化的场景：

Sub QueryDynamicTable()
    Dim targetWb As Workbook
    Dim targetTable As ListObject
    Dim tableRangeAddr As String
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim filePath As String
    
    ' 替换为你的目标文件路径
    filePath = "C:\Resources\你的文件.xlsx"
    
    ' 后台只读打开工作簿，不显示界面
    Set targetWb = Workbooks.Open( _
        Filename:=filePath, _
        ReadOnly:=True, _
        UpdateLinks:=False, _
        Visible:=False _
    )
    
    ' 获取指定工作表中的ListObject
    Set targetTable = targetWb.Worksheets("1A").ListObjects("Table_1A")
    ' 拼接带工作表名的范围地址（ADODB需要的格式）
    tableRangeAddr = "'" & targetTable.Parent.Name & "'!" & targetTable.Range.Address(External:=False)
    
    ' 关闭工作簿，无需保存
    targetWb.Close SaveChanges:=False
    
    ' 初始化ADODB连接
    Set conn = New ADODB.Connection
    conn.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & filePath & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    
    ' 执行查询（示例：筛选材料为"钢"的记录，读取材料和20℃属性）
    Set rs = conn.Execute( _
        "SELECT [材料], [20℃] FROM " & tableRangeAddr & " WHERE [材料] = '钢'" _
    )
    
    ' 处理查询结果（示例：输出到立即窗口）
    Do While Not rs.EOF
        Debug.Print "材料：" & rs("材料").Value, "20℃属性：" & rs("20℃").Value
        rs.MoveNext
    Loop
    
    ' 清理资源
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    Set targetTable = Nothing
    Set targetWb = Nothing
End Sub

方案2：给ListObject创建动态命名范围

手动或用VBA给Table_1A创建一个动态命名范围，ADODB可以直接识别这个名称，无需每次读取范围：

1. 手动创建命名范围：

   • 打开目标工作簿，按Ctrl+F3打开名称管理器
   • 新建名称，比如Table1A_Data，引用位置输入：=1A!Table_1A[#All]
   • 保存后，ADODB查询可直接使用这个名称：

     SELECT * FROM [Table1A_Data] WHERE [材料] = '铝'
     

2. VBA自动维护命名范围（可选，确保范围随ListObject自动更新）：

   Sub UpdateTableName()
       Dim targetWb As Workbook
       Dim targetTable As ListObject
       Dim filePath As String
       
       filePath = "C:\Resources\你的文件.xlsx"
       Set targetWb = Workbooks.Open(filePath, ReadOnly:=False)
       Set targetTable = targetWb.Worksheets("1A").ListObjects("Table_1A")
       
       ' 更新或创建命名范围
       ThisWorkbook.Names.Add _
           Name:="Table1A_Data", _
           RefersTo:=targetTable.Range, _
           Visible:=True
           
       targetWb.Close SaveChanges:=True
   End Sub
   

注意事项 #

• 确保安装了Microsoft.ACE.OLEDB.12.0驱动（Office默认自带，若缺失需单独安装）
• 32位/64位Office需对应使用匹配的驱动版本，避免运行报错
• HDR=YES表示你的ListObject第一行是表头，若没有表头需改为HDR=NO，用F1、F2等字段名查询

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