Extractions sélectives d'Excel en VBA Access

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


Sujets que vous pourriez aussi aimer :


Extractions sélectives sur données externes

Dans le volet précédent, nous avons appris à enrichir et relier des listes déroulantes entre elles, à partir de données externes, plus précisément issues d'une feuille d'un classeur Excel. Dans cette suite logique, sur ces choix recoupés, nous allons voir comment restituer les informations filtrées répondant favorablement aux choix recoupés par les listes déroulantes.



Sur l'exemple illustré par la capture, l'utilisateur choisit un département, une activité puis une ville à l'aide de trois listes déroulantes reliées en cascade. Presque aussitôt, toutes les informations concordantes, issues d'un classeur Excel, sont restituées dans un sous-formulaire.

Base de données Access à télécharger
Pour poursuivre les travaux, nous devons récupérer ceux aboutis à l'occasion du volet précédent.

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

Sans surprise, nous récupérons le fichier de la base de données accompagné du classeur Excel hébergeant les données à piloter depuis un formulaire Access.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet de navigation, double cliquer sur le formulaire f_villes pour l'exécuter,
• Puis, déployer la première liste déroulante, celle des départements,

Comme vous le constatez, elle est préremplie de certains départements. Ces informations sont issues de la seconde feuille du classeur Excel qui accompagne cette base de données.

• Cliquer sur l'un de ces départements pour le choisir,
• Déployer alors la deuxième liste déroulante,

Par choix recoupés sur la feuille Excel, elle offre uniquement les activités qui sont recensées pour le département choisi en amont.

• Déployer alors la troisième liste déroulante,

Par restrictions en cascade, elle présente uniquement les villes du département proposant l'activité choisie.

La base de données Excel
Maintenant, à chaque choix opéré par l'utilisateur, nous devons restituer les activités de sorties concordantes, dans le sous-formulaire placé sous les listes déroulantes. Là encore, les données sont à piocher dans le classeur Excel, mais plus sur la même feuille.

• A la racine du dossier de décompression, double cliquer sur le fichier Excel pour l'ouvrir,

Nous débouchons sur la seconde feuille de ce classeur. Elle est nommée listes. C'est elle que nous avons questionnée en VBA lors du volet précédent, pour articuler les listes déroulantes sur le formulaire Access.

• En bas de la fenêtre Excel, cliquer sur le premier onglet pour afficher sa feuille,

Elle est nommée bd_sorties. C'est elle qui accueille la base des informations que nous allons devoir sonder à la recherche des activités de sorties concordant avec les choix recoupés par l'utilisateur depuis le formulaire Access.

Comme vous le remarquez, la structure de cette table est spéciale. Les informations à récolter sont inscrites une colonne sur deux. C'est ainsi que le nom, le département, l'activité et la ville sont respectivement renseignés en colonnes 2, 4, 6 et 8. Ces indices sont importants. Nous en aurons besoin pour pointer sur les bonnes colonnes par le code VBA Access.

Procédure VBA de remplissage
Pour réaliser cette extraction chirurgicale, nous proposons de créer une procédure unique et indépendante. Elle devra donc être appelée à chaque changement de valeur dans l'une ou l'autre liste déroulante. C'est elle qui doit détecter si les trois listes sont à recouper ou seulement les deux premières ou encore s'il s'agit de ne considérer que le département. Tout dépend de la progression dans les choix opérés par l'utilisateur sur le formulaire Access.

• Fermer le classeur Excel puis revenir sur le formulaire Access,
• Réaliser ensuite le raccourci clavier ALT + F11 pour revenir dans l'éditeur VBA Access,

Nous découvrons ainsi le code VBA du développement précédent, celui qui a permis d'articuler ces listes déroulantes sur la base des données du classeur Excel.

• Sous la procédure changer_valeur, créer la procédure remplir comme suit :

Sub remplir()
Dim fenetre As Excel.Application: Dim classeur As Excel.Workbook: Dim feuille As Excel.Worksheet
Dim chemin As String: Dim ligne As Integer
Dim base As Database: Dim requete As String

End Sub

