Listes Access liées sur des données Excel

Accueil > Bureautique > Access > Access VBA > Listes Access liées sur des données Excel

Livres à télécharger

‹ ›

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

Sujets que vous pourriez aussi aimer :

Listes liées de valeurs Excel en VBA Access

Nous avons déjà appris à relier des listes déroulantes entre elles, que ce soit avec Excel, Access ou même Word. Mais ce qu'il y a de plus étonnant et c'est ce que nous allons prouver, il est possible d'enrichir et d'articuler des listes déroulantes sur un formulaire Access, sans que sa base n'héberge la moindre donnée. Ce prodige est rendu possible par communication avec Excel, en allant piocher les informations dynamiques sur les feuilles d'un classeur.

Listes déroulantes liées de données Excel sur un formulaire Access

Listes déroulantes liées de données Excel sur un formulaire Access

Sur l'exemple illustré par la capture, malgré l'absence totale d'informations dans cette base, lorsque l'utilisateur déploie la première liste déroulante du formulaire, des départements lui sont proposés. Au choix de l'un d'entre eux, une seconde liste déroulante se nourrit des activités de sorties qui sont recensées dans ce département. Dès lors, au choix de l'une de ces activités, c'est une troisième et dernière liste déroulante qui suggère les villes proposant ces activités de sorties dans le département défini en amont. C'est ainsi que les enregistrements concordants sont livrés dans un sous-formulaire, sous les listes déroulantes. Cette partie, nous la développerons dans un second temps. Ici, l'objectif est d'articuler des listes déroulantes sur un formulaire Access à partir de données externes.

Base de données Access à télécharger
Pour aboutir ces travaux, nous suggérons de porter l'étude sur un formulaire Access offrant ces listes déroulantes, accompagné d'un classeur Excel embarquant les données à piloter.

Télécharger le fichier compressé listes-access-liees-avec-excel.rar en cliquant sur ce lien,
Puis le décompresser dans le dossier de votre choix,

Comme vous pouvez le constater, la décompression livre deux fichiers. Il s'agit de celui de la base de données Access et de celui du classeur Excel.

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é,

Comme l'indique le volet de navigation sur la gauche, cette base de données est constituée d'une table (t_sorties) et d'un formulaire (f_villes).

Double cliquer sur la table t_sorties pour l'afficher en mode feuille de données,

Comme vous pouvez le voir et comme nous l'avons annoncé cette table est vide, prouvant que cette base est dépourvue de données.

Fermer la table en cliquant sur la croix de son onglet,
Puis, double cliquer sur le formulaire f_villes pour l'exécuter,

Ce dernier est effectivement pourvu de trois listes déroulantes à articuler ainsi que d'un sous formulaire pour restituer les données correspondant aux choix entonnoirs. Mais là encore, aucune de ces zones ne transporte la moindre information.

Listes déroulantes de formulaire Access à relier entre elles avec des données Excel

Listes déroulantes de formulaire Access à relier entre elles avec des données Excel

Les données Excel à articuler
Nous proposons maintenant de découvrir à quel endroit cette application Access peut se nourrir des informations pour donner vie à ce formulaire.

Dans le dossier de décompression, double cliquer sur le fichier source-excel-listes-liees.xlsx,

C'est la seconde feuille de ce classeur qui nous intéresse ici. Elle est nommée listes.

Trois listes déroulantes reliées en cascade sur feuille Excel

Trois listes déroulantes reliées en cascade sur feuille Excel

Trois listes déroulantes sont déjà articulées entre elles en cellules respectives B5, C5 et D5. En fonction des choix émis en cascade, les données sources alimentant ces listes déroulantes, s'actualisent sur la droite, dans les colonnes J et K à partir de la ligne 5. Elles se nourrissent des informations de la première feuille, nommée bd_sorties. Vous l'avez compris, c'est avec ces plages dynamiques que nous devons communiquer depuis Access pour nourrir les trois listes déroulantes à articuler sur le formulaire.

Le code VBA existant
Nous allons le découvrir, quelques procédures VBA existent déjà.

