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

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
'--------------------------------------------------

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

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:

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
'--------------------------------------------------

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

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).

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

I have code that use the new Microsoft.ACE.OLEDB.12.0
http://www.rondebruin.nl/ado.htm

Test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




wrote in message ...
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).

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

I tested your code and every thing is perfect. It's worked well.
Thank a lot for your help.


"Ron de Bruin" a écrit dans le message de groupe de discussion :
...
I have code that use the new Microsoft.ACE.OLEDB.12.0
http://www.rondebruin.nl/ado.htm

Test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




wrote in message
...
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).

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

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


a écrit dans le message de groupe de discussion :
...
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).

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

Thanks for the feedback

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"MichDenis" wrote in message ...
I tested your code and every thing is perfect. It's worked well.
Thank a lot for your help.


"Ron de Bruin" a écrit dans le message de groupe de discussion :
...
I have code that use the new Microsoft.ACE.OLEDB.12.0
http://www.rondebruin.nl/ado.htm

Test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




wrote in message
...
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).

Thanks also to you and Tom for your help.
 

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

On Wednesday, March 04, 2009 7:20 AM MichDenis wrote:

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:


On Wednesday, March 04, 2009 2:51 PM Ron de Bruin wrote:

I have code that use the new Microsoft.ACE.OLEDB.12.0
http://www.rondebruin.nl/ado.htm

Test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


On Wednesday, March 04, 2009 4:07 PM MichDenis wrote:

I tested your code and every thing is perfect. It's worked well.
Thank a lot for your help.


"Ron de Bruin" a écrit dans le message de groupe de discussion :
I have code that use the new Microsoft.ACE.OLEDB.12.0
http://www.rondebruin.nl/ado.htm

Test it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




wrote in message
...


On Wednesday, March 04, 2009 4:22 PM MichDenis wrote:

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


a écrit dans le message de groupe de discussion :
...
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).


On Wednesday, March 04, 2009 4:27 PM Ron de Bruin wrote:

Thanks for the feedback

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


On Friday, March 06, 2009 10:06 PM gimme_this_gimme_tha wrote:

Check the data for:

1. Single quotes
2. Linefeeds
3. Unusually long strings

To be on the safe side, copy the first 50K rows into the WorkSheet a
bunch of times to confirm that the problem *really* has to do with the
number of rows.

If all else fails - move the data out of Excel and into a database.
Organize the data like a normal person.


On Friday, March 06, 2009 10:06 PM gimme_this_gimme_tha wrote:

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).