vba 针对 Excel 电子表格的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20021767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query against Excel Spreadsheet
提问by Solution Seeker
I am facing a problem while firing SQL Query against Excel SpreadSheet. I have an Excel-2007 spreadsheet having around 1 lac rows with two columns. Column-1 (cid) & Column-2 (company). I would like to fetch all the rows from sheet(tab1) where company="spider". I am using below code. it's giving me results but only from first 1400 rows. If I do have company="spider" after 1400 rows it's not able to fetch it through below code. Need help for this.
我在针对 Excel 电子表格触发 SQL 查询时遇到问题。我有一个 Excel-2007 电子表格，其中大约有 1 个 lac 行和两列。第 1 列（cid）和第 2 列（公司）。我想从公司 =“蜘蛛”的工作表（tab1）中获取所有行。我正在使用下面的代码。它给了我结果，但只来自前 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
Please provide your valuable comments/solutions to fix this. Let me know incase you need any additional details.
请提供您宝贵的意见/解决方案来解决这个问题。如果您需要任何其他详细信息，请告诉我。
Thanks.
谢谢。
回答by jacouh
This worked for me on Excel 2007:
这在 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
!!!Be careful, results are copied to Range("D:E"), erasing the old data of those 2 columns.
!!!小心，结果被复制到 Range("D:E") ，删除那两列的旧数据。
As shown, "spider" is found on record 2 and record 2838, ie > row 1400.
如图所示，在记录 2 和记录 2838（即 > 行 1400）上找到了“蜘蛛”。
Extended Properties="Excel 12.0 Macro;HDR=YES" defines an Excel Document with Macro, ie a MyDoc.xlsm file, HDR=Yes indicates that the first row is the header, ie, column names. For other format of Excel documents, please see reference.
Extended Properties="Excel 12.0 Macro;HDR=YES" 定义了一个带有宏的Excel文档，即MyDoc.xlsm文件，HDR=Yes表示第一行是表头，即列名。其他格式的 Excel 文档请参见参考。
Or you can traverse the recordset:
或者你可以遍历记录集：
'
'...
'
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
'
'...
'