Fermer le classeur Excel et revenir sur le formulaire Access,
A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
Dans les propositions, choisir le mode Création,

Nous basculons ainsi dans la vue en conception du formulaire.

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

Nous découvrons tout d'abord la présence des trois procédures déclenchant un code VBA au changement de valeur dans l'une ou l'autre liste déroulante : villes_Change, activites_Change et dep_Change.

La première appelle la procédure effacer. Cette dernière supprime toutes les données précédemment chargées dans la table t_sorties, par les actions réalisées en amont avec les autres listes déroulantes. En effet, chaque nouveau choix doit actualiser les données servant de source au sous formulaire, sous les trois listes déroulantes. Les deux suivantes réinitialisent simplement les listes dépendantes au choix d'une donnée dans une liste parent. Enfin, la procédure Form_Load s'exécute au chargement du formulaire. Elle aussi réinitialise la table et les listes déroulantes.

Comme nous l'avons appris à l'occasion d'une astuce passée, une référence est nécessaire pour pouvoir piloter un classeur Excel par le code VBA Access. Elle a déjà été implantée.

En haut de l'éditeur, cliquer sur le menu Outils,
Dans les propositions, choisir l'option Références,

Ajouter la référence à Excel dans le projet VBA Access

Comme vous pouvez le voir, la case de la référence à Microsoft Excel est déjà cochée. Elle est effectivement absolument nécessaire pour ce développement.

Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille de code,

Procédure de remplissage
Nous proposons de créer une procédure VBA indépendante pour remplir les trois listes déroulantes sur appel, en fonction du nom du contrôle à charger, passé en paramètre.

Sous la procédure effacer, créer la procédure charger_liste, comme suit :

Sub charger_liste(colonne As Byte, controle As ComboBox)
Dim fenetre As Excel.Application: Dim classeur As Excel.Workbook: Dim feuille As Excel.Worksheet
Dim chemin As String: Dim ligne As Byte

End Sub

Nous la déclarons avec deux paramètres en attente. Le nom de la liste déroulante à charger arrive en deuxième position. En premier argument, c'est la variable colonne (entier court) qui est attendue. En effet, selon la liste déroulante à remplir, les données à puiser sur la feuille Excel ne sont pas situées dans la même colonne. Dès lors et grâce à la référence ajoutée au projet, nous déclarons trois objets permettant respectivement de piloter une application Excel, un classeur dans cette application et plus précisément enfin, une feuille dans ce classeur.

Enfin,nous déclarons une variable (chemin) pour mémoriser le chemin d'accès au classeur et une autre (ligne) pour parcourir les lignes de sa feuille.

Initialiser les objets Excel
Maintenant que les variables sont définies, nous allons notamment pouvoir initialiser les objets permettant de prendre possession du classeur Excel et de ses éléments.

Après la déclaration des variables, ajouter les instructions VBA suivantes :

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

Quelle que soit la liste déroulante à remplir, le prélèvement commence à partir de la ligne 5 sur la feuille liste du classeur Excel. C'est la colonne qui change, I (9) pour les départements, J (10) pour les activités et K (11) pour les villes.

La propriété enfant Path de la propriété CurrentProject de l'objet Application renvoie le chemin d'accès à la base de données en cours. Nous lui associons (&) le nom du fichier Excel à piloter. Précisément, nous créons ensuite une nouvelle instance d'Excel pilotée par notre objet fenetre. Grâce à elle et à la méthode Open de la collection Workbooks, nous désignons le classeur (chemin) que la variable classeur pilote désormais. Puis, nous faisons pointer notre objet feuille sur la feuille nommée listes grâce à la collection héritée Worksheets de notre objet classeur. Pour que les traitements se réalisent en tâche de fond, nous choisissons de ne pas afficher la fenêtre Excel (Visible = False).

Parcourir les cellules de la colonne
Pour chaque numéro de colonne passé en paramètre, nous devons maintenant analyser toutes les cellules qu'elle contient, tant qu'elles ne sont pas vides.

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

