如何通过Word VBA的ADODB连接读取Excel中B列有效行数数据

我懂你现在的处境：在Word VBA编辑器里写代码，从.docm文件启动，靠ADODB连接到Excel客户数据库（工作表名叫data），目的是把真实有效的客户数据读进动态数组，用来后续搜索和自动填充Word文档。但现在卡在了Excel的结构上——A列手动填了7万行索引，可从B列开始的真实数据只有182行，而且B、C列在最后一行有效数据前完全没有空白单元格，这个有效行数还会随着新增客户数据变化。你不想打开Excel，就想通过ADODB拿到仅包含有效数据的记录集，用它的行数来ReDim动态数组。

先看你现有代码的问题：原来的SELECT * From [data$]会把A列的7万行空数据（除了索引）都读进来，导致RecordCount返回7万，完全不是你要的真实有效行数。

解决方案：优化SQL查询过滤无效行 #

核心思路是在SQL查询里过滤掉B列（或C列，因为你说B、C列有效行前无空白）为空的行，只保留有真实数据的记录。修改后的代码如下：

Option Explicit
Private Sub UseADOSelect()
 Dim connection As New ADODB.connection
 Dim recSet As New ADODB.Recordset
 Dim wordDoc As Word.Document
 Dim strPESELfromWord As String
 Dim strQuery As String
 Dim arraySize As Integer
 Dim strSexDigit As String
 Dim strArray() As Variant ' 改用Variant适配多类型数据，按需调整
 Dim i As Integer, j As Integer

 Set wordDoc = Word.ActiveDocument
 strPESELfromWord = Trim(Selection.Text)
 Word.Application.Visible = True
 strSexDigit = Mid(strPESELfromWord, 10, 1)
 
 ' 保持原连接字符串不变
 connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=X:\Roesler\Excel\FW 1\custdb.xlsm;" & _
 "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1;"""

 ' 关键修改：过滤B列为空的行，仅保留有效数据
 ' 若有效数据起始列是C列，把[B]改成[C]即可
 strQuery = "SELECT * From [data$] WHERE NOT ISNULL([B])"

 ' 指定游标类型为adOpenStatic，确保RecordCount能准确返回记录数
 recSet.Open strQuery, connection, adOpenStatic, adLockReadOnly

 Debug.Print "有效记录行数 = " & recSet.RecordCount
 arraySize = recSet.RecordCount

 ' 仅当有有效数据时才初始化数组
 If arraySize > 0 Then
    ' 二维数组存储所有列的有效数据，适配后续填充需求
    ReDim strArray(1 To arraySize, 1 To recSet.Fields.Count)
    
    recSet.MoveFirst
    For i = 1 To arraySize
        ' 遍历每一列填充数组
        For j = 1 To recSet.Fields.Count
            strArray(i, j) = recSet.Fields(j - 1).Value
        Next j
        recSet.MoveNext
    Next i
 End If

 ' 在这里继续你的搜索、Word文档填充逻辑...

 ' 务必清理资源，避免内存泄漏
 recSet.Close
 connection.Close
 Set recSet = Nothing
 Set connection = Nothing
End Sub

关键修改说明 #

• SQL查询优化：用WHERE NOT ISNULL([B])过滤掉B列为空的行，这样记录集里就只有真实的有效数据行，RecordCount会准确返回实际的有效行数（比如182）。如果你的有效数据起始列是C列，直接把[B]改成[C]就行。
• 记录集游标设置：指定adOpenStatic和adLockReadOnly，确保RecordCount能正确返回记录数（部分游标类型下RecordCount会返回-1，无法获取真实行数）。
• 数组调整：把原来的一维数组改成二维数组，方便存储所有列的有效数据，更贴合后续填充Word文档多字段的需求。
• 资源清理：最后关闭记录集和连接，释放对象，避免长时间占用内存。

特殊情况处理 #

如果你的Excel表第一行不是表头（也就是连接字符串里HDR=NO），那SQL查询里的列名要改用[F2]（代表B列），对应的查询语句改成：

SELECT * From [data$] WHERE NOT ISNULL([F2])

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