在VBA中打开记录集时出错 [英] Error opening recordset in VBA

问题描述

我正在使用ADO开发Excel VBA应用程序。我正在尝试打开一个记录集，但如果我的表有超过65536行，则open方法会失败。我知道这个数字是旧的Excel行限制，但我使用2016版本和正确的连接
字符串。也许这是我的项目中引用的一些库，但我找不到哪一个。


如果我能得到一些帮助，我将非常感激。

I'm developing an Excel VBA application using ADO. I'm trying to open a recordset, but the open method fails if my table has more than 65536 lines. I know this number is the old Excel line limit, but I'm using the 2016 version and the correct connection strings. Perhaps it's some library referenced in my project, but I can't find out which one.
I would appreciate very much if I could get some help with this.

错误：运行时错误'-2147217865（80040e37）'： Microsoft Jet数据库引擎找不到对象'我的工作表$ A8：AD70000'。确保对象存在，并正确拼写其名称和路径名。

The error: Runtime error '-2147217865 (80040e37)': The Microsoft Jet database engine could not find the object 'My sheet$A8:AD70000'. Make sure the object exists and that you spell its name and the path name correctly.

我的代码：

Sub MySub()

Dim conn    As ADODB.Connection
Dim rs      As ADODB.Recordset
Dim str     As String

Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection

str = "SELECT * FROM [My sheet$A8:AD70000];"

''Opening connection with the workbook
conn.ConnectionTimeout = 90
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & Application.ThisWorkbook.FullName & ";" & _
                        "Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1"";"
conn.Open

rs.Open str, conn, adOpenForwardOnly, adLockReadOnly, adCmdText

rs.Close
conn.Close

End Sub

推荐答案

你好，

发现：  https://chandoo.org/forum/threads/excel-recordset-only-returns-65536-rows-if-you-try-从一个范围来拉取数据.12492 /

found this: https://chandoo.org/forum/threads/excel-recordset-only-returns-65536-rows-if-you-try-to-pull-data-from-a-range.12492/

同样在互联网上的其他帖子中，我似乎不可能。

Also in other posts on the internet, I does not seem possible.

这篇关于在VBA中打开记录集时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！