针对 Excel 电子表格的 SQL 查询

Question

我在针对 Excel 电子表格执行 SQL 查询时遇到了问题。我有一个包含约 1 万行和两列的 Excel 2007 电子表格。第一列是 cid，第二列是 company。我想从名为“tab1”的工作表中获取所有 company="spider" 的行。我使用以下代码。它给出了结果，但只能返回前 1400 行。如果在 1400 行之后有 company="spider"，以下代码将无法获取它。请求帮助。

Sub main()

On Error GoTo ErrHandler

Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ThisWorkbook.FullName & "; Extended Properties=Excel 8.0"
    .Open
End With

Set ObjRes = cn.Execute("Select cid,company from [tab1$] where [company]= 'spider'")

result.Range("A:B").Clear
result.Range("A1").CopyFromRecordset ObjRes

cn.Close
Set cn = Nothing
Set ObjRes = Nothing

   Exit Sub
ErrHandler:
  cn.Close
  MsgBox "dataerror"

   End Sub

请提供您宝贵的意见/解决方案来修复这个问题。如果您需要任何额外的细节，请告诉我。谢谢。

- Solution Seeker

---

你的数据库文件是哪个Excel版本？换句话说，ThisWorkbook.FullName返回的文件扩展名是什么？ - Kazimierz Jawor

@kazjaw - 这个文件是Excel-2007格式的。 - Solution Seeker

请根据此信息尝试更改您的提供程序和连接字符串。 - Kazimierz Jawor

@KazJaw - 在使用您提供的连接字符串时，我发现了“找不到可安装的ISAM”错误消息。您有什么想法出了什么问题吗？如果可能的话，您能否修改上面由我提供的代码中的连接字符串。这将真正帮助我。谢谢。 - Solution Seeker

Answer 1

这对我在Excel 2007上有效：

Sub sofMain20021767()

  Dim cn, ObjRes
  Dim result

  On Error GoTo ErrHandler

  Set cn = CreateObject("ADODB.Connection")
  With cn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" _
      & "; Data Source=" & ThisWorkbook.FullName _
      & "; Extended Properties=""Excel 12.0 Macro;HDR=YES"""
    .Open
  End With

'
  Set ObjRes = cn.Execute("SELECT cid,company FROM [tab1$] WHERE [company]= 'spider'")
'

' result.Range("A:B").Clear
' result.Range("A1").CopyFromRecordset ObjRes

  Range("D:E").Clear
  Range("D1").CopyFromRecordset ObjRes
  '
  cn.Close
  Set cn = Nothing
  Set ObjRes = Nothing

  Exit Sub
ErrHandler:
  MsgBox Err.Description
  Set cn = Nothing

End Sub

请注意，结果将被复制到“D：E”范围，覆盖这两列的旧数据。

如图所示，“spider”在记录2和记录2838中被找到，即行号大于1400。

Extended Properties =“Excel 12.0 Macro; HDR = YES”定义了一个带有宏的Excel文档，即MyDoc.xlsm文件，HDR = Yes表示第一行是标头，即列名。有关其他格式的Excel文档，请参见参考。

或者您可以遍历记录集：

  '
  '...
  '
  Range("D:E").Clear
  '
  'Range("D1").CopyFromRecordset ObjRes
  '

  '
  ' now we traverse the recordset:
  '
  Dim i
  i = 1
  '
  Do While (Not ObjRes.EOF)
    Range("D" & i).Value = ObjRes(0).Value
    Range("E" & i).Value = ObjRes(1).Value
    i = i + 1
    ObjRes.MoveNext
  Loop
  '
  '...
  '

- jacouh

---

非常感谢。以上的代码成功了！只有一个快速问题。在记录集方面，是否存在最大可包含的记录数限制？因为当我尝试在表格中搜索一个出现超过1K次的字符串时，将记录集结果复制到表格中时出现错误。有任何想法吗？ - Solution Seeker

仅受 Excel 行数限制，对于我们的实际使用，Recordset 是无限制的。对于错误，您可以尝试使用 do while 循环来复制记录/记录。 - jacouh

你有遍历记录集中每个记录的示例代码吗？我尝试了一下，但是没有结果。 - Solution Seeker