...
Do While feuille.Cells(ligne, colonne).Value <> ""
controle.AddItem feuille.Cells(ligne, colonne).Value
ligne = ligne + 1
Loop
...

A partir de la ligne 5 et pour la colonne transmise (Cells(ligne, colonne)), tant que la cellule n'est pas vide (Value <> ""), nous ajoutons la valeur qu'elle porte (AddItem) dans la liste déroulante dont le nom (controle) est passé en paramètre de la procédure. Bien sûr, pour poursuivre l'étude sur les cellules du dessous, nous n'oublions pas d'incrémenter notre variable à chaque passage (ligne = ligne + 1).

Décharger les objets de la mémoire
Pour coder proprement, nous devons détruire les objets qui ne sont plus utilisés à l'issue du traitement.

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

...
fenetre.Quit
Set fenetre = Nothing
Set classeur = Nothing
Set feuille = Nothing
...

C'est la réinitialisation (Set) à Nothing qui permet de détruire chacun de ces objets.

Charger les départements
Pour que cette procédure soit exécutée, elle doit être appelée, à de multiples occasions d'ailleurs. Son premier appel doit intervenir au chargement du formulaire pour remplir la première liste déroulante (dep) des départements énumérés dans la feuille Excel.

Dans la procédure Form_Load, ajouter l'appel suivant :

Private Sub Form_Load()
effacer
dep.RowSource = "": activites.RowSource = "": villes.RowSource = ""

charger_liste 9, dep
End Sub

Les départements sont listés en colonne I de la feuille listes. Il s'agit donc de la neuvième colonne. Nous passons cette information à la procédure en premier paramètre (9) ainsi que le nom de la liste déroulante (dep) à charger en second argument.

Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
Puis, Exécuter ce dernier en enfonçant la touche F5 du clavier par exemple,
Dès lors, déployer la première liste déroulante,

Charger une liste déroulante de formulaire Access à partir de données issues d-Excel

Comme vous pouvez le constater, bien que cette base de données soit dépourvue de données, cette première liste est remplie de nombreux départements. Nous les avons récupérés depuis la feuille Excel avec succès.

Charger les activités et les villes
Maintenant, à chaque fois qu'un département est choisi, la liste des activités doit s'adapter. Et dès qu'une activité est sélectionnée avec la deuxième liste déroulante, la liste des villes concordant avec le département et l'activité doit être actualisée. Nous devons donc réaliser l'appel de la même procédure aux changements de valeurs dans ces listes déroulantes.

Revenir dans l'éditeur VBA Access,
Adapter le code de la procédure dep_change comme suit :

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

charger_liste 10, activites
End Sub

Cette fois, au changement de département, c'est la liste des activités que nous adaptons. Ces informations sont situées en colonne J (10) de la feuille Excel.

Puis, adapter le code de la procédure activités_change comme suit :

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

charger_liste 11, villes
End Sub

Au changement d'activité, nous chargeons la troisième liste déroulante (villes) des villes situées en colonne K (11) de la feuille Excel.

Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + Tab),
Choisir un département avec la première liste déroulante,
Puis, déployer la deuxième liste déroulante,

Remplir une liste déroulante Access depuis Excel par le code VBA

Comme vous pouvez le voir, la deuxième liste réagit et offre des activités. Mais s'agit-il vraiment des activités disponibles dans le département désigné en amont ? C'est toute la question.

Choisir une activité avec la deuxième liste déroulante,
Puis, déployer la troisième liste déroulante,

Listes déroulantes pas encore reliées entre elles sur un formulaire Access

Là encore cette troisième liste réagit et se remplit des données Excel. Mais un problème saute aux yeux. Il ne s'agit pas des villes du département choisi en amont. Sur l'exemple illustré par la capture, Bandol par exemple, n'est pas une ville de la Drôme mais une ville du Var.

