Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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
'--------------------------------------------------

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,069
Default 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
'--------------------------------------------------


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 236
Default 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).
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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).

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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).




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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).

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default 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).



  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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).




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get my Excel 2007 worksheet to go beyond 65536 rows? Alan Excel Worksheet Functions 1 May 4th 09 04:03 PM
xlsx 2007 display only 65536 rows oldLearner57 Excel Discussion (Misc queries) 3 July 14th 08 06:08 PM
Why Excel 2007 still have the 65536 raw limit? [email protected] Excel Discussion (Misc queries) 5 November 30th 07 03:17 PM
Excel 2007 65536 rows-convert bufbec Excel Discussion (Misc queries) 1 November 7th 07 04:02 PM
Can I put more than 65536 rows in one Excel sheet? Office user Excel Worksheet Functions 2 September 26th 05 11:08 PM


All times are GMT +1. The time now is 09:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"