vba 在 Excel 2013 中查询超过 65536 行错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24472183/
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
Querying more than 65536 rows error in Excel 2013
提问by Nicholas Clarke
I am trying to query a spreadsheet using VBA and am running up against a seeming hard limit of 65536 rows (though I am running Excel 2013).
我正在尝试使用 VBA 查询电子表格，并且遇到了 65536 行的看似硬限制（尽管我正在运行 Excel 2013）。
When trying to select all rows where the number of rows is greater than 65536 I get the following error message:
尝试选择行数大于 65536 的所有行时，我收到以下错误消息：
runtime error '-2147217865 (80040e37)':
运行时错误“-2147217865 (80040e37)”：
The Microsoft Access database engine could not find the object 'Sheet1$A1:A65537'.....
Microsoft Access 数据库引擎找不到对象“Sheet1$A1:A65537”.....
My code:
我的代码：
Option Explicit
Sub ExcelQuery()
Dim conXLS As ADODB.Connection
Dim rsXLS As ADODB.Recordset
Dim strPath As String
Dim strSQL As String
Dim i As Integer
'Get the full directory + file name location of the current workbook (so it can query itself)'
strPath = Application.ActiveWorkbook.FullName
'create the ADO connection to the excel file'
Set conXLS = New ADODB.Connection
With conXLS
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;Readonly=False"""
End With
conXLS.Open
strSQL = "" & _
"SELECT " & _
"* " & _
"FROM " & _
"[Sheet1$A1:A65537] "
'create ADO recordset to hold contents of target sheet.'
Set rsXLS = New ADODB.Recordset
With rsXLS
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
'using SQL return contents of the target sheet'
rsXLS.Open strSQL, conXLS
'disconnect the active connection'
Set rsXLS.ActiveConnection = Nothing
'Return results to excel'
Sheets("Sheet2").Cells(1, 1).CopyFromRecordset rsXLS
Set rsXLS = Nothing
'destroy the connection object'
conXLS.Close
Set conXLS = Nothing
End Sub
I also tried the connection string:
我也试过连接字符串：
With conXLS
.Provider = "Microsoft.Jet.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPath & ";" & _
"Extended Properties=Excel 12.0;"
.Open
I have set references to "Microsoft ActiveX Data Objects 6.0 Library" and "OLE Automation".
我已经设置了对“Microsoft ActiveX Data Objects 6.0 Library”和“OLE Automation”的引用。
Interestingly, there seems to be no problem when using MSQuery.
有趣的是，使用 MSQuery 时似乎没有问题。
回答by Alexander Bell
Older Excel versions (prior to 2007) indeed have a limit of some 65k+ rows per worksheet. Run your code and reference any object Lib starting w/Excel 2007 and up (max 1,048,576 rows per worksheet, Lib version correspondingly 12.x and up). Pertinent to your case, try to use a notation [Sheet1$A:A]instead of [Sheet1$A1:A65537]Rgds,
较旧的 Excel 版本（2007 年之前）确实每个工作表的行数限制为 65k+ 行。运行您的代码并引用从 Excel 2007 及更高版本开始的任何对象 Lib（每个工作表最多 1,048,576 行，Lib 版本对应于 12.x 及更高版本）。与您的情况相关，尝试使用符号[Sheet1$A:A]而不是[Sheet1$A1:A65537]Rgds，
回答by ??c Thanh Nguy?n
I encountered this problem a long time ago. What I did was writing my query like this:
我很久以前就遇到过这个问题。我所做的是像这样编写我的查询：
select Data from [Temp$]
Where Tempwas my sheet name, the content of cell A1was "Data" and the content of A2:A80000were ids
其中Temp是我的工作表名称，单元格A1的内容是“数据”，A2:A80000的内容是 ID
It worked.
有效。