Adapter les contenus des listes déroulantes
La raison est toute simple. Nous travaillons sur des plages figées. Pour que les activités s'adaptent au département et que les villes s'adaptent au département et à l'activité, nous devons influer sur les valeurs des cellules B5, C5 et D5. C'est en fonction des choix dans ces zones que ces plages réagissent. En d'autres termes, nous devons y inscrire les choix émis par l'utilisateur depuis le formulaire Access. Pour cela, nous proposons encore une fois de créer une procédure adaptative. Elle doit attendre la valeur à inscrire ainsi que l'indice de colonne de la cellule qui doit recevoir l'information. En effet, la ligne est déjà connue. Il s'agit de la ligne 5.

Revenir dans l'éditeur VBA Access (ALT + F11),
Sous la procédure charger_liste, créer la procédure changer_valeur comme suit :

Sub changer_valeur(colonne As Byte,valeur As String)
Dim fenetre As Excel.Application: Dim classeur As Excel.Workbook: Dim feuille As Excel.Worksheet
Dim chemin As String: Dim ligne As Byte

End Sub

Nous déclarons exactement les mêmes variables que pour la procédure précédente. En effet, nous avons besoin de piloter le classeur et ses feuilles et nous avons besoin de pointer sur une ligne précise. En revanche, les paramètres en attente de la procédure ne sont pas tout à fait identiques. Le premier permet de désigner la colonne où réside la cellule à implémenter. Le second, issu du choix dans la liste, indique la valeur à y inscrire.

Piloter le classeur Excel
Comme nous l'avons fait précédemment, nous devons commencer par initialiser les variables objets pour piloter le classeur Excel.

Après la déclaration des variables, ajouter les instructions VBA suivantes :

A la suite du code, ajouter les instructions VBA suivantes :

...
feuille.Cells(ligne, colonne).Value = valeur
classeur.Save
fenetre.Application.DisplayAlerts = False
...

C'est la méthode Save d'un objet de type Workbook qui permet d'ordonner la sauvegarde. Et pour qu'aucun message de confirmation n'apparaisse à l'écran, nous réglons la propriété enfant DisplayAlerts de notre objet Excel à False.

Détruire les objets
Avant de réaliser l'appel de cette procédure, comme précédemment, nous devons détruire les objets pour libérer la mémoire.

A la fin de la procédure, ajouter les lignes VBA suivantes :

...
fenetre.Quit
Set fenetre = Nothing
Set classeur = Nothing
Set feuille = Nothing
...

Relier les listes déroulantes
Pour que les listes déroulantes réagissent désormais entre elles, nous devons appeler cette nouvelle procédure à deux reprises, au changement de département et au changement d'activité. Mais bien sûr, cet appel doit intervenir avant celui de la procédure charger_liste.

Adapter le code de 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
End Sub

Puis, adapter le code de la procédure activités_change comme suit :

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

changer_valeur 3, activites.Value
charger_liste 11, villes
End Sub

Dans le premier cas, nous indiquons d'agir en colonne deux (B) et de transmettre la valeur du nouveau département pour que la liste des activités réagisse automatiquement sur la feuille Excel. Dans le second cas, nous indiquons d'agir en colonne trois (C) et de transmettre la nouvelle activité pour que la liste des villes réagisse instantanément.

Enregistrer les modifications et revenir sur le formulaire Access,
Choisir un département avec la première liste déroulante,
Choisir une activité dépendante avec la deuxième liste déroulante,
Puis, choisir une ville proposant une activité dans ce département avec la troisième,

3 listes déroulantes reliées entre elles sur un formulaire Access à partir de données Excel pilotées en VBA

Cette fois et comme vous pouvez l'apprécier, tout est cohérent. Nous débouchons bien sur les villes du département choisi en amont et proposant bien l'activité désignée dans un second temps. Nous avons donc réussi à relier des listes déroulantes sur un formulaire Access, à partir de données issues d'un classeur Excel. Sur cette base et dans le prochain volet, nous verrons comment extraire les activités de sorties, toujours issues du classeur Excel, correspondant strictement à ces choix entonnoirs.

Sur Facebook

Sur Youtube

Les livres

Contact

Mentions légales

Toutes les vidéos de formation en informatique

Les livres de formation du Formateur à télécharger