Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get my Excel 2007 worksheet to go beyond 65536 rows? | Excel Worksheet Functions | |||
xlsx 2007 display only 65536 rows | Excel Discussion (Misc queries) | |||
Why Excel 2007 still have the 65536 raw limit? | Excel Discussion (Misc queries) | |||
Excel 2007 65536 rows-convert | Excel Discussion (Misc queries) | |||
Can I put more than 65536 rows in one Excel sheet? | Excel Worksheet Functions |