Comme nous l'avons fait dans le volet précédent, nous déclarons trois objets pour piloter une instance d'Excel, dans cette instance un classeur précis et dans ce classeur, une feuille précise. De la même façon aussi, les deux variables qui suivent doivent permettre de stocker le chemin d'accès au classeur et de parcourir les lignes de la feuille. Par contre et c'est nouveau, comme nous avons besoin d'insérer des données dans la table t_sorties, nous devons être en capacité de piloter la base de données en cours. En effet, cette table est la source de données du sous-formulaire sur le formulaire Access. Donc, le premier objet doit permettre de piloter la base de données en cours tandis que la variable doit construire la syntaxe de la requête SQL pour insérer des données.

Piloter la base Access et le classeur Excel
Maintenant que les variables existent, nous pouvons les initialiser pour prendre possession de la base de données Access et du classeur Excel. Rappelons néanmoins que ces déclarations ont été rendues possibles grâce à la référence Microsoft Excel ajoutée au projet. Nous l'avions expliqué à l'occasion du volet précédent.

• Après la déclaration des variables, ajouter les initialisations suivantes :

...
Set base = CurrentDb
ligne = 2
chemin = Application.CurrentProject.Path & "\source-excel-listes-liees"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)
Set feuille = classeur.Worksheets("bd_sorties")
fenetre.Visible = False
...

Tout d'abord, grâce à la fonction VBA Access CurrentDb, nous initialisons (Set) notre objet base sur la base de données active. Nous calons la variable ligne sur l'indice de la première ligne à scruter dans la base de données Excel. C'est alors la fonction VBA CreateObject, avec le paramètre Excel.Application qui permet d'instancier la classe Excel via l'objet (Set) fenetre. Ainsi, grâce à sa collection Workbooks et à sa méthode Open, nous définissons le classeur (chemin) à piloter. De cette manière, l'objet classeur (Set) hérite des propriétés et méthodes pour entrer dans le coeur de ses éléments. Pour preuve, sa collection Worksheets permet de désigner la feuille (bd_sorties) à analyser. Grâce à l'objet feuille (Set), nous pourrons donc pointer sur ses cellules. Enfin, la propriété Visible réglée à False, pour l'instance d'Excel, permet d'exécuter ces actions en tâche de fond, pour ne jamais ouvrir la moindre fenêtre d'un classeur. Tout se produira en mémoire.

Récolter les informations Excel
Maintenant que les objets sont initialisés, à partir de la deuxième ligne, nous devons parcourir toutes les informations de la base de données Excel, tant qu'il existe des données, donc tant que la cellule n'est pas vide dans la première colonne (B donc 2). Et pour cela, nous devons engager une boucle Do While.

• A la suite du code VBA, créer la boucle Do While suivante :

...
Do While feuille.Cells(ligne, 2).Value <> ""
requete = "INSERT INTO t_sorties (s_rs, s_dep, s_act, s_ville) VALUES ('" & Replace(feuille.Cells(ligne, 2).Value, "'", "-") & "','" & feuille.Cells(ligne, 4).Value & "','" & Replace(feuille.Cells(ligne, 6).Value, "'","-") & "','" & Replace(feuille.Cells(ligne,8).Value, "'", "-") & "')"

ligne = ligne + 1
Loop
...

A partirde la ligne 2 et de la colonne B (2), nous parcourons toutes les activités de la base de données Excel sur la feuille nommée bd_sorties. Nous construisons la requête d'insertion (INSERT INTO) pour intégrer les informations de la feuille Excel dans les champs de la table Access. Mais celle-ci ne doit être exécutée que dans la mesure où certains critères sont rencontrés. Nous devons vérifier les listes déroulantes qui sont actionnées sur le formulaire Access. Bien sûr, nous n'oublions pas d'incrémenter la variable ligne (ligne = ligne + 1) à chaque passage, pour déplacer le pointeur de l'analyse sur les lignes suivantes de la base de données Excel.

Nous devons maintenant précisément gérer les contextes pour savoir à quelle occasion la requête insertion doit être exécutée. Tout dépend des listes déroulantes actionnées par l'utilisateur sur le formulaire. C'est donc un ensemble d'instructions conditionnelles que nous devons activer.

• Dans la boucle et avant l'incrémentation de la variable, ajouter la multiple instruction conditionnelle suivante :

