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