Une macro pour extraire des données d'une base Excel

VBA et les listbox


Il existe de très nombreuses techniques pour extraire des données d'une base Excel. Il s'agit notamment des filtres, des requêtes, des fonctions de bases de données Excel (Données / grille), des TCD, des formules matricielles.
La macro proposée ici au téléchargement n'apporte pas de progrès particulier pour l'utilisateur de ces différentes techniques. Elle permet simplement d'effectuer l'opération d'extraction avec plus de confort, en offrant la possibilité de modifier à volonté l'échantillon en cours de constitution. Elle n'est pas liée à une base de donnée particulière et peut pointer vers n'importe quel fichier Excel.
Elle est basée sur l'utilisation de listbox en cascade.
Un premier listbox permet de choisir le champ (colonne) sur lequel va s'effectuer le choix (par exemple, dans le cas d'un fichier d'adresses, les nom, prénom, rue, code postal, ville...).
Quand une colonne est choisie avec ce premier listbox (par exemple le prénom), la liste des valeurs que le champ peut prendre (par exemple, Amélie, Juliette, Rosalie, Caroline, Evelyne, Martine...) est affichée dans un second listbox.
Quand un choix est fait (par exemple Amélie), la macro affiche dans un troisième listbox les noms de toutes les Amélie de la base.
Quand l'une d'entre elle est sélectionnée, elle est transférée dans un quatrième listbox (avec possibilité de revenir en arrière).
On peut ajouter de la même façon d'autres fiches sélectionnées sur la base d'autres critères.
L'échantillon ainsi constitué est inscrit dans un onglet, avec possibilité de sauvegarde sous forme de fichier indépendant.


Principaux éléments de la macro :

Première étape, le choix du fichier contenant la base de données
On affiche la boîte de dialogue "ouvrir" avec Application.GetOpenFilename qui permet d'intercepter le clic sur "annuler" par If choixfich = False (et éventuellement de vérifier avant de l'ouvrir que le fichier cible n'est pas déjà ouvert) :
choixfich = Application.GetOpenFilename("Fichiers Microsoft Excel (*.xls), *.xls", , "choix du fichier contenant la base de données")
If choixfich = False Then Exit Sub
Workbooks.Open(choixfich)
Si on veut vérifier si le fichier cible est ouvert, il suffit de remplacer la dernière ligne par :
ouvert=False
For Each fich In Workbooks
If fich.FullName = choixfich Then ouvert = True
Next
If ouvert = False Then Workbooks.Open (choixfich)

Mise à jour des listes
1) La technique la plus simple de mise à jour des listbox consiste à les lier à une plage d'une feuille Excel (qu'on pourra masquer). Il suffit de passer en mode création (clic sur l'icone correspondante dans la barre d'outils VBA ou bien à partir de l'éditeur VBA, Execution / mode création), puis de double cliquer sur le listbox.
Affichage / Fenêtre Propriétés permet d'accéder aux propriétés du listbox.
Si la plage liée doit être la plage A1:A200 de la page secteurs du classeur contenant le listbox, il suffit d'inscrire l'adresse de la plage sous forme secteurs!A1:A200 dans la case correspondant à ListFillRange.
A chaque modification de la plage, le listbox se met automatiquement à jour.
Petit souci de perfectionniste toutefois, si le nombre de lignes remplies change et si on ne souhaite pas ajouter un grand nombre de lignes vides dans le listbox ?
On peut toujours mettre à jour la plage liée par :
derlin = ThisWorkbook.Sheets("secteurs").Columns(1).Find("*", , , , , xlPrevious).Row
ThisWorkbook.Sheets(1).ListBox1.ListFillRange = "secteurs!A1:A" & derlin
(la première ligne détermine derlin, la dernière ligne non vide de la colonne 1 de la page "secteurs", la deuxième affecte la nouvelle plage liée : "secteurs!A1:A" & derlin (le listbox est situé dans la première feuille du classeur contenant la macro).

2) Une autre solution de mise à jour des listes consiste à ajouter ou retirer des éléments à la liste :
ThisWorkbook.Sheets(1).ListBox3.AddItem "toto" va ajouter l'élément toto à la liste.
Pour ajouter à la liste 3 l'élément séléctionné dans la liste 1 :
ThisWorkbook.Sheets(1).ListBox3.AddItem ThisWorkbook.Sheets(1).ListBox1.Value .
Pour supprimer l'élément sur lequel on a cliqué dans la liste ListBox3 :
Private Sub ListBox3_click()
lin = ListBox3.ListIndex
If MsgBox("supprimer " & ListBox3.Text & " ?", vbYesNo) = vbNo Then Exit Sub
ListBox3.RemoveItem (lin)
End Sub
Ou bien pour vider tous les éléments de la liste ListBox4 :
nouveau:
If ThisWorkbook.Sheets(1).ListBox4.ListCount > 0 Then
ThisWorkbook.Sheets(1).ListBox4.RemoveItem (0)
GoTo nouveau
End If
Si la liste n'est pas liée à des données, on peut utiliser ThisWorkbook.Sheets("Feuil1").ListBox4.Clear pour vider la liste.
(Remarque : attention, les lignes du listbox sont numérotées à partir de zéro, RemoveItem(0) retire la première ligne).


Cliquez ici pour télécharger la macro (fichier zip 69 ko contenant une base de données exemple et la macro).