在Excel 2013中查询超过65536行错误 [英] Querying more than 65536 rows error in Excel 2013
问题描述
我正在尝试使用VBA查询电子表格,并且遇到了65536行的看似硬限制(尽管我正在运行Excel 2013).
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).
当尝试选择行数大于65536的所有行时,出现以下错误消息:
When trying to select all rows where the number of rows is greater than 65536 I get the following error message:
运行时错误'-2147217865(80040e37)':
runtime error '-2147217865 (80040e37)':
Microsoft Access数据库引擎找不到对象'Sheet1 $ A1:A65537'.....
The Microsoft Access database engine could not find the object 'Sheet1$A1:A65537'.....
我的代码:
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
我已经设置了对"Microsoft ActiveX数据对象6.0库"和"OLE自动化"的引用.
I have set references to "Microsoft ActiveX Data Objects 6.0 Library" and "OLE Automation".
有趣的是,使用MSQuery似乎没有问题.
Interestingly, there seems to be no problem when using MSQuery.
推荐答案
Excel的较早版本(2007年之前)确实每个工作表的行数限制为65k +.运行您的代码并引用从Excel 2007及更高版本开始的任何对象Lib(每个工作表最多1,048,576行,相应的Lib版本为12.x及更高版本).与您的情况有关,请尝试使用符号[Sheet1$A:A]
代替[Sheet1$A1:A65537]
Rgds
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 2013中查询超过65536行错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!