ado+sql+excel07的数据源是否要求65536行以内？

minjiwei

各位好，
数据源文件为xlsb格式，行数超出65536行，使用ado连接，跳出错误“外部表不是预期的格式”，如果把数据减少到65536行以内，则不会跳出错误。请问，是什么原因？如何规避？谢谢！

ctp_119

你是用这个打开连接吗？
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.FullName
还是用这个？
objcn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source= " & ThisWorkbook.FullName
用上面那条应该没有限制。最好上传附件看看。

minjiwei

很不幸，我用的是
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.FullName
这个是怎么回事啊？

zhaogang1960

原帖由 minjiwei 于 2011-6-7 18:17 发表
很不幸，我用的是
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.FullName
这个是怎么回事啊？

自连接不能突破65536行限制
把要连接的工作表另存为一个新的xlsx文件后再连接就可以了，如：
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes';Data Source=" & ThisWorkbook.Path & "\123.xlsx"

补充内容 (2015-2-9 17:06):
2007后ADO存在一个Bug，连接工作簿处于打开状态，工作表如果指定区域，则只能查询65536行以内的数据，解决办法：
一、去掉区域，[明细$a1:p]改为[明细$]
或者
二、继续坚持指定区域([明细$a1:p])，放弃自连接，把数

linpansheng

这个是微软的一个BUG
使用整个表就不会出现这个问题了。
猜测楼主是使用 [表$A1:D65536]这种形式，如果是，就是微软的那个bug了

zhaogang1960

原帖由 linpansheng 于 2011-6-7 21:15 发表
这个是微软的一个BUG
使用整个表就不会出现这个问题了。
猜测楼主是使用 [表$A1:D65536]这种形式，如果是，就是微软的那个bug了

刚刚测试了一下，在2007sp3或2010中都已经没有这个限制了，下面附件中占用了70000行：
Sub Macro1()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
    SQL = "select * from [Sheet2$]"
    Range("a2").CurrentRegion.Offset(1).ClearContents
    Range("a2").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub
工作簿1.rar (217.02 KB, 下载次数: 101)

2011-6-7 21:51 上传

点击文件名下载附件

补充内容 (2015-2-9 17:04):
2007后ADO存在一个Bug，连接工作簿处于打开状态，工作表如果指定区域，则只能查询65536行以内的数据，解决办法：
一、去掉区域，[明细$a1:p]改为[明细$]
或者
二、继续坚持指定区域([明细$a1:p])，放弃自连接，把数

minjiwei

这是我的代码，使用left outer join和子查询，结果就是不能跑大于65536行的数据。环境是2007sp3

Private Sub connect2databaseGCWTD(ByVal Database As String)
'Sub connect2databaseGCWTD()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, sql1 As String
Dim i As Long

'MsgBox Replace(Sheet5.Range("B3"), "W", "", 1, 1)

cn.Open " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Database & ";Extended Properties='Excel 12.0;IMEX=1;HDR=YES';Persist Security Info=True;"
sql1 = "select Inside_ISR_Team as teamName,sum(Rev_Svc) as WTDSum_Rev_Svc,sum(Mgn_Svc) as WTDsum_Mgn_Svc from [Sheet1$] where Fiscal_QuarterWeek=" & CLng(Replace(Sheet5.Range("B3"), "W", "", 1, 1)) & " AND [Region]='" & Sheet5.Range("b2").Value & "'and Inside_ISR_Team in (select [Team] from [" & ThisWorkbook.FullName & "].[Allteam$]) group by Inside_ISR_Team"
sql = "select team,location,WTDSum_Rev_Svc,WTDsum_Mgn_Svc,SUBLOCATION from [" & ThisWorkbook.FullName & "].[Allteam$] a left outer join (" & sql1 & ") b on b.[teamName]=a.[Team];"


rs.Open sql, cn

Sheet6.Cells.ClearContents
Sheet6.Range("a2").CopyFromRecordset rs


For i = 0 To rs.Fields.Count - 1

Sheet6.Cells(1, i + 1) = rs.Fields(i).Name
Next


rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing


End Sub

linpansheng

[SQL in Excel] 为什么不能支持大于65536行的ADO连接 http://www.exceltip.net/thread-14081-1-1-232.html
请赵老师看看这个帖子，多个会员测试是微软的一个BUG

zhaogang1960

原帖由 linpansheng 于 2011-6-7 22:16 发表
[SQL in Excel] 为什么不能支持大于65536行的ADO连接 http://www.exceltip.net/thread-14081-1-1-232.html
请赵老师看看这个帖子，多个会员测试是微软的一个BUG

我没有资格查看这个论坛的附件
记得开始2007自连接，确实不支持超过65536行的数据源

补充内容 (2015-2-9 17:05):
2007后ADO存在一个Bug，连接工作簿处于打开状态，工作表如果指定区域，则只能查询65536行以内的数据，解决办法：
一、去掉区域，[明细$a1:p]改为[明细$]
或者
二、继续坚持指定区域([明细$a1:p])，放弃自连接，把数

zhaogang1960

原帖由 minjiwei 于 2011-6-7 22:12 发表
这是我的代码，使用left outer join和子查询，结果就是不能跑大于65536行的数据。环境是2007sp3

Private Sub connect2databaseGCWTD(ByVal Database As String)
'Sub connect2databaseGCWTD()
Dim cn As New AD ...

Database是什么？是其他工作簿(.xlsx)吧？如果是，from [" & ThisWorkbook.FullName & "].[Allteam$]好像不完整，改成：
from [Excel 12.0;Database=" & ThisWorkbook.FullName & "].[Allteam$]试试看