Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
Je reviens avec un autre souci. De ma précédente discussion, j'ai adapté un code de @patricktoulon.
Le code fonctionne bien mais encore faire des trucs.
VB:
Option Explicit
Sub test2()
Dim Dico As Object, cel As Range, myarray, k
Dim i As Integer
Set Dico = CreateObject("scripting.dictionary")
For Each cel In ThisWorkbook.Sheets("Feuil1").Range("TbAfectAnimal").Columns(2).Cells
If Not Dico.Exists(cel.Text) Then
Dico(cel.Text) = Array(cel.Text, 1, cel.Offset(, 2).Value)
Else
myarray = Dico(cel.Text)
myarray(1) = myarray(1) + 1
Dico(cel.Text) = myarray
End If
Next
'tes 3 valeurs pour chaque NoDOSSIER ,sont dans les items du dico (sous la forme d'un array)
'a savoir [ NoDossier , occurence , IdPerson]
For Each k In Dico.Keys
Debug.Print Join(Dico(k), " | ")
Next
End Sub
Je voudrais supprimer toutes les clés dont l'occurrence est >1 et ensuite copier celles qui restent dans le tableau de la feuil2.
J'arrive à lire les clés comme ceci dans une boucle
Code:
for i=0 to dico.count-1
debug.print dico.keys()(i)
next i
ADODB.Recordset est un objet utilisé pour manipuler et accéder aux ensembles de données en mémoire. Il est fréquemment utilisé dans des environnements VBA pour interagir avec des bases de données. Voici les aspects clés de cet objet :
Création : On crée une instance de ADODB.Recordset en utilisant CreateObject("ADODB.Recordset").
Champs (Fields) : Les champs définissent la structure des données dans le Recordset. On utilise .Fields.Append pour ajouter des champs avec un nom, un type de données, et parfois une taille.
Ouverture (Open) : La méthode .Open ouvre le Recordset et le rend prêt pour les opérations.
Filtrage (Filter) : La propriété .Filter permet de définir des critères pour afficher uniquement certains enregistrements. Le filtre est appliqué en utilisant des expressions SQL.
Ajout d'Enregistrement (AddNew) : La méthode .AddNew crée un nouvel enregistrement dans le Recordset. Après avoir ajouté un enregistrement, vous pouvez définir les valeurs des champs.
Mise à Jour (Update) : La méthode .Update enregistre les modifications apportées à l'enregistrement courant.
Déplacement (MoveFirst, MoveNext, etc.) : Les méthodes comme .MoveFirst, .MoveNext, etc., permettent de naviguer à travers les enregistrements du Recordset.
Fin du Recordset (EOF) : La propriété .EOF indique si le curseur est à la fin du Recordset, ce qui est utile pour vérifier s'il y a des enregistrements disponibles.
2. ADODB.Fields
ADODB.Fields est une collection de tous les champs d'un Recordset. Vous pouvez accéder à cette collection via la propriété .Fields du Recordset.
Ajouter des Champs (Append) : La méthode .Append est utilisée pour ajouter un nouveau champ à la collection. Vous spécifiez le nom du champ, le type de données, et parfois la taille.
Accéder aux Champs : Vous pouvez accéder à un champ spécifique dans un Recordset en utilisant son nom ou son index, comme !Societe ou rs.Fields("Societe").Value.
Utilisation dans le Code
VB:
Sub test()
Const adVarWChar = 202, adInteger = 3, adDBDate = 133
Dim Rs As Object, cel As Range
Set Rs = CreateObject("ADODB.Recordset")
With Rs
.Fields.Append "Societe", adVarWChar, 50
.Fields.Append "nb", adInteger
.Fields.Append "Date", adDBDate
.Open
For Each cel In ThisWorkbook.Sheets("Source").Range("TbSource").Columns(1).Cells
.Filter = "Societe='" & Replace(cel.Text, "'", "''") & "'"
If .EOF Then .AddNew
!Societe = cel.Text: !nb = !nb + 1: If !Date < cel.Offset(, 2) Then !Date = cel.Offset(, 2)
Next
.Filter = "nb>1": .Update: .MoveFirst
End With
With Feuil2.ListObjects("TbResultat")
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
If Not Rs.EOF Then .Parent.Cells(2, 1).CopyFromRecordset Rs
End With
End Sub
Explication des Objets dans le Code
Création du Recordset :
Set Rs = CreateObject("ADODB.Recordset") : Crée une nouvelle instance d'un objet ADODB.Recordset.
Définition des Champs :
.Fields.Append "Societe", adVarWChar, 50 : Ajoute un champ "Societe" avec un type de données chaîne de caractères Unicode (adVarWChar) et une taille maximale de 50 caractères.
.Fields.Append "nb", adInteger : Ajoute un champ "nb" avec un type de données entier (adInteger).
.Fields.Append "Date", adDBDate : Ajoute un champ "Date" avec un type de données date (adDBDate).
Opérations sur le Recordset :
.Open : Ouvre le Recordset pour permettre les opérations.
.Filter = "Societe='" & Replace(cel.Text, "'", "''") & "'" : Applique un filtre pour afficher les enregistrements où le champ "Societe" correspond à la valeur de cel.Text, en gérant les apostrophes simples pour éviter les erreurs de syntaxe SQL.
If .EOF Then .AddNew : Si aucun enregistrement n'est trouvé (fin du Recordset), ajoute un nouvel enregistrement.
!Societe = cel.Text : Assigne la valeur de cel.Text au champ "Societe".
!nb = !nb + 1 : Incrémente le champ "nb" de 1.
If !Date < cel.Offset(, 2) Then !Date = cel.Offset(, 2) : Met à jour le champ "Date" si la nouvelle date est plus récente.
Copie dans le Tableau Excel :
If Not Rs.EOF Then .Parent.Cells(2, 1).CopyFromRecordset Rs : Copie les données du Recordset dans le tableau Excel nommé "TbResultat", en commençant à partir de la cellule B2.
En résumé, le code utilise l'objet ADODB.Recordset pour gérer une collection de données en mémoire, appliquer des filtres, ajouter ou mettre à jour des enregistrements, et enfin copier ces données dans un tableau Excel pour affichage.
Si l'idée est de récupérer Les numéros de dossier ayant plusieurs aucurance il y a plus simple.
Certains de proposerai à juste titre power querry. Personnellement je n'y ai pas accès mais d'autres peuvent t'accompagner.
Personnellement je pourrais t'offrir un solutions SQL ma il faudra préciser ce que tu veux sélectionné comme information et sur la base de quel filtre. Et je te donnerai un script et toutes les explications qui si rattachement.
Je vais devoir m'absenter mais j'attends t'a réponse et en fonction je m'y colle ce soir.
Étant sur excel 2010, je n'ai pas installé le complément Power query. Je ne suis donc pas partant pour PQ.
Lorsque j'ouvre une discussion je ne joins jamais le fichier sur lequel je travaille.
Je monte des fichiers pour illustrer le problème sur lequel je bute.
J'aurais souhaité que tu me commentes ton précédent code pour mieux comprendre ta solution du post#10.
Curieux, je suis partant pour une solution SQL, mais ça serait trop long à expliquer car il s'agit pas d'un tableau mais de 4 tableaux.
Je prépare tout et ouvrirai une nouvelle discussion. J'indiquerai vouloir une solution SQL.
Je reviens avec un autre souci. De ma précédente discussion, j'ai adapté un code de @patricktoulon.
Le code fonctionne bien mais encore faire des trucs.
VB:
Option Explicit
Sub test2()
Dim Dico As Object, cel As Range, myarray, k
Dim i As Integer
Set Dico = CreateObject("scripting.dictionary")
For Each cel In ThisWorkbook.Sheets("Feuil1").Range("TbAfectAnimal").Columns(2).Cells
If Not Dico.Exists(cel.Text) Then
Dico(cel.Text) = Array(cel.Text, 1, cel.Offset(, 2).Value)
Else
myarray = Dico(cel.Text)
myarray(1) = myarray(1) + 1
Dico(cel.Text) = myarray
End If
Next
'tes 3 valeurs pour chaque NoDOSSIER ,sont dans les items du dico (sous la forme d'un array)
'a savoir [ NoDossier , occurence , IdPerson]
For Each k In Dico.Keys
Debug.Print Join(Dico(k), " | ")
Next
End Sub
Je voudrais supprimer toutes les clés dont l'occurrence est >1 et ensuite copier celles qui restent dans le tableau de la feuil2.
J'arrive à lire les clés comme ceci dans une boucle
Code:
for i=0 to dico.count-1
debug.print dico.keys()(i)
next i
je t'ai donné un moteur simple à toi d'en faire ce que tu veux après et ta boucle sur dico.counts c'est pas bon
on boucle sur les dico.keys et pas le dico.count
j'ai pourtant bien insisté d"ans la vidéo
for each patate in dicopommedeterre.keys
for i=0 to ubound(dicopommedeterre(patate))
msgbox dicopommedeterre(patate)(i)
next
next
D'où as-tu tiré ça. je ne suis pas très fort mais pas débile à ce point.
Si je l'ai fait, je dois consulter car ça ne va plus dans ma petite tête.
Mes boucles sur les dicos sont
for i=0 to dico.count-1
.......................................
next i
ou
for each cle in dico.keys
............................
next clé
Ce que je n'avais pas compris, c'est comment lire les items du dico lorsqu'ils sont dans un array.
Et tu me l'as bien expliqué dans la vidéos je t'en remercie.
### Late Binding (liaison tardive) vs Early Binding (liaison anticipée)
- **Early Binding (liaison anticipée)** :
- Se produit lorsque les types d'objets sont définis au moment de la compilation du code. En VBA, cela signifie que tu as référencé la bibliothèque d'objets appropriée via `Outils > Références`, et que tu utilises le mot-clé `New` pour créer une instance d'un objet.
- Exemple : `Set Dico = New Scripting.Dictionary`
- Avantages :
- Meilleure performance : Le code est plus rapide car les références sont résolues lors de la compilation.
- Assistance IntelliSense : Tu bénéficies de l'auto-complétion et des vérifications de syntaxe pendant que tu écris ton code.
- Vérification de type : Les erreurs de type peuvent être détectées lors de la compilation.
- **Late Binding (liaison tardive)** :
- Se produit lorsque les objets sont créés au moment de l'exécution et non à la compilation. Cela se fait souvent en utilisant `CreateObject`, où l'objet est défini comme `Object` jusqu'à ce qu'il soit réellement instancié.
- Exemple : `Set Dico = CreateObject("Scripting.Dictionary")`
- Avantages :
- Flexibilité : Utile lorsque le code doit fonctionner dans différents environnements où la bibliothèque pourrait ne pas être installée, ou avec différentes versions.
- Aucune référence nécessaire : Tu n'as pas besoin de référencer la bibliothèque d'objets explicitement dans ton projet.
### Application dans ton cas :
- **Early Binding** (avec `New`) : Si tu utilises `Set Dico = New Scripting.Dictionary`, tu fais de la liaison anticipée. La bibliothèque `Microsoft Scripting Runtime` doit être référencée dans ton projet VBA.
- **Late Binding** (avec `CreateObject`) : Si tu utilises `Set Dico = CreateObject("Scripting.Dictionary")`, tu fais de la liaison tardive. Cela te permet de créer l'objet `Dictionary` même si la bibliothèque n'est pas référencée dans ton projet, mais cela se fait au détriment de la performance et de la vérification de type lors de la compilation.
Donc, en résumé, le choix entre `CreateObject` et `New` dépend principalement de la manière dont tu souhaites gérer les références aux bibliothèques d'objets dans ton code VBA, ainsi que du besoin de flexibilité par rapport à la performance.
j'en ai fait bon usage. Lorsqu'on me fait des propositions. La moindre des politesses est que je réponde.
D'où as-tu tiré ça. je ne suis pas très fort mais pas débile à ce point.
Si je l'ai fait, je dois consulter car ça ne va plus dans ma petite tête.
Mes boucles sur les dicos sont
for i=0 to dico.count-1
.......................................
next i
ou
for each cle in dico.keys
............................
next clé
Ce que je n'avais pas compris, c'est comment lire les items du dico lorsqu'ils sont dans un array.
Et tu me l'as bien expliqué dans la vidéos je t'en remercie.
Ce code VBA montre comment utiliser une connexion ADODB pour exécuter une requête SQL sur une feuille Excel, puis copier les résultats dans une autre feuille. Je vais expliquer chaque partie du code et les objets ADODB utilisés.
Présentation et Explication du Code
VB:
Sub test2()
Dim sql As String
Feuil1.[E1] = "TOTO" ' Change la valeur de la cellule E1 sur Feuil1 à "TOTO"
' Supprime les données existantes dans le tableau nommé "TbRes" sur Feuil2, s'il y en a
If Not Feuil2.ListObjects("TbRes").DataBodyRange Is Nothing Then
Feuil2.ListObjects("TbRes").DataBodyRange.Delete
End If
' Définition de la requête SQL
sql = "SELECT Max([Date]), NoDossier, Last(IdAnimal), Last(IdPerson), Last(TOTO) " & _
"FROM [Feuil1$] GROUP BY NoDossier HAVING COUNT(NoDossier) > 1"
' Création d'une connexion ADODB
Dim Cn As Object
Set Cn = CreateObject("ADODB.Connection")
With Cn
' Ouverture de la connexion avec une chaîne de connexion directe
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
' Exécution de la requête SQL et copie des résultats dans la cellule A2 de Feuil2
Feuil2.[A2].CopyFromRecordset .Execute(sql)
' Fermeture de la connexion
.Close
End With
' Réinitialisation de la valeur de la cellule E1 sur Feuil1
Feuil1.[E1] = "Cat."
End Sub
Explication des Objets et Méthodes Utilisés
Feuil1.[E1] et Feuil2.[A2]:
Utilisation : Ces expressions accèdent directement aux cellules E1 sur Feuil1 et A2 sur Feuil2. Cette notation permet de lire ou écrire des valeurs dans des cellules spécifiques.
Modifications : La cellule E1 sur Feuil1 est temporairement changée à "TOTO", puis elle est modifiée en "Cat." après l'exécution de la requête, la raison est du au fait que les champ contenant un [.] point sont difficilement gérable dans les requête de regroupement .
Feuil2.ListObjects("TbRes").DataBodyRange:
Utilisation : Accède au tableau nommé "TbRes" sur Feuil2 et son corps de données (DataBodyRange).
Suppression des Données : Si le tableau contient des données (DataBodyRange n'est pas Nothing), les données existantes sont supprimées pour préparer l'insertion des nouveaux résultats.
sql:
Définition : La chaîne sql contient la requête SQL à exécuter. Cette requête sélectionne la date maximale, le numéro de dossier, et les dernières valeurs de plusieurs colonnes, groupées par NoDossier, avec une condition sur le nombre de dossiers.
CreateObject("ADODB.Connection"):
Création : Crée une instance d'un objet ADODB.Connection, utilisé pour se connecter à une source de données.
Provider : Microsoft.ACE.OLEDB.12.0 est le fournisseur OLEDB utilisé pour se connecter à un fichier Excel.
Data Source : Spécifie le chemin du fichier Excel, ici le fichier courant (ThisWorkbook.FullName).
Extended Properties : Détermine des options supplémentaires pour la connexion. Excel 12.0 Xml indique que le fichier est un fichier Excel 2007 ou plus récent. HDR=YES signifie que la première ligne de données est utilisée comme en-tête.
.Execute(sql):
Exécution de Requête : Exécute la requête SQL définie dans la variable sql et retourne un objet ADODB.Recordset contenant les résultats de la requête.
Feuil2.[A2].CopyFromRecordset:
Copie des Résultats : La méthode CopyFromRecordset copie les données du Recordset retourné par l'exécution de la requête SQL directement dans la cellule A2 de Feuil2.
Cn.Close:
Fermeture de Connexion : Ferme la connexion à la source de données pour libérer les ressources.
Conclusion
Le code utilise l'objet ADODB.Connection pour ouvrir une connexion à une feuille Excel, exécuter une requête SQL, et copier les résultats dans un tableau Excel. La chaîne de connexion directe est utilisée pour se connecter au fichier Excel en spécifiant le fournisseur OLEDB et les propriétés étendues pour le fichier Excel. Ce code montre comment utiliser ADODB pour manipuler des données dans Excel en utilisant SQL, et comment intégrer ces données dans des tableaux Excel pour présentation.
Requête SQL Commentée
Code:
SELECT Max([Date]), NoDossier, Last(IdAnimal), Last(IdPerson), Last(TOTO)
FROM [Feuil1$]
GROUP BY NoDossier
HAVING COUNT(NoDossier) > 1
SELECT : Cette clause indique les colonnes et les expressions à inclure dans les résultats de la requête.
Max([Date]) : Sélectionne la valeur maximale du champ [Date]. Max() est une fonction d'agrégation qui renvoie la plus grande valeur dans le groupe d'enregistrements.
NoDossier : Inclut le champ NoDossier dans les résultats. Ce champ est utilisé pour grouper les enregistrements.
Last(IdAnimal) : Renvoie la dernière valeur trouvée dans le champ IdAnimal pour chaque groupe d'enregistrements. Notez que la fonction Last() n'est pas standard SQL, mais est supportée dans les connexions OLEDB avec Excel pour obtenir la dernière valeur d'un champ dans un groupe.
Last(IdPerson) : Renvoie la dernière valeur trouvée dans le champ IdPerson pour chaque groupe d'enregistrements.
Last(TOTO) : Renvoie la dernière valeur trouvée dans le champ TOTO pour chaque groupe d'enregistrements.
FROM [Feuil1$]:
FROM : Spécifie la source des données pour la requête.
[Feuil1$] : Indique le nom de la feuille Excel à partir de laquelle les données sont extraites. Le $ est utilisé pour désigner une feuille de calcul dans Excel dans les requêtes OLEDB.
GROUP BY NoDossier:
GROUP BY : Cette clause regroupe les résultats en ensembles d'enregistrements qui partagent la même valeur dans une colonne spécifique.
NoDossier : Les enregistrements sont regroupés par le champ NoDossier. Cela signifie que les fonctions d'agrégation comme Max() et Last() sont calculées pour chaque groupe distinct de NoDossier.
HAVING COUNT(NoDossier) > 1:
HAVING : Cette clause est utilisée pour filtrer les groupes créés par GROUP BY, en se basant sur une condition.
COUNT(NoDossier) > 1 : Condition qui filtre les groupes pour inclure uniquement ceux où le nombre d'enregistrements (pour chaque NoDossier) est supérieur à 1. En d'autres termes, cette condition conserve uniquement les groupes où NoDossier apparaît plus d'une fois.
Résumé
La requête SQL sélectionne les données de la feuille Excel Feuil1 avec les colonnes suivantes :
La date maximale pour chaque groupe de NoDossier.
Le champ NoDossier.
La dernière valeur trouvée pour IdAnimal, IdPerson, et TOTO dans chaque groupe de NoDossier.
Les résultats sont regroupés par NoDossier, et seuls les groupes où NoDossier apparaît plus d'une fois sont inclus dans les résultats finaux.
Cette requête est utilisée pour analyser et extraire des informations agrégées des données dans une feuille Excel, avec un focus sur les groupes ayant plusieurs occurrences.
Sub test()
' Déclaration des constantes pour les types de données
Const adVarWChar = 202, adInteger = 3, adDBDate = 133
' Déclaration des variables
Dim Rs As Object, cel As Range
Set Rs = CreateObject("ADODB.Recordset")
' Configuration du Recordset avec les champs nécessaires
With Rs
.Fields.Append "Societe", adVarWChar, 50 ' Champ pour le nom de la société
.Fields.Append "nb", adInteger ' Champ pour le nombre d'occurrences
.Fields.Append "Date", adDBDate ' Champ pour la date la plus récente
.Open
' Parcours de chaque cellule dans la colonne 1 du tableau TbSource
For Each cel In ThisWorkbook.Sheets("Source").Range("TbSource").Columns(1).Cells
.Filter = "Societe='" & Replace(cel.Text, "'", "''") & "'" ' Filtrer par société
If .EOF Then .AddNew ' Ajouter un nouvel enregistrement si non trouvé
!Societe = cel.Text
!nb = !nb + 1 ' Incrémenter le compteur
If !Date < cel.Offset(, 2) Then !Date = cel.Offset(, 2) ' Mettre à jour la date la plus récente
Next
.Filter = "nb>1" ' Ne conserver que les sociétés apparaissant plus d'une fois
.Update ' Appliquer les modifications
.MoveFirst ' Se positionner au début du recordset
End With
' Copie des résultats dans le tableau TbResultat sur Feuil2
With Feuil2.ListObjects("TbResultat")
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete ' Vider le tableau s'il contient des données
If Not Rs.EOF Then .Parent.Cells(2, 1).CopyFromRecordset Rs ' Copier les résultats dans le tableau
End With
End Sub
Ce code VBA montre comment utiliser une connexion ADODB pour exécuter une requête SQL sur une feuille Excel, puis copier les résultats dans une autre feuille. Je vais expliquer chaque partie du code et les objets ADODB utilisés.
Présentation et Explication du Code
VB:
Sub test2()
Dim sql As String
Feuil1.[E1] = "TOTO" ' Change la valeur de la cellule E1 sur Feuil1 à "TOTO"
' Supprime les données existantes dans le tableau nommé "TbRes" sur Feuil2, s'il y en a
If Not Feuil2.ListObjects("TbRes").DataBodyRange Is Nothing Then
Feuil2.ListObjects("TbRes").DataBodyRange.Delete
End If
' Définition de la requête SQL
sql = "SELECT Max([Date]), NoDossier, Last(IdAnimal), Last(IdPerson), Last(TOTO) " & _
"FROM [Feuil1$] GROUP BY NoDossier HAVING COUNT(NoDossier) > 1"
' Création d'une connexion ADODB
Dim Cn As Object
Set Cn = CreateObject("ADODB.Connection")
With Cn
' Ouverture de la connexion avec une chaîne de connexion directe
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
' Exécution de la requête SQL et copie des résultats dans la cellule A2 de Feuil2
Feuil2.[A2].CopyFromRecordset .Execute(sql)
' Fermeture de la connexion
.Close
End With
' Réinitialisation de la valeur de la cellule E1 sur Feuil1
Feuil1.[E1] = "Cat."
End Sub
Explication des Objets et Méthodes Utilisés
Feuil1.[E1] et Feuil2.[A2]:
Utilisation : Ces expressions accèdent directement aux cellules E1 sur Feuil1 et A2 sur Feuil2. Cette notation permet de lire ou écrire des valeurs dans des cellules spécifiques.
Modifications : La cellule E1 sur Feuil1 est temporairement changée à "TOTO", puis elle est modifiée en "Cat." après l'exécution de la requête, la raison est du au fait que les champ contenant un [.] point sont difficilement gérable dans les requête de regroupement .
Feuil2.ListObjects("TbRes").DataBodyRange:
Utilisation : Accède au tableau nommé "TbRes" sur Feuil2 et son corps de données (DataBodyRange).
Suppression des Données : Si le tableau contient des données (DataBodyRange n'est pas Nothing), les données existantes sont supprimées pour préparer l'insertion des nouveaux résultats.
sql:
Définition : La chaîne sql contient la requête SQL à exécuter. Cette requête sélectionne la date maximale, le numéro de dossier, et les dernières valeurs de plusieurs colonnes, groupées par NoDossier, avec une condition sur le nombre de dossiers.
CreateObject("ADODB.Connection"):
Création : Crée une instance d'un objet ADODB.Connection, utilisé pour se connecter à une source de données.
Provider : Microsoft.ACE.OLEDB.12.0 est le fournisseur OLEDB utilisé pour se connecter à un fichier Excel.
Data Source : Spécifie le chemin du fichier Excel, ici le fichier courant (ThisWorkbook.FullName).
Extended Properties : Détermine des options supplémentaires pour la connexion. Excel 12.0 Xml indique que le fichier est un fichier Excel 2007 ou plus récent. HDR=YES signifie que la première ligne de données est utilisée comme en-tête.
.Execute(sql):
Exécution de Requête : Exécute la requête SQL définie dans la variable sql et retourne un objet ADODB.Recordset contenant les résultats de la requête.
Feuil2.[A2].CopyFromRecordset:
Copie des Résultats : La méthode CopyFromRecordset copie les données du Recordset retourné par l'exécution de la requête SQL directement dans la cellule A2 de Feuil2.
Cn.Close:
Fermeture de Connexion : Ferme la connexion à la source de données pour libérer les ressources.
Conclusion
Le code utilise l'objet ADODB.Connection pour ouvrir une connexion à une feuille Excel, exécuter une requête SQL, et copier les résultats dans un tableau Excel. La chaîne de connexion directe est utilisée pour se connecter au fichier Excel en spécifiant le fournisseur OLEDB et les propriétés étendues pour le fichier Excel. Ce code montre comment utiliser ADODB pour manipuler des données dans Excel en utilisant SQL, et comment intégrer ces données dans des tableaux Excel pour présentation.
Requête SQL Commentée
Code:
SELECT Max([Date]), NoDossier, Last(IdAnimal), Last(IdPerson), Last(TOTO)
FROM [Feuil1$]
GROUP BY NoDossier
HAVING COUNT(NoDossier) > 1
SELECT : Cette clause indique les colonnes et les expressions à inclure dans les résultats de la requête.
Max([Date]) : Sélectionne la valeur maximale du champ [Date]. Max() est une fonction d'agrégation qui renvoie la plus grande valeur dans le groupe d'enregistrements.
NoDossier : Inclut le champ NoDossier dans les résultats. Ce champ est utilisé pour grouper les enregistrements.
Last(IdAnimal) : Renvoie la dernière valeur trouvée dans le champ IdAnimal pour chaque groupe d'enregistrements. Notez que la fonction Last() n'est pas standard SQL, mais est supportée dans les connexions OLEDB avec Excel pour obtenir la dernière valeur d'un champ dans un groupe.
Last(IdPerson) : Renvoie la dernière valeur trouvée dans le champ IdPerson pour chaque groupe d'enregistrements.
Last(TOTO) : Renvoie la dernière valeur trouvée dans le champ TOTO pour chaque groupe d'enregistrements.
FROM [Feuil1$]:
FROM : Spécifie la source des données pour la requête.
[Feuil1$] : Indique le nom de la feuille Excel à partir de laquelle les données sont extraites. Le $ est utilisé pour désigner une feuille de calcul dans Excel dans les requêtes OLEDB.
GROUP BY NoDossier:
GROUP BY : Cette clause regroupe les résultats en ensembles d'enregistrements qui partagent la même valeur dans une colonne spécifique.
NoDossier : Les enregistrements sont regroupés par le champ NoDossier. Cela signifie que les fonctions d'agrégation comme Max() et Last() sont calculées pour chaque groupe distinct de NoDossier.
HAVING COUNT(NoDossier) > 1:
HAVING : Cette clause est utilisée pour filtrer les groupes créés par GROUP BY, en se basant sur une condition.
COUNT(NoDossier) > 1 : Condition qui filtre les groupes pour inclure uniquement ceux où le nombre d'enregistrements (pour chaque NoDossier) est supérieur à 1. En d'autres termes, cette condition conserve uniquement les groupes où NoDossier apparaît plus d'une fois.
Résumé
La requête SQL sélectionne les données de la feuille Excel Feuil1 avec les colonnes suivantes :
La date maximale pour chaque groupe de NoDossier.
Le champ NoDossier.
La dernière valeur trouvée pour IdAnimal, IdPerson, et TOTO dans chaque groupe de NoDossier.
Les résultats sont regroupés par NoDossier, et seuls les groupes où NoDossier apparaît plus d'une fois sont inclus dans les résultats finaux.
Cette requête est utilisée pour analyser et extraire des informations agrégées des données dans une feuille Excel, avec un focus sur les groupes ayant plusieurs occurrences.
Je te remercie beaucoup. Tu viens de me faire un cours. Je l'ai lu plusieurs fois que le SQL était puissant et facile à comprendre, car basé sur le langage humain.
J'ai toujours eu une appréhension et je ne me suis jamais intéressé à ce langage.
Je dois m'y mettre, tu viens de me montrer sur mon exemple son utilisation.
Sub test()
' Déclaration des constantes pour les types de données
Const adVarWChar = 202, adInteger = 3, adDBDate = 133
' Déclaration des variables
Dim Rs As Object, cel As Range
Set Rs = CreateObject("ADODB.Recordset")
' Configuration du Recordset avec les champs nécessaires
With Rs
.Fields.Append "Societe", adVarWChar, 50 ' Champ pour le nom de la société
.Fields.Append "nb", adInteger ' Champ pour le nombre d'occurrences
.Fields.Append "Date", adDBDate ' Champ pour la date la plus récente
.Open
' Parcours de chaque cellule dans la colonne 1 du tableau TbSource
For Each cel In ThisWorkbook.Sheets("Source").Range("TbSource").Columns(1).Cells
.Filter = "Societe='" & Replace(cel.Text, "'", "''") & "'" ' Filtrer par société
If .EOF Then .AddNew ' Ajouter un nouvel enregistrement si non trouvé
!Societe = cel.Text
!nb = !nb + 1 ' Incrémenter le compteur
If !Date < cel.Offset(, 2) Then !Date = cel.Offset(, 2) ' Mettre à jour la date la plus récente
Next
.Filter = "nb>1" ' Ne conserver que les sociétés apparaissant plus d'une fois
.Update ' Appliquer les modifications
.MoveFirst ' Se positionner au début du recordset
End With
' Copie des résultats dans le tableau TbResultat sur Feuil2
With Feuil2.ListObjects("TbResultat")
If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete ' Vider le tableau s'il contient des données
If Not Rs.EOF Then .Parent.Cells(2, 1).CopyFromRecordset Rs ' Copier les résultats dans le tableau
End With
End Sub
### Late Binding (liaison tardive) vs Early Binding (liaison anticipée)
- **Early Binding (liaison anticipée)** :
- Se produit lorsque les types d'objets sont définis au moment de la compilation du code. En VBA, cela signifie que tu as référencé la bibliothèque d'objets appropriée via `Outils > Références`, et que tu utilises le mot-clé `New` pour créer une instance d'un objet.
- Exemple : `Set Dico = New Scripting.Dictionary`
- Avantages :
- Meilleure performance : Le code est plus rapide car les références sont résolues lors de la compilation.
- Assistance IntelliSense : Tu bénéficies de l'auto-complétion et des vérifications de syntaxe pendant que tu écris ton code.
- Vérification de type : Les erreurs de type peuvent être détectées lors de la compilation.
- **Late Binding (liaison tardive)** :
- Se produit lorsque les objets sont créés au moment de l'exécution et non à la compilation. Cela se fait souvent en utilisant `CreateObject`, où l'objet est défini comme `Object` jusqu'à ce qu'il soit réellement instancié.
- Exemple : `Set Dico = CreateObject("Scripting.Dictionary")`
- Avantages :
- Flexibilité : Utile lorsque le code doit fonctionner dans différents environnements où la bibliothèque pourrait ne pas être installée, ou avec différentes versions.
- Aucune référence nécessaire : Tu n'as pas besoin de référencer la bibliothèque d'objets explicitement dans ton projet.
### Application dans ton cas :
- **Early Binding** (avec `New`) : Si tu utilises `Set Dico = New Scripting.Dictionary`, tu fais de la liaison anticipée. La bibliothèque `Microsoft Scripting Runtime` doit être référencée dans ton projet VBA.
- **Late Binding** (avec `CreateObject`) : Si tu utilises `Set Dico = CreateObject("Scripting.Dictionary")`, tu fais de la liaison tardive. Cela te permet de créer l'objet `Dictionary` même si la bibliothèque n'est pas référencée dans ton projet, mais cela se fait au détriment de la performance et de la vérification de type lors de la compilation.
Donc, en résumé, le choix entre `CreateObject` et `New` dépend principalement de la manière dont tu souhaites gérer les références aux bibliothèques d'objets dans ton code VBA, ainsi que du besoin de flexibilité par rapport à la performance.
Bonjour,
Adodb fait référence à la librairie: Microsoft Activex Data Object {A.D.O}. Si tu veux utiliser ADODB en Early Binding alors Oui il te faudra activer la librairie.
Je suis fainéant comme pas deux, je rédige le code mois même, car je n'es pas confiance,et je soustraite à Chatgpt le soins de d'expliquer et doccumenter.
Si tu analyse mes différents postes tu trouveras la différence.
Bonjour,
Adodb fait référence à la librairie: Microsoft Activex Data Object {A.D.O}. Si tu veux utiliser ADODB en Early Binding alors Oui il te faudra activer la librairie.
Je suis fainéant comme pas deux, je rédige le code mois même, car je n'es pas confiance,et je soustraite à Chatgpt le soins de d'expliquer et doccumenter.
Si tu analyse mes différents postes tu trouveras la différence.
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
,
