Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système... le
plus surprenant c'est que la requête bloque dès que je lui demande d'aller
au delà de 65536 lignes... ce qui est la limite d'excel 2003... Lorsque le
temps s'y prêtera, je ferai quelques tests supplémentaires et utilisant en
outre le pilote ODBC plutôt que la version "Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied sur
le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est une
limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : #s3#CJzmJHA.1252@TK2MSFTNGP03.phx.gbl...
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Un fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : OSWVhTsmJHA.5980@TK2MSFTNGP06.phx.gbl...
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
Daniel
J'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : uOvQ76pmJHA.1184@TK2MSFTNGP04.phx.gbl...
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
Daniel
Et bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système... le
plus surprenant c'est que la requête bloque dès que je lui demande d'aller
au delà de 65536 lignes... ce qui est la limite d'excel 2003... Lorsque le
temps s'y prêtera, je ferai quelques tests supplémentaires et utilisant en
outre le pilote ODBC plutôt que la version "Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" <i@v> a écrit dans le message de groupe de discussion :
OXQ91bmmJHA.3876@TK2MSFTNGP02.phx.gbl...
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :
Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied sur
le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est une
limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système... le
plus surprenant c'est que la requête bloque dès que je lui demande d'aller
au delà de 65536 lignes... ce qui est la limite d'excel 2003... Lorsque le
temps s'y prêtera, je ferai quelques tests supplémentaires et utilisant en
outre le pilote ODBC plutôt que la version "Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied sur
le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est une
limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : ujafWy0mJHA.4140@TK2MSFTNGP05.phx.gbl...
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : #s3#CJzmJHA.1252@TK2MSFTNGP03.phx.gbl...
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Un fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : OSWVhTsmJHA.5980@TK2MSFTNGP06.phx.gbl...
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
Daniel
J'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : uOvQ76pmJHA.1184@TK2MSFTNGP04.phx.gbl...
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
Daniel
Et bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" <i@v> a écrit dans le message de groupe de discussion :
OXQ91bmmJHA.3876@TK2MSFTNGP02.phx.gbl...
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :
Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : ujafWy0mJHA.4140@TK2MSFTNGP05.phx.gbl...
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : #s3#CJzmJHA.1252@TK2MSFTNGP03.phx.gbl...
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Un fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : OSWVhTsmJHA.5980@TK2MSFTNGP06.phx.gbl...
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
Daniel
J'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : uOvQ76pmJHA.1184@TK2MSFTNGP04.phx.gbl...
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
Daniel
Et bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" <i@v> a écrit dans le message de groupe de discussion :
OXQ91bmmJHA.3876@TK2MSFTNGP02.phx.gbl...
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :
Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB programming
comminity then you will see a query on this subject at regular intervals. I
always answer this particular query as I have sympathy for the developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors work
properly it all became clear. You will not get a correct recordcount if the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it is
very appropriate). It is readonly but very light on your systems resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go back.
Another feature of this recordset is that ADO doesn't know how far the data
goes. The only way it can get from the first to the last record is by moving
records, one by one, until no more are left. Consequently it can't possibly
know how many there are until its too late. This is the cursor to use if you
want to load a list of records, which represent a list of resources (e.g.
language resource strings in a localization system), as quickly as possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out from
the database tables and worked on in isolation. Changes made by other users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of cursor
you will be loading the entire set of records into memory. For this reason a
server can feel somewhat under seige if you write a multiuser application
and all the users are opening large data sets (so think carefully). As there
are very few large scale database applications being written by developers
this is not so important. Because all the records in the selected data set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of the
primary key values in memory but not the other selected column values. Using
the list of primary keys (the key set) ADO retrieves enough rows from the
table to fill it's record cache which is what your code can look at. Each
time you navigate throughout the recordset a new [cachesize] set of records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that your
recordset is aware of changes to column values which other users make but it
is not aware of new additions (because the set of keys is not reloaded, its
static). As you can imagine using this cursor type involves a lot of return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in memory
so it does not have to return to the database. However as the set of primary
keys is loaded once and is in effect static ADO can easily return how many
records are in the recordset (by counting how many keys there are). Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set of
keys to satisfy the cache. However when the user (or code) navigates through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour connaître les détails sur l'utilisation du pilote
approprié. Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur de la propriété ConnectionString sur «
Microsoft.ACE.OLEDB.12.0 » Si vous vous connectez à des données Microsoft
Office Excel, ajoutez « Excel 12.0 » aux propriétés étendues de la chaîne de
connexion OLEDB. Si vous développez des applications à l'aide d'ODBC pour
vous connecter à des données Microsoft Office Access, définissez la chaîne
de connexion sur « Pilote={Pilote Microsoft Access (*.mdb,
*.accdb)};DBQ=emplacement du fichier mdb/accdb » Si vous développez des
applications à l'aide d'ODBC pour vous connecter à des données Microsoft
Office Excel, définissez la chaîne de connexion sur « Pilote={Pilote
Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" a écrit dans le message de groupe de
discussion :
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
DanielC'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming comminity then you will see a query on this subject at regular
intervals. I always answer this particular query as I have sympathy for the
developer, once I was like they are now; confused. I always thought such a
matter should be simple (how hard could it be?). Once I understood how
cursors work properly it all became clear. You will not get a correct
recordcount if the recordset does not support bookmarks. If
rs.Supports(adBookmark) Then ' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it
is very appropriate). It is readonly but very light on your systems
resources and the fastest to use. As the name suggests you can only move
forwards through it; once you leave a record and move to another you cannot
go back. Another feature of this recordset is that ADO doesn't know how far
the data goes. The only way it can get from the first to the last record is
by moving records, one by one, until no more are left. Consequently it can't
possibly know how many there are until its too late. This is the cursor to
use if you want to load a list of records, which represent a list of
resources (e.g. language resource strings in a localization system), as
quickly as possible. Static - the most commonly used cursor type in my
experience. This is the cursor which clientside recordsets use no matter
what you may have asked for. It works like a snapshot of the database as
the data is copied out from the database tables and worked on in isolation.
Changes made by other users will happen without the recordset you have
opened being aware of it. The data can be read-only or read-write. When you
request this type of cursor you will be loading the entire set of records
into memory. For this reason a server can feel somewhat under seige if you
write a multiuser application and all the users are opening large data sets
(so think carefully). As there are very few large scale database
applications being written by developers this is not so important. Because
all the records in the selected data set are loaded into memory it is
simple for ADO to return the count. Keyset - similar to a static recordset
only much more clever. When a selection of records is made as a recordset
is opened the cursor maintains a list of the primary key values in memory
but not the other selected column values. Using the list of primary keys
(the key set) ADO retrieves enough rows from the table to fill it's record
cache which is what your code can look at. Each time you navigate
throughout the recordset a new [cachesize] set of records is retrieved from
the database using the static set of primary key values initially retrieved
when the recordset was first opened. This means that your recordset is
aware of changes to column values which other users make but it is not aware
of new additions (because the set of keys is not reloaded, its static). As
you can imagine using this cursor type involves a lot of return visits to
the database as the user navigates throughout the recordset so performance
is not as good as the static cursor which makes a copy in memory so it does
not have to return to the database. However as the set of primary keys is
loaded once and is in effect static ADO can easily return how many records
are in the recordset (by counting how many keys there are). Dynamic - the
big daddy of cursors. Be afraid, in fact I should turn a run if I were you.
I cannot remember the last time I the dynamic cursor type. It is definitely
intended for multiuser, multi-access systems which are very involved to
code properly. It is similar to a keyset cursor as it allows modifications
made by other users to show through to the recordset but this comes at a
price (a terrible price). When the recordset is opened ADO retrieves a list
of primary keys and then loads enough rows using the set of keys to satisfy
the cache. However when the user (or code) navigates through the recordset
ADO reloads the set of primary keys again (and again, and again, and
again...) before loading the new rows. You can see that this is very
epensive, even more so that the keyset cursor. Because the set of primary
keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from
one moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir
le résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe
de discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour connaître les détails sur l'utilisation du pilote
approprié. Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur de la propriété ConnectionString sur «
Microsoft.ACE.OLEDB.12.0 » Si vous vous connectez à des données Microsoft
Office Excel, ajoutez « Excel 12.0 » aux propriétés étendues de la chaîne de
connexion OLEDB. Si vous développez des applications à l'aide d'ODBC pour
vous connecter à des données Microsoft Office Access, définissez la chaîne
de connexion sur « Pilote={Pilote Microsoft Access (*.mdb,
*.accdb)};DBQ=emplacement du fichier mdb/accdb » Si vous développez des
applications à l'aide d'ODBC pour vous connecter à des données Microsoft
Office Excel, définissez la chaîne de connexion sur « Pilote={Pilote
Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : uqDIEa1mJHA.824@TK2MSFTNGP05.phx.gbl...
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
Daniel
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming comminity then you will see a query on this subject at regular
intervals. I always answer this particular query as I have sympathy for the
developer, once I was like they are now; confused. I always thought such a
matter should be simple (how hard could it be?). Once I understood how
cursors work properly it all became clear. You will not get a correct
recordcount if the recordset does not support bookmarks. If
rs.Supports(adBookmark) Then ' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it
is very appropriate). It is readonly but very light on your systems
resources and the fastest to use. As the name suggests you can only move
forwards through it; once you leave a record and move to another you cannot
go back. Another feature of this recordset is that ADO doesn't know how far
the data goes. The only way it can get from the first to the last record is
by moving records, one by one, until no more are left. Consequently it can't
possibly know how many there are until its too late. This is the cursor to
use if you want to load a list of records, which represent a list of
resources (e.g. language resource strings in a localization system), as
quickly as possible. Static - the most commonly used cursor type in my
experience. This is the cursor which clientside recordsets use no matter
what you may have asked for. It works like a snapshot of the database as
the data is copied out from the database tables and worked on in isolation.
Changes made by other users will happen without the recordset you have
opened being aware of it. The data can be read-only or read-write. When you
request this type of cursor you will be loading the entire set of records
into memory. For this reason a server can feel somewhat under seige if you
write a multiuser application and all the users are opening large data sets
(so think carefully). As there are very few large scale database
applications being written by developers this is not so important. Because
all the records in the selected data set are loaded into memory it is
simple for ADO to return the count. Keyset - similar to a static recordset
only much more clever. When a selection of records is made as a recordset
is opened the cursor maintains a list of the primary key values in memory
but not the other selected column values. Using the list of primary keys
(the key set) ADO retrieves enough rows from the table to fill it's record
cache which is what your code can look at. Each time you navigate
throughout the recordset a new [cachesize] set of records is retrieved from
the database using the static set of primary key values initially retrieved
when the recordset was first opened. This means that your recordset is
aware of changes to column values which other users make but it is not aware
of new additions (because the set of keys is not reloaded, its static). As
you can imagine using this cursor type involves a lot of return visits to
the database as the user navigates throughout the recordset so performance
is not as good as the static cursor which makes a copy in memory so it does
not have to return to the database. However as the set of primary keys is
loaded once and is in effect static ADO can easily return how many records
are in the recordset (by counting how many keys there are). Dynamic - the
big daddy of cursors. Be afraid, in fact I should turn a run if I were you.
I cannot remember the last time I the dynamic cursor type. It is definitely
intended for multiuser, multi-access systems which are very involved to
code properly. It is similar to a keyset cursor as it allows modifications
made by other users to show through to the recordset but this comes at a
price (a terrible price). When the recordset is opened ADO retrieves a list
of primary keys and then loads enough rows using the set of keys to satisfy
the cache. However when the user (or code) navigates through the recordset
ADO reloads the set of primary keys again (and again, and again, and
again...) before loading the new rows. You can see that this is very
epensive, even more so that the keyset cursor. Because the set of primary
keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from
one moment to the next (and you will get -1 if you ask).
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : ujafWy0mJHA.4140@TK2MSFTNGP05.phx.gbl...
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir
le résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : #s3#CJzmJHA.1252@TK2MSFTNGP03.phx.gbl...
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Un fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion : OSWVhTsmJHA.5980@TK2MSFTNGP06.phx.gbl...
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
Daniel
J'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe
de discussion : uOvQ76pmJHA.1184@TK2MSFTNGP04.phx.gbl...
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
Daniel
Et bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" <i@v> a écrit dans le message de groupe de discussion :
OXQ91bmmJHA.3876@TK2MSFTNGP02.phx.gbl...
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :
Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour connaître les détails sur l'utilisation du pilote
approprié. Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur de la propriété ConnectionString sur «
Microsoft.ACE.OLEDB.12.0 » Si vous vous connectez à des données Microsoft
Office Excel, ajoutez « Excel 12.0 » aux propriétés étendues de la chaîne de
connexion OLEDB. Si vous développez des applications à l'aide d'ODBC pour
vous connecter à des données Microsoft Office Access, définissez la chaîne
de connexion sur « Pilote={Pilote Microsoft Access (*.mdb,
*.accdb)};DBQ=emplacement du fichier mdb/accdb » Si vous développez des
applications à l'aide d'ODBC pour vous connecter à des données Microsoft
Office Excel, définissez la chaîne de connexion sur « Pilote={Pilote
Microsoft Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" a écrit dans le message de groupe de
discussion :
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
DanielC'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned of
-1; there's something enevitable about it, like the tides. The reasons for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming comminity then you will see a query on this subject at regular
intervals. I always answer this particular query as I have sympathy for the
developer, once I was like they are now; confused. I always thought such a
matter should be simple (how hard could it be?). Once I understood how
cursors work properly it all became clear. You will not get a correct
recordcount if the recordset does not support bookmarks. If
rs.Supports(adBookmark) Then ' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful. This
is what is normally given as the explanation is the docs and books which I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically you
should expect this to be true if you open a static or a keyset cursor. To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but it
is very appropriate). It is readonly but very light on your systems
resources and the fastest to use. As the name suggests you can only move
forwards through it; once you leave a record and move to another you cannot
go back. Another feature of this recordset is that ADO doesn't know how far
the data goes. The only way it can get from the first to the last record is
by moving records, one by one, until no more are left. Consequently it can't
possibly know how many there are until its too late. This is the cursor to
use if you want to load a list of records, which represent a list of
resources (e.g. language resource strings in a localization system), as
quickly as possible. Static - the most commonly used cursor type in my
experience. This is the cursor which clientside recordsets use no matter
what you may have asked for. It works like a snapshot of the database as
the data is copied out from the database tables and worked on in isolation.
Changes made by other users will happen without the recordset you have
opened being aware of it. The data can be read-only or read-write. When you
request this type of cursor you will be loading the entire set of records
into memory. For this reason a server can feel somewhat under seige if you
write a multiuser application and all the users are opening large data sets
(so think carefully). As there are very few large scale database
applications being written by developers this is not so important. Because
all the records in the selected data set are loaded into memory it is
simple for ADO to return the count. Keyset - similar to a static recordset
only much more clever. When a selection of records is made as a recordset
is opened the cursor maintains a list of the primary key values in memory
but not the other selected column values. Using the list of primary keys
(the key set) ADO retrieves enough rows from the table to fill it's record
cache which is what your code can look at. Each time you navigate
throughout the recordset a new [cachesize] set of records is retrieved from
the database using the static set of primary key values initially retrieved
when the recordset was first opened. This means that your recordset is
aware of changes to column values which other users make but it is not aware
of new additions (because the set of keys is not reloaded, its static). As
you can imagine using this cursor type involves a lot of return visits to
the database as the user navigates throughout the recordset so performance
is not as good as the static cursor which makes a copy in memory so it does
not have to return to the database. However as the set of primary keys is
loaded once and is in effect static ADO can easily return how many records
are in the recordset (by counting how many keys there are). Dynamic - the
big daddy of cursors. Be afraid, in fact I should turn a run if I were you.
I cannot remember the last time I the dynamic cursor type. It is definitely
intended for multiuser, multi-access systems which are very involved to
code properly. It is similar to a keyset cursor as it allows modifications
made by other users to show through to the recordset but this comes at a
price (a terrible price). When the recordset is opened ADO retrieves a list
of primary keys and then loads enough rows using the set of keys to satisfy
the cache. However when the user (or code) navigates through the recordset
ADO reloads the set of primary keys again (and again, and again, and
again...) before loading the new rows. You can see that this is very
epensive, even more so that the keyset cursor. Because the set of primary
keys is reloaded each time the user navigates record additions and
deletions will be seen. However, it also means that the number of records is
indeterminate and likely to change so ADO cannot say what it will be from
one moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à voir
le résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait :
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007 peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de groupe
de discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel 2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour
connaître les détails sur l'utilisation du pilote approprié.
Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur
de la propriété ConnectionString sur « Microsoft.ACE.OLEDB.12.0 »
Si vous vous connectez à des données Microsoft Office Excel, ajoutez «
Excel 12.0 » aux
propriétés étendues de la chaîne de connexion OLEDB.
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Access, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Access (*.mdb, *.accdb)};DBQ=emplacement du fichier mdb/accdb »
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Excel, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" a écrit dans le message de groupe de
discussion :
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
DanielC'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned
of
-1; there's something enevitable about it, like the tides. The reasons
for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming
comminity then you will see a query on this subject at regular
intervals. I
always answer this particular query as I have sympathy for the
developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors
work
properly it all became clear. You will not get a correct recordcount if
the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful.
This
is what is normally given as the explanation is the docs and books which
I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically
you
should expect this to be true if you open a static or a keyset cursor.
To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance
and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but
it is
very appropriate). It is readonly but very light on your systems
resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go
back.
Another feature of this recordset is that ADO doesn't know how far the
data
goes. The only way it can get from the first to the last record is by
moving
records, one by one, until no more are left. Consequently it can't
possibly
know how many there are until its too late. This is the cursor to use if
you
want to load a list of records, which represent a list of resources
(e.g.
language resource strings in a localization system), as quickly as
possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out
from
the database tables and worked on in isolation. Changes made by other
users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of
cursor
you will be loading the entire set of records into memory. For this
reason a
server can feel somewhat under seige if you write a multiuser
application
and all the users are opening large data sets (so think carefully). As
there
are very few large scale database applications being written by
developers
this is not so important. Because all the records in the selected data
set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of
the
primary key values in memory but not the other selected column values.
Using
the list of primary keys (the key set) ADO retrieves enough rows from
the
table to fill it's record cache which is what your code can look at.
Each
time you navigate throughout the recordset a new [cachesize] set of
records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that
your
recordset is aware of changes to column values which other users make
but it
is not aware of new additions (because the set of keys is not reloaded,
its
static). As you can imagine using this cursor type involves a lot of
return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in
memory
so it does not have to return to the database. However as the set of
primary
keys is loaded once and is in effect static ADO can easily return how
many
records are in the recordset (by counting how many keys there are).
Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I
were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but
this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set
of
keys to satisfy the cache. However when the user (or code) navigates
through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this
is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions
and
deletions will be seen. However, it also means that the number of records
is
indeterminate and likely to change so ADO cannot say what it will be from
one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe
de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à
voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si
intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de
façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe
de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe
de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait
:
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007
peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu
le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de
groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du
système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel
2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires
et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le
pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant
environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si
c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de
cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour
connaître les détails sur l'utilisation du pilote approprié.
Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur
de la propriété ConnectionString sur « Microsoft.ACE.OLEDB.12.0 »
Si vous vous connectez à des données Microsoft Office Excel, ajoutez «
Excel 12.0 » aux
propriétés étendues de la chaîne de connexion OLEDB.
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Access, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Access (*.mdb, *.accdb)};DBQ=emplacement du fichier mdb/accdb »
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Excel, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe de
discussion :
uqDIEa1mJHA.824@TK2MSFTNGP05.phx.gbl...
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
Daniel
C'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned
of
-1; there's something enevitable about it, like the tides. The reasons
for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming
comminity then you will see a query on this subject at regular
intervals. I
always answer this particular query as I have sympathy for the
developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors
work
properly it all became clear. You will not get a correct recordcount if
the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful.
This
is what is normally given as the explanation is the docs and books which
I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically
you
should expect this to be true if you open a static or a keyset cursor.
To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance
and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but
it is
very appropriate). It is readonly but very light on your systems
resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go
back.
Another feature of this recordset is that ADO doesn't know how far the
data
goes. The only way it can get from the first to the last record is by
moving
records, one by one, until no more are left. Consequently it can't
possibly
know how many there are until its too late. This is the cursor to use if
you
want to load a list of records, which represent a list of resources
(e.g.
language resource strings in a localization system), as quickly as
possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out
from
the database tables and worked on in isolation. Changes made by other
users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of
cursor
you will be loading the entire set of records into memory. For this
reason a
server can feel somewhat under seige if you write a multiuser
application
and all the users are opening large data sets (so think carefully). As
there
are very few large scale database applications being written by
developers
this is not so important. Because all the records in the selected data
set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of
the
primary key values in memory but not the other selected column values.
Using
the list of primary keys (the key set) ADO retrieves enough rows from
the
table to fill it's record cache which is what your code can look at.
Each
time you navigate throughout the recordset a new [cachesize] set of
records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that
your
recordset is aware of changes to column values which other users make
but it
is not aware of new additions (because the set of keys is not reloaded,
its
static). As you can imagine using this cursor type involves a lot of
return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in
memory
so it does not have to return to the database. However as the set of
primary
keys is loaded once and is in effect static ADO can easily return how
many
records are in the recordset (by counting how many keys there are).
Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I
were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but
this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set
of
keys to satisfy the cache. However when the user (or code) navigates
through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this
is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions
and
deletions will be seen. However, it also means that the number of records
is
indeterminate and likely to change so ADO cannot say what it will be from
one
moment to the next (and you will get -1 if you ask).
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe
de
discussion : ujafWy0mJHA.4140@TK2MSFTNGP05.phx.gbl...
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Ok. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à
voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si
intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de
façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe
de
discussion : #s3#CJzmJHA.1252@TK2MSFTNGP03.phx.gbl...
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
Daniel
Un fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de groupe
de
discussion : OSWVhTsmJHA.5980@TK2MSFTNGP06.phx.gbl...
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
Daniel
J'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait
:
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007
peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu
le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" <dcolardelleZZZ@gmail.com> a écrit dans le message de
groupe de
discussion : uOvQ76pmJHA.1184@TK2MSFTNGP04.phx.gbl...
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
Daniel
Et bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du
système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel
2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires
et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" <i@v> a écrit dans le message de groupe de discussion :
OXQ91bmmJHA.3876@TK2MSFTNGP02.phx.gbl...
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :
Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le
pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant
environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si
c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de
cette
limite ?
Merci de votre participation.
A propos du pilote pour office 2007 :
Possible de le télécharger là :
http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyIDu54f536-8c28-4598-9b72-ef94e038c891
(c'est peut être le même que sur le cd d'installation office 2007
Si vous utilisez une application, consultez la documentation de votre
application pour
connaître les détails sur l'utilisation du pilote approprié.
Si vous développez des applications à l'aide d'OLEDB, définissez
l'argument de fournisseur
de la propriété ConnectionString sur « Microsoft.ACE.OLEDB.12.0 »
Si vous vous connectez à des données Microsoft Office Excel, ajoutez «
Excel 12.0 » aux
propriétés étendues de la chaîne de connexion OLEDB.
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Access, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Access (*.mdb, *.accdb)};DBQ=emplacement du fichier mdb/accdb »
Si vous développez des applications à l'aide d'ODBC pour vous connecter à
des données
Microsoft Office Excel, définissez la chaîne de connexion sur «
Pilote={Pilote Microsoft
Excel (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=emplacement du fichier
xls/xlsx/xlsm/xlsb »
"Daniel.C" a écrit dans le message de groupe de
discussion :
Super.
Pas seulement pour le -1. J'ai appris les 4 types de curseurs utilisés
avec ADO. Reste à les essayer...
DanielC'est ce que je pensais... c'est une question de pilote !
Ok avec ceci : Provider=Microsoft.ACE.OLEDB.12.0
Mais il reste des zones grises comme le fait qu'il n'accepte
pas des plages nommées si le nombre de lignes dépassent 65536...
Et pour le -1 , désolé pour l'anglais :
Why is Recordcount sometimes -1 ? by Simon Greener
Anyone who uses ADO will eventually get a Recordset RecordCount returned
of
-1; there's something enevitable about it, like the tides. The reasons
for
this can be obscure and the standard documentation which comes from
Microsoft is not too helpful. If you are a member of an online VB
programming
comminity then you will see a query on this subject at regular
intervals. I
always answer this particular query as I have sympathy for the
developer,
once I was like they are now; confused. I always thought such a matter
should be simple (how hard could it be?). Once I understood how cursors
work
properly it all became clear. You will not get a correct recordcount if
the
recordset does not support bookmarks. If rs.Supports(adBookmark) Then
' I will get a recordcount.
End If
Such a statement as the one above, although true, is not very helpful.
This
is what is normally given as the explanation is the docs and books which
I
have read. The normal carbon based developer (such as you and I) need
something more, something which is actually helpful.
You may ask 'when will the recordset support bookmarks? How do I predict
that, pray tell?' A good question to ask, one which I asked. Basically
you
should expect this to be true if you open a static or a keyset cursor.
To
explain that a little more, to make it clear, we will now talk about
cursors. There are four types of cursors to be concerned about. These are
the
cursors which ADO supports.
. ForwardOnly
. Static
. Keyset
. Dynamic
The list shown (above) is in ascending order of complexity, performance
and
cost (of system resources).
ForwardOnly - sometimes referred to as a firehose cursor (not by me but
it is
very appropriate). It is readonly but very light on your systems
resources
and the fastest to use. As the name suggests you can only move forwards
through it; once you leave a record and move to another you cannot go
back.
Another feature of this recordset is that ADO doesn't know how far the
data
goes. The only way it can get from the first to the last record is by
moving
records, one by one, until no more are left. Consequently it can't
possibly
know how many there are until its too late. This is the cursor to use if
you
want to load a list of records, which represent a list of resources
(e.g.
language resource strings in a localization system), as quickly as
possible.
Static - the most commonly used cursor type in my experience. This is the
cursor which clientside recordsets use no matter what you may have asked
for. It works like a snapshot of the database as the data is copied out
from
the database tables and worked on in isolation. Changes made by other
users
will happen without the recordset you have opened being aware of it. The
data can be read-only or read-write. When you request this type of
cursor
you will be loading the entire set of records into memory. For this
reason a
server can feel somewhat under seige if you write a multiuser
application
and all the users are opening large data sets (so think carefully). As
there
are very few large scale database applications being written by
developers
this is not so important. Because all the records in the selected data
set
are loaded into memory it is simple for ADO to return the count. Keyset -
similar to a static recordset only much more clever. When a selection of
records is made as a recordset is opened the cursor maintains a list of
the
primary key values in memory but not the other selected column values.
Using
the list of primary keys (the key set) ADO retrieves enough rows from
the
table to fill it's record cache which is what your code can look at.
Each
time you navigate throughout the recordset a new [cachesize] set of
records
is retrieved from the database using the static set of primary key values
initially retrieved when the recordset was first opened. This means that
your
recordset is aware of changes to column values which other users make
but it
is not aware of new additions (because the set of keys is not reloaded,
its
static). As you can imagine using this cursor type involves a lot of
return
visits to the database as the user navigates throughout the recordset so
performance is not as good as the static cursor which makes a copy in
memory
so it does not have to return to the database. However as the set of
primary
keys is loaded once and is in effect static ADO can easily return how
many
records are in the recordset (by counting how many keys there are).
Dynamic
- the big daddy of cursors. Be afraid, in fact I should turn a run if I
were
you. I cannot remember the last time I the dynamic cursor type. It is
definitely intended for multiuser, multi-access systems which are very
involved to code properly. It is similar to a keyset cursor as it allows
modifications made by other users to show through to the recordset but
this
comes at a price (a terrible price). When the recordset is opened ADO
retrieves a list of primary keys and then loads enough rows using the set
of
keys to satisfy the cache. However when the user (or code) navigates
through
the recordset ADO reloads the set of primary keys again (and again, and
again, and again...) before loading the new rows. You can see that this
is
very epensive, even more so that the keyset cursor. Because the set of
primary keys is reloaded each time the user navigates record additions
and
deletions will be seen. However, it also means that the number of records
is
indeterminate and likely to change so ADO cannot say what it will be from
one
moment to the next (and you will get -1 if you ask).
"Daniel.C" a écrit dans le message de groupe
de
discussion :
Oui, ça m'intéresse bougrement (j'ai commencé à mettre mon nez dans ADO
seulement hier).
Le code suivant donne le nombre d'enregistrements :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Rst.Open texte_SQL, Cnn, adOpenStatic
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielOk. L'usage de "Execute permet de récupérer l'ensemble des données
même si le nombre de cellules excède 65536.
Si Set Rst = Cnn.Execute(texte_SQL) fonctionne,
Je ne sais pas pourquoi ceci ne fonctionne pas si la plage
initiale est de plus de 65536 lignes ? Si quelqu'un connaît
la (les) raison... il peut se manifester !
Rst.Open Requete, Cnn, adOpenForwardOnly, adLockReadOnly
Par contre pour obtenir le nombre d'enregistrements, on se doit
d'utiliser un curseur "adOpenStatic" au lieu de adOpenForwardOnly
sinon en utilisant la dernière, le nombre d'enregistrements obtenus
à l'aide de NbRecord = Rst.RecordCount donne toujours -1. En utilisant
"Execute" on ne peut pas définir le curseur qu'il doit utiliser et à
voir le
résultat, il emploie certainement "adOpenForwardOnly" par défaut. le
résultat -1 est dû à la manière dont le recordset est lu ... (si
intéressé,
je peux publier un truc la dessus). On peut obtenir la même chose de
façon
détourner en utilisant ceci NbRecord = UBound(Rst.GetRows(), 2)
évidemment un peu plus lent.
Merci pour ta participation.
"Daniel.C" a écrit dans le message de groupe
de
discussion : #s3#
Avec le code suivant, l'importation se fait, par contre, le recordcount
est à -1 :
Sub Proposition_Daniel_98184_lignes()
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Fichier As String
Dim Requete As String
Dim NbRecord As Long
Fichier = ThisWorkbook.FullName
Requete = "SELECT * FROM MaListe "
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
End With
'Rst.Open Requete, Cnn, adOpenStatic, adLockOptimistic
'NbRecord = Rst.RecordCount
'MsgBox NbRecord & " lignes"
NomFeuille = "Feuil1"
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cnn.Execute(texte_SQL)
NbRecord = Rst.RecordCount
MsgBox NbRecord & " lignes"
Range("A2").CopyFromRecordset Rst
Rst.Close: Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Sub
DanielUn fichier exemple de la problématique
http://cjoint.com/?demPlh3C2T
Ton lien pointe sur un fichier PDF. où est ton fichier volume.xlsx
"Daniel.C" a écrit dans le message de groupe
de
discussion :
Avec le code tel que je l'ai posté, j'ai récupéré les 300000 lignes du
classeur fermé (volume.xlsx).
DanielJ'ai vu l'adresse accompagnant ton message mais je n'ai pas
eu le temps d'en prendre connaissance ! Voici les tests que j'ai fait
:
avec : "Microsoft Activex Data 2.8 objects librairy"
J'ai une erreur d'exécution.
Mon code donne ceci comme message si plus de 65536 lignes
Erreur d'exécution : "-2147217865 (80040e37)
Ton code en plus d'avoir le message de mon code ajoute
ceci au message d'erreur :
message : Le moteur de données Microsoft Office Access n'a pas pu
trouver l'objet "Données$B1:B65537". Assurez-vous que l'objet
existe et que vous avez correctement saisi son nom et son chemin
d'accès.
Si j'utilise le pilote ODBC :
le code donne quelque chose comme :
'------------------------------------------
texte_SQL = "SELECT Market FROM MaListe "
Cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName Rst.Open texte_SQL, Cnn, adOpenStatic,
adLockOptimistic '------------------------------------------
J'ai ceci comme message si je vais au delà de 65536 lignes :
'-----------------------------
Erreur d'exécution : "-2147217887 (80040e21)
Ce pilote ne prend pas en charge les propriétés demandées.
'-----------------------------
J'arrive au même résultat avec les 3 méthodes.
Comme c'étais dans un fichier 2003 que j'avais converti version 2007
peut
être n'avait-il pas aimé la conversion. J'ai copié les données dans un
fichier tout neuf enregistré`directement au format xlsm et j'ai obtenu
le
même résultat.
Souffrerait-il d'un blocage psychologique ... ? ;-))
Merci pour ta collaboration.
"Daniel.C" a écrit dans le message de
groupe de
discussion :
Bonjour.
Je suis tombé sur ce code :
(http://silkyroad.ftp-developpez.com/VBA/ClasseursFermes/ClasseursFermes.pdf)
Sub RequeteClasseurFerme_Excel2007()
Dim Cn As ADODB.Connection
Dim Fichier As String
Dim NomFeuille As String, texte_SQL As String
Dim Rst As ADODB.Recordset
'Définit le classeur fermé servant de base de données
Fichier = "e:donneesdanielmpfevolume.xlsx"
'Nom de la feuille dans le classeur fermé
NomFeuille = "Volume"
Set Cn = New ADODB.Connection
'--- Connexion ---
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 12.0;HDR=YES;"""
.Open
'Requête
texte_SQL = "SELECT * FROM [" & NomFeuille & "$]"
Set Rst = New ADODB.Recordset
Set Rst = Cn.Execute(texte_SQL)
'Ecrit le résultat de la requête dans la cellule A2
Range("A2").CopyFromRecordset Rst
'--- Fermeture connexion ---
.Close
End With
Set Cn = Nothing
End Sub
Ca a l'air de faire l'affaire.
DanielEt bien, Merci Isabelle.
Je posais la question pour savoir si d'autres avaient éprouvé cette
difficulté. J'ai trouvé cela surprenant car ADO à l'extérieur de
l'environnement d'excel est plutôt limité par la capacité du
système...
le plus surprenant c'est que la requête bloque dès que je lui demande
d'aller au delà de 65536 lignes... ce qui est la limite d'excel
2003...
Lorsque le temps s'y prêtera, je ferai quelques tests supplémentaires
et
utilisant en outre le pilote ODBC plutôt que la version
"Microsoft.Jet.OLEDB.4.0"
N.B. ça fait plus que 2 jours que le message fut envoyé...nous serons
bientôt obligé d'envoyer nos messages par bateau !!! ;-))
Bonne journée.
"isabelle" a écrit dans le message de groupe de discussion :
bonjour Denis,
ma réponse est seulement pour que tu voie ton message, je n'ai pas
xl2007 désole,
isabelle
michdenis a écrit :Bonjour à toutes et à tous,
A ) Êtes-vous toujours là ? le forum ?
Depuis 4 - 5 jours, le moins que je peux dire est que la
communication a été difficile.
Est-ce que quelqu'un pourrait dire à Garnote de s'enlever le
pied
sur le cable ?
B ) Petite observation si le message passe...
Hier, j'ai utilisé ADO (activex data object 2.8 librairy) pour
effectuer une requête sur une table d'excel 2007 contenant
environ
70,000 lignes. Quelle fut ma surprise de me rendre compte que je
n'était pas capable (ado) d'aller au delà de 65536 lignes limite
d'excel 2003...! La question : Est-ce que quelqu'un sait si
c'est
une limite réelle d'ADO dans Excel ou
s'il y a une manipulation qui nous permet d'aller au-delà de
cette
limite ?
Merci de votre participation.