使用ADODB查询关闭的Excel工作簿返回NULL值问题求助

你遇到的返回NULL问题由两个常见原因共同导致：

• 关闭状态下的Excel文件中，公式单元格仅存储公式本身，不存储计算结果。ACE OLEDB驱动不会主动触发公式计算，仅能读取磁盘文件中持久化的缓存值，如果文件未开启「保存前自动重算」，缓存值为空就会返回NULL。
• ACE驱动默认仅扫描查询区域前8行数据判断列数据类型，如果前8行空白占比更高，会直接判定列类型为Null，后续有值的单元格也会被转换为NULL返回。

方案1：批量修改源文件配置（无需改代码） #

如果可以操作所有待查询的工作簿，直接开启自动重算缓存即可：

1. 打开目标工作簿，点击「文件>选项>公式」
2. 勾选「计算选项」分组下的「保存工作簿前重新计算」
3. 保存文件后关闭，用原代码即可正常读取数值
   该方案无需修改查询逻辑，读取速度和你测试的3.5秒一致。

方案2：修改代码适配无缓存文件 #

如果没法修改源文件配置，先修正你现有代码的两处错误，再调整连接和查询逻辑：

1. 原连接字符串中fileName变量被包裹在双引号内，属于语法错误，未正确传入文件路径
2. 遍历空值的循环会把记录集指针移到末尾，后续CopyFromRecordset无法输出任何数据

修正后代码 #

Sub ADOexample()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fileName As String
    ' 替换为你的实际文件路径
    fileName = "C:\你的文件路径\目标文件.xlsx"
    
    CommandText = "Select CStr(F1) AS 列值 From [BV_284700$y40:y300]"
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
            fileName & _
            ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO"";"
                      
    conn.Open ConnectionString
    Set rs = conn.Execute(CommandText)
    
    ThisWorkbook.Worksheets(1).Cells.ClearContents
    ThisWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rs
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

额外优化（解决类型判定问题） #

如果前8行空白较多导致类型判定错误，修改注册表调整驱动扫描行数：

1. 按下Win+R输入regedit打开注册表
2. 定位到对应路径（以Office 2016/2019/365为例，版本号16.0，低版本替换为对应版本号）：
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
3. 找到TypeGuessRows项，将值从默认的8改为0，驱动会扫描全表判断列类型。

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