使用ADODB读取Excel隐藏列时返回Null值的问题求助

问题 #

使用ADODB.Connection与Recordset对象读取Excel工作表数据时，整体流程无报错，但源Excel中被隐藏的列，Recordset查询该列仅返回Null值。该隐藏列可被连接/记录集识别，无字段未识别的SQL错误，甚至能获取rs.Field(i).Name，但ADO无法读取其值。使用rs.CopyFromRecordset方法、遍历字段记录或在即时窗口查看均返回Null。未找到连接/记录集可处理此情况的属性，请问这是ADO的限制或Bug吗？

补充信息 #

连接字符串使用标准ACE Provider，读取模式，属性为Excel XML或Excel Macro，代码如下：

cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Mode='Read';" & _
    "Data Source=" & fullFilePath & ";" & _
    "Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open

记录集设置如下：

With rs
    .ActiveConnection = cn
    .CursorType = adOpenStatic
    .Source = SQL_Query
    .Open
End With

回答 #

这是ACE OLEDB Provider的默认行为，并非ADO本身的Bug或限制。ACE驱动在读取Excel时，默认会忽略隐藏列的单元格值，仅保留字段名称信息。

解决方法有两种：

• 临时取消列隐藏：读取数据前，通过Excel对象模型打开目标文件，取消所有列的隐藏状态，读取完成后再按需恢复。示例代码：

  Dim xlApp As Object, xlWB As Object
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = False
  Set xlWB = xlApp.Workbooks.Open(fullFilePath)
  
  ' 取消当前工作表所有列隐藏
  xlWB.ActiveSheet.Columns.Hidden = False
  
  ' 执行ADO读取操作...
  
  ' 如需恢复原有隐藏状态，可先记录隐藏列信息再恢复，示例仅作参考
  ' xlWB.ActiveSheet.Columns.Hidden = True
  
  xlWB.Close SaveChanges:=False
  xlApp.Quit
  Set xlWB = Nothing
  Set xlApp = Nothing
  

• 修改连接字符串属性：在Extended Properties中添加IMEX=1，强制驱动处理混合数据类型列的同时，会强制加载隐藏列的实际值。调整后的连接字符串：

  cn.ConnectionString = _
      "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Mode='Read';" & _
      "Data Source=" & fullFilePath & ";" & _
      "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
  cn.Open
  

  注：IMEX=1原本用于解决混合数据类型列的读取问题，但实测对隐藏列读取同样有效，无需修改原有查询逻辑。

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