Chercher et Extraire des données dans un classeur fermé

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Chercher dans un autre classeur

Grâce à l'astuce précédente, nous avons appris à récupérer du contenu à partir d'un classeur externe et fermé. Dans cette suite logique, nous allons apprendre à réaliser des recherches dans un classeur externe pour extraire des informations précises et les importer sur le classeur principal.



Sur l'exemple illustré par la capture, l'utilisateur choisit la référence d'un article à l'aide d'une liste déroulante. Instantanément, les informations associées comme la désignation et le prix sont rapatriées dans les cellules du dessous. Pourtant ce classeur n'héberge aucune information. La recherche de la référence est engagée dans une base de données abritée par un classeur externe et fermé. Cette prouesse, nous proposons de la réaliser par le code VBA Excel.

Classeurs Excel à télécharger
Pour aboutir ces travaux, nous avons besoin d'un classeur principal et d'une base de données externe.

• Télécharger le fichier compressé chercher-dans-autre-classeur.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous pouvez le voir, la décompression livre deux classeurs Excel. Le premier est nommé bdd. C'est lui qui héberge la base de données dans laquelle nous devons entreprendre les recherches. Le second est nommé importer-autre-classeur. C'est lui qui doit diriger ces recherches dans la base externe.

• Double cliquer sur le classeur bdd.xlsx pour l'ouvrir dans Excel,

Nous y découvrons un tableau long recensant de nombreux articles vestimentaires. La référence à chercher est située en colonne B. Les informations s'étendent sur la plage de cellules B4:H247. Les données à rapatrier sur le nom, le prix, le poids et le stock sont placées respectivement en deuxième, troisième, quatrième et sixième colonnes de ce tableau. Ces remarques sont loin d'être anodines quand il s'agira de bâtir la formule d'extraction en VBA Excel, depuis le classeur principal.

• Fermer cette base de données en cliquant sur la croix de sa fenêtre,
• Puis, double cliquer sur le fichier importer-autre-classeur.xlsm pour l'ouvrir dans Excel,

Nous découvrons le tableau de synthèse à implémenter automatiquement au choix d'une référence avec la liste déroulante en cellule E5. Il s'agit bien entendu des références issues de la base de données externe. Les informations associées sur la désignation, le prix, le poids et le stock doivent être rapatriées en cellules respectives C7, C9, E9 et G9.

Code VBA au choix
C'est donc au choix d'une référence avec la liste déroulante que nous devons exécuter un code VBA capable de réaliser la recherche de ce code dans le tableau du classeur externe bdd.xlsx. Pour cela et nous en avons l'habitude, nous devons exploiter une procédure événementielle spéciale, capable de détecter les changements de valeurs dans les cellules de la feuille. Cette procédure événementielle existe déjà et nous allons le constater.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,

Nous découvrons effectivement la présence de la procédure Worksheet_Change. Nous l'avons construite en choisissant l'élément Worksheet avec la liste de gauche, en haut de la feuille de code. Elle se déclenchera dès qu'une cellule sera modifiée dans la feuille. Mais nous devrons limiter son exécution au changement de valeur en cellule E5 pour ne pas consommer les ressources inutilement. Nous y reviendrons en temps voulu. Un paramètre est en attente dans cette procédure. Il se nomme Target. Il représente la cellule modifiée. C'est grâce à lui que nous pourrons récolter la référence à rechercher dans la base de données externe.

La variable du chemin à la base externe
Pour débuter, nous avons besoin d'une variable pour mémoriser le chemin d'accès complet à la base de données externe. C'est ainsi que nous pourrons exploiter une fonction de recherche capable d'analyser ce tableau. Ce chemin doit être composé du chemin d'accès au classeur externe, du nom du classeur lui-même à inscrire entre crochets, du nom de la feuille cible pour enfin atteindre la plage du tableau à préfixer d'un point d'exclamation. C'est ainsi dans la syntaxe Excel.

• Dans les bornes de la procédure événementielle, ajouter la déclaration de variable suivante :

...
Dim chemin As String
...

Nous nommons cette variable chemin. Naturellement, nous la typons comme un texte (As String) pour recevoir le chemin d'accès complet à construire.

