Excel 2007 - ADO - More than 65536 rows - Problem ?

M

MichDenis

Good morning every one,

I try to extract data from a column in an 2007 excel worksheet
with ADO. Up to 65536 rows, i have no problem but if i go
over, the code blocks here : Rst.Open Requete....
Does anyone know the reasons why this sub blocks ?

Thank for your collaboration and your time.

'--------------------------------------------------
Sub Test()

Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Dim Champ As String
Dim Rg As Range

With Sheet1
'More than 65536 rows -> problem ?
Set Rg = .Range("B1:B" & .Range("B200000").End(xlUp).Row)
'Ok if less or equal to 65536 rows -> no problem
' Set Rg = .Range("B1:B65536")
End With

Champ = "Market"

'If over 65536 rows, both syntaxes don't work
'A)
Requete = "SELECT " & Champ & " From [" & Rg.Parent.Name & "$" & _
Rg.Address(0, 0) & "]"
'B)
'Requete = "SELECT * From Liste "

Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic, adCmdText
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
'--------------------------------------------------
 
T

Tom Hutchins

If your workbook (with the ADO code) was created in an Excel version before
2007, then only 65536 rows will be available. That might be your problem.

From a post by Bob Umlas:
If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas

Hope this helps,

Hutch
 
G

gimme_this_gimme_that

Hi Mitch,

I re-read your question and see I didn't read it closely the first
time through.

Confirm that you're using the most recent version of the M$ ActiveX
Data Objects Library.

My version is 2.8 (but 2.4-2.7 are on my system as well).
 
M

MichDenis

Thanks also to you and Tom for your help.
I finally found an acceptable solution.


<[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi Mitch,

I re-read your question and see I didn't read it closely the first
time through.

Confirm that you're using the most recent version of the M$ ActiveX
Data Objects Library.

My version is 2.8 (but 2.4-2.7 are on my system as well).
 
T

Thomas Huang

Dear MichDenis, I also met similar situation. Could you pls. tell me what acceptable solution you have found? Thank you very much!
Good morning every one,

I try to extract data from a column in an 2007 excel worksheet
with ADO. Up to 65536 rows, i have no problem but if i go
over, the code blocks here : Rst.Open Requete....
Does anyone know the reasons why this sub blocks ?

Thank for your collaboration and your time.

'--------------------------------------------------
Sub Test()

Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Dim Champ As String
Dim Rg As Range

With Sheet1
'More than 65536 rows -> problem ?
Set Rg = .Range("B1:B" & .Range("B200000").End(xlUp).Row)
'Ok if less or equal to 65536 rows -> no problem
' Set Rg = .Range("B1:B65536")
End With

Champ = "Market"

'If over 65536 rows, both syntaxes don't work
'A)
Requete = "SELECT " & Champ & " From [" & Rg.Parent.Name & "$" & _
Rg.Address(0, 0) & "]"
'B)
'Requete = "SELECT * From Liste "

Cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic, adCmdText
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
'--------------------------------------------------
On Wednesday, March 04, 2009 1:16 PM TomHutchin wrote:
If your workbook (with the ADO code) was created in an Excel version before
2007, then only 65536 rows will be available. That might be your problem.

From a post by Bob Umlas:
If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas

Hope this helps,

Hutch

"MichDenis" wrote:
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO syntax problem 2
Pivot Table Type Mismatch when More than 65536 rows 1
create blank rows 1
ADO retrieval from close workbook-data type issue 2
ADO - Access to Excel 5
Autofill 3
Problem in Excel 2007 when Range beyond 65536 in SELECT statement 2
Excel 2003 vs 2007 4

Top