Excel VBA查询外部Excel数据时遭遇BOF/EOF错误求助

我正在开发一款公司小型库存管理的Excel VBA应用，用于按货架索引已打包的箱子。Database.xlsx文件包含两个工作表：

• Pack表：存储打包箱的重量、尺寸等信息
• Trans表：记录箱子对应的货架位置

示例数据 #

Pack表：

Trans表：

错误现象 #

每次执行查询时都会报错：bof or eof is true or the current record has been deleted，调试发现BOF为False、EOF为True、RecordCount=-1，说明查询未返回任何记录。

尝试的代码 #

Private Function SearchSalesID(SalesIDLine As String) As String()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object
    Dim result() As String
    Dim index As Integer
    
    'On Error GoTo ErrorHandler

    Dim filePath As String
    filePath = DataBase_Path & "\" & DataBase_Name

    Set conn = CreateObject("ADODB.Connection")

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & filePath & ";" & _
              "Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=1"";"

    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn

    cmd.CommandText = "SELECT Loc_ID FROM [Trans$] AS T INNER JOIN [Pack$] AS P ON T.Pack_ID = P.Pack_ID WHERE P.SalesIDLine LIKE '" & SalesIDLine & "%'"
    
    Set rs = cmd.Execute

    result = rs.GetRows
    
    rs.Close
    conn.Close
    
    SearchSalesID = Application.Transpose(result)
    
    Exit Function

ErrorHandler:
    If Not conn Is Nothing Then
        conn.Close
    End If
    If Not rs Is Nothing Then
        rs.Close
    End If
    SearchSalesID = Array() 
End Function

解决方案 #

1. 先判断记录集是否为空

在调用rs.GetRows前，必须检查是否有返回记录，避免空记录集操作触发错误：

Set rs = cmd.Execute

If Not rs.EOF Then
    result = rs.GetRows
    SearchSalesID = Application.Transpose(result)
Else
    SearchSalesID = Array() ' 返回空数组
End If

2. 使用参数化查询替代字符串拼接

直接拼接SalesIDLine可能因特殊字符（如单引号）导致SQL语法错误，改用参数化查询更安全可靠：

cmd.CommandText = "SELECT Loc_ID FROM [Trans$] AS T INNER JOIN [Pack$] AS P ON T.Pack_ID = P.Pack_ID WHERE P.SalesIDLine LIKE ?"
' 手动定义常量（若未引用ADO库）
Const adVarChar As Integer = 200
Const adParamInput As Integer = 1
cmd.Parameters.Append cmd.CreateParameter("SalesID", adVarChar, adParamInput, 255, SalesIDLine & "%")

3. 验证连接与表名正确性

• 确认DataBase_Path和DataBase_Name拼接的文件路径完全正确，可通过Debug.Print filePath输出检查
• 确保工作表名称准确，若表名包含空格或特殊字符，需用方引号包裹，例如[Trans Data$]

4. 调整连接字符串的IMEX设置

如果Pack表的SalesIDLine列存在混合数据类型（文本+数字），IMEX=1可能导致数据读取异常，可尝试修改为IMEX=0，或确保列数据类型统一为文本。

修改后的完整代码 #

Private Function SearchSalesID(SalesIDLine As String) As String()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object
    Dim result() As String
    
    On Error GoTo ErrorHandler

    Dim filePath As String
    filePath = DataBase_Path & "\" & DataBase_Name

    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & filePath & ";" & _
              "Extended Properties=""Excel 12.0 XML;HDR=YES;IMEX=0"";"

    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = conn

    ' 使用参数化查询
    cmd.CommandText = "SELECT Loc_ID FROM [Trans$] AS T INNER JOIN [Pack$] AS P ON T.Pack_ID = P.Pack_ID WHERE P.SalesIDLine LIKE ?"
    ' 手动定义常量（若未引用ADO库）
    Const adVarChar As Integer = 200
    Const adParamInput As Integer = 1
    cmd.Parameters.Append cmd.CreateParameter("SalesID", adVarChar, adParamInput, 255, SalesIDLine & "%")
    
    Set rs = cmd.Execute

    If Not rs.EOF Then
        result = rs.GetRows
        SearchSalesID = Application.Transpose(result)
    Else
        SearchSalesID = Array()
    End If

Cleanup:
    If Not rs Is Nothing Then rs.Close
    If Not conn Is Nothing Then conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
    
    Exit Function

ErrorHandler:
    MsgBox "查询错误：" & Err.Description, vbCritical
    SearchSalesID = Array()
    Resume Cleanup
End Function

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