Vérifier la cellule modifiée
Nous l'avons annoncé précédemment, nous ne devons exécuter ce code VBA que si le changement est opéré dans la cellule E5 de la liste déroulante.

• A la suite du code VBA, créer l'instruction conditionnelle suivante :

...
If Target.Address = "$E$5" Then

End If
...

C'est la propriété Address de l'objet Target passé en paramètre qui renseigne sur les coordonnées de la cellule modifiée par l'utilisateur sur la feuille. S'il s'agit bien de la cellule de la liste déroulante ("$E$5), alors (Then) nous poursuivons l'exécution.

Le chemin de la plage externe
C'est maintenant que nous devons assembler les différents éléments énoncés précédemment pour pouvoir pointer sur la plage de cellules B4:H247 de la base de données externe.

• Dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
chemin = "'" & ThisWorkbook.Path & "\[bdd.xlsx]Catalogue'!B4:H247"
...

Selon la syntaxe d'Excel, ce chemin doit nécessairement est inscrit entre côtes. Comme nous sommes déjà dans des doubles côtes, nous utilisons la simple côté (') pour l'encadrement, avec la touche 4 en haut à gauche du clavier. La propriété Path de l'objet ThisWorkbook renseigne tout d'abord sur l'emplacement du classeur externe, soit le dossier de l'application active. Après un antislah (\) pour entrer dans le dossier, nous lui associons le nom du classeur choisi, entre crochets ([bdd.xlsx]). Puis, par voie hiérarchique, nous désignons sa feuille que nous faisons suivre d'un point d'exclamation (Catalogue'!), après avoir fermé la côte, pour enfin atteindre la plage de cellules cible (B4:H247) afin d'y exécuter la recherche de la référence choisie avec la liste déroulante.

Lancer la recherche
La fonction Excel dédiée mais aussi la plus simple pour organiser cette recherche et les extractions des informations associées est la fonction RechercheV. Mais attention, nous sommes en train de coder en VBA. En anglais, elle se nomme vlookup. Nous devons lui demander de chercher la référence (E5) dans la plage externe (chemin). Puis, nous devons spécifier le numéro de la colonne de l'information concordante à retourner. Dans le tableau de recherche, la désignation est par exemple située en deuxième colonne (2).

• A la suite dans l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
[C7].Formula = "=vlookup(E5, " & chemin & ", 2, false)"
[C7].Value = [C7].Value
...

Dans la cellule de la désignation (C7), nous inscrivons la formule de recherche grâce à la propriété Formula. Nous exploitons la fonction RechercheV (vlookup) à la recherche de la référence choisie (E5) dans la base de données externe (," & chemin & "). La concaténation s'impose ensuite pour briser la chaîne statique dans laquelle nous sommes, pour pouvoir transmettre une donnée dynamique. En retour, nous demandons de restituer la désignation placée en deuxième colonne du tableau (2). Enfin, nous réalisons une recherche exacte (false). Ensuite, nous remplaçons la formule par la valeur de l'extraction par une affectation exacte.

Importer les autres valeurs
Pour extraire les autres informations concordantes, c'est exactement le même principe qui doit être engagé. Mais en fonction de la donnée demandée, la fonction RechercheV doit adapter son indice de retour.

• Toujours à la suite de l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
[C9].Formula = "=vlookup(E5, " & chemin &", 3, false)"
[C9].Value = [C9].Value
[E9].Formula = "=vlookup(E5, " & chemin &", 4, false)"
[E9].Value = [E9].Value
[G9].Formula = "=vlookup(E5, " & chemin &", 6, false)"
[G9].Value = [G9].Value
...

Pour le prix (C9), le poids (E9) et le stock (G9), nous engageons la même recherche verticale sur le tableau. Mais nous demandons les informations respectives en retour (3, 4 et 6). Il ne nous reste plus qu'à tester cette solution.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Avec la liste déroulante en cellule E5, choisir une nouvelle référence,

Aussitôt et comme vous pouvez l'apprécier, toutes les informations, issues de la base de données externe fermée, sont extraites dans les cellules prévues à cet effet.