...
requete = "INSERT INTO t_sorties (s_rs, s_dep, s_act, s_ville) VALUES('" & Replace(feuille.Cells(ligne, 2).Value, "'", "-") & "','" & feuille.Cells(ligne, 4).Value & "','" & Replace(feuille.Cells(ligne, 6).Value, "'","-") & "','" & Replace(feuille.Cells(ligne,8).Value, "'", "-") & "')"

If (dep.Value <> "" And activites.Value <> "" And villes.Value <> "") Then
If (feuille.Cells(ligne, 4).Value = dep.Value And feuille.Cells(ligne, 6).Value = activites.Value And feuille.Cells(ligne, 8).Value = villes.Value) Then
base.Execute requete
End If
ElseIf (dep.Value <> "" And activites.Value <>"") Then
If (feuille.Cells(ligne, 4).Value = dep.Value And feuille.Cells(ligne, 6).Value = activites.Value) Then
base.Execute requete
End If
Else
If (feuille.Cells(ligne, 4).Value = dep.Value) Then
base.Execute requete
End If
End If

ligne = ligne + 1
...

Lorsque les trois listes déroulantes (dep.Value <> "" And activites.Value <> "" And villes.Value <> "") sont renseignées, dans la base de la feuille Excel, nous cherchons à trouver chaque correspondance avec ces choix entonnoirs (feuille.Cells(ligne, 4).Value = dep.Value And feuille.Cells(ligne, 6).Value = activites.Value And feuille.Cells(ligne, 8).Value = villes.Value). Dès que l'une d'entre elles est avérée, nous ajoutons sa ligne en exécutant la requête Sql (base.Execute requete). Lorsque seules les deux premières listes déroulantes sont renseignées, nous cherchons les correspondances sur ces deux premiers choix pour autoriser l'exécution de la requête. Dans le cas restant (Else), nous cherchons simplement la correspondance sur le département pour exécuter la requête SQL.

Libérer la mémoire
Désormais, après la boucle, donc une fois que toutes les données ont été passées en revue, nous devons détruire les variables objets pour libérer la mémoire de l'ordinateur.

• Après la boucle, ajouter les instructions VBA suivantes :

...
Loop

DoCmd.Requery

base.Close
fenetre.Quit
Set base = Nothing
Set fenetre = Nothing
Set classeur = Nothing

End Sub
...

Tout d'abord, nous exploitons la méthode Requery de l'objet DoCmd pour actualiser les liaisons et ainsi rafraîchir l'affichage du sous-formulaire de manière à ce qu'il présente instantanément les données extraites, correspondant aux choix recoupés. Ensuite, nous réinitialisons (Set) chaque objet à Nothing pour les détruire tour à tour.

Extraire sur des choix recoupés
Il ne nous reste plus qu'à tester les extractions réalisées à chaque choix effectué par l'utilisateur par le biais de l'une et l'autre listes déroulantes. Mais pour cela, cette procédure remplir doit être appelée à chaque changement opéré.

• Adapter la procédure dep_Change comme suit :

Private Sub dep_Change()
activites.RowSource = "": villes.RowSource = ""
activites.Value = "": villes.Value = ""

changer_valeur 2, dep.Value
charger_liste 10, activites

effacer
remplir
End Sub

En effet, le processus d'extraction doit intervenir en toute fin, une fois que les listes ont été reliées et que la table, servant de source de données au sous formulaire a été effacée.

• Adapter la procédure activites_Change comme suit :

Private Sub activites_Change()
villes.RowSource = "": villes.Value = ""

changer_valeur 3, activites.Value
charger_liste 11, villes

effacer
remplir
End Sub

• Enfin, adapter la procédure villes_Change comme suit :

Private Sub villes_Change()
effacer
remplir
End Sub

• Enregistrer les modifications et basculer sur le formulaire,
• Avec la première liste déroulante, choisir un département,

Comme vous pouvez l'apprécier, après un temps de traitement et d'échange d'informations avec Excel, ce sont bien toutes les activités de sorties du département demandé qui sont extraites.

• Avec la deuxième liste déroulante, choisir une activité de sortie,

Cette fois la liste des résultats se restreint. Seules subsistent les sorties de l'activité choisie dans le département demandé.

• Avec la dernière liste déroulante, choisir une ville,

Sur cette dernière action, c'est une extraction chirurgicale qui est réalisée. Il ne reste plus que les sorties de l'activité dans la ville appartenant au département demandé en première instance.