Charger une liste déroulante de valeurs externes

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


Sujets que vous pourriez aussi aimer :


Charger une liste déroulante de valeurs externes

Nous avions déjà appris à créer des formulaires interactifs avec Word. Mais lorsque certaines informations à renseigner sont suggérées sous forme de listes déroulantes, capables de s'enrichir automatiquement des dernières données, c'est un confort appréciable pour l'utilisateur et un gain de temps non négligeable.

Données externes
Pour la mise en place de cette nouvelle astuce VBA Word, nous proposons tout d'abord de récupérer des données externes hébergées par un classeur Excel, auquel il s'agira de se connecter.

• Télécharger le classeur source.xlsx en cliquant sur ce lien,
• Puis, double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,

Très simplement, nous découvrons une feuille Excel dépouillée. En colonne A, elle propose une énumération de quelques départements. Et cette liste est susceptible de s'enrichir à tout moment. C'est à ces données dynamiques que nous devons nous connecter par le code VBA Word pour remplir le contenu d'une liste déroulante de choix, afin de toujours proposer les dernières informations à l'utilisateur.

Contrôles de formulaire
Pour débuter simplement, nous devons commencer par ajouter une liste déroulante sur un nouveau document. Word livre des objets ActiveX qui offrent de nombreuses propriétés faciles à piloter par le code VBA.

• Fermer le classeur Excel en cliquant sur la croix de sa fenêtre,
• Démarrer Word et créer un nouveau document vierge,
• En haut de la fenêtre Word, cliquer sur l'onglet Développeur pour activer son ruban,

S'il n'est pas accessible dans votre environnement, vous pouvez le rendre disponible facilement. Pour cela, vous devez d'abord réaliser un clic droit n'importe où sur le ruban actif. Dans le menu contextuel, vous devez choisir l'option Personnaliser le ruban. Dans la liste de droite de la boîte de dialogue qui suit, vous devez cocher la case Développeur. Puis, vous devez revenir sur le document en validant ce réglage par le bouton Ok en bas de la boîte de dialogue.

• Dans la section Contrôles du ruban, cliquer sur le bouton Outils hérités,
• Dans Contrôles ActiveX des propositions, cliquer sur le contrôle Zone de liste déroulante,

La liste déroulante s'insère aussitôt à l'emplacement du point d'insertion sur le document. Vous pouvez la redimensionner à votre guise grâce aux poignées qui l'encadrent.

• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Propriétés,

Aussitôt, la fenêtre propriétés apparaît. Elle offre tous les réglages qu'il est possible de paramétrer pour la liste déroulante active.

• Dans la zone de sa propriété (Name), remplacer l'intitulé Combobox1 par liste,
• Puis, valider ce nom par la touche Entrée du clavier,

Des noms explicites sont importants. Ce sont ces noms d'objets qui permettent de piloter facilement ces contrôles par le code VBA.

• Fermer la fenêtre Propriétés en cliquant sur sa croix,
• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Mode création,

Ainsi, nous désactivons le mode conception pour exploiter la liste en production.

• Enregistrer le document Word (en Docm) dans le même dossier que le classeur Excel,

De cette manière, il sera plus simple de pointer sur la source de données externes par chemin relatif.

Code VBA à l'ouverture du document
Nous devons maintenant envisager de créer une procédure de code VBA capable de se déclencher à l'ouverture du document. C'est ainsi que nous pourrons ordonner d'établir la liaison avec la source Excel pour toujours proposer les informations au dernier indice.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Word,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément ThisDocument,

Ainsi, nous affichons la feuille de code associée au document actif au centre de l'écran.

• En haut de cette feuille, déployer la liste déroulante de gauche,
• Cliquer alors sur l'objet Document,

Cette action a pour effet de créer la procédure événementielle Document_New. Ce n'est pas celle qui nous intéresse pour surgir au démarrage.

• Dans la liste de droite, choisir l'événement associé Open,

C'est ainsi que nous créons la procédure Document_Open. C'est elle qui va déclencher le code qu'elle contient à l'ouverture du document. De fait, la procédure Document_New peut être effacée.

Ajouter la référence à Excel
Maintenant, pour pouvoir communiquer avec Excel, nous devons faire référence à une librairie qui le représente. Cette librairie offre des classes que nous allons instancier. Ces instanciations vont livrer des objets permettant de piloter le classeur à distance dans Excel et depuis Word.

• En haut de la fenêtre de l'éditeur, cliquer sur le menu Outils,
• En haut des propositions, choisir Références,
• Dès lors, cocher la case Microsoft Excel 16.0 Object Library,

Toutes ces librairies sont classées par ordre alphabétique.





Piloter les objets Excel
Désormais, nous devons instancier certaines classes de cette librairie pour créer les objets nécessaires. Nous avons besoin d'un objet pour piloter Excel et d'un autre pour prendre le contrôle du classeur des données source dans l'application Excel.

• Dans les bornes de la procédure, déclarer les variables suivantes :

...
Dim fenetre As Excel.Application: Dim classeur As Excel.Workbook
Dim chemin As String: Dim compteur As Byte
...

Grâce à cette référence ajoutée, nous créons tout d'abord un objet pour représenter l'application Excel. Nous le nommons fenetre. Puis, nous créons un autre objet représentant un classeur. Nous le nommons classeur et lors de l'affectation, nous devrons préciser le chemin du fichier cible.

Comme nous sommes dans la phase de déclaration, nous en profitons pour déclarer deux autres variables. La première nommée chemin doit précisément reconstruire le chemin d'accès au classeur des données à récolter pour charger la liste déroulante. Bien sûr nous la typons comme un texte, soit comme un String. Ensuite, nous déclarons une variable de boucle que nous nommons compteur. En effet, nous allons parcourir toutes les données à charger dans la liste. Mais nous partons du principe qu'au-delà d'un certain seuil, nous mettrons fin à la boucle. Nous typons cette variable comme un entier court.

• Maintenant, à la suite du code, ajouter les initialisations et affectations suivantes :

...
liste.Clear
compteur = 1
chemin = ThisDocument.Path & "\source.xlsx"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)
...

La méthode Clear d'un objet de type zone de liste déroulante permet de vider son contenu avant de penser à le recharger. Ensuite, nous initialisons la variable de boucle sur la valeur 1. Puis, nous construisons le chemin relatif d'accès au classeur des données à charger. C'est la propriété Path de l'objet ThisDocument, pour le document actif, qui renseigne sur le chemin du dossier dans lequel il est enregistré. Nous le concaténons avec le nom du classeur Excel sans oublier l'antislash qui indique que nous entrons dans ce dossier pour pointer sur ce fichier. C'est alors qu'interviennent les instanciations. Le mot clé Set est nécessaire pour initialiser des variables objets. La fonction Excel CreateObject permet de créer un objet de programmation à partir d'une classe. Cette classe, disponible grâce à la référence est mentionnée en argument. Notre objet pour piloter Excel est désormais fonctionnel. Nous l'exploitons alors pour descendre jusqu'à la collection des classeurs (Workbooks) qui offre la méthode Open à laquelle nous passons le chemin d'accès. Notre objet pour piloter le classeur cible dans Excel est prêt.

Parcourir les valeurs du classeur
Puisque nos objets existent et que l'un d'entre eux pointe précisément sur le classeur détenant les informations à récolter, nous disposons désormais exactement des mêmes méthodes et propriétés que celles disponibles en VBA Excel. Nous allons donc initier une boucle pour parcourir toutes les cellules de la première colonne du classeur, tant qu'une donnée existe, pour charger les valeurs dans la liste déroulante du document Word.

• A la suite du code de la procédure, ajouter la boucle suivante :

...
Do While classeur.Sheets("Feuil1").Range("A" & compteur).Value <> ""
liste.AddItem classeur.Sheets("Feuil1").Range("A" & compteur).Value
compteur = compteur + 1
If (compteur > 200) Then Exit Do
Loop
...

Nous enclenchons une boucle Do While destinée à poursuivre son traitement tant que son critère est vrai. Pour ce critère, nous utilisons l'objet classeur qui permet d'atteindre sa collection de feuilles. Nous lui passons le nom de celle qui est concernée (Feuil1). En respectant la hiérarchie, nous pouvons désormais accéder à son objet Range qui désigne une plage de cellules à préciser. La colonne est connue, c'est pourquoi nous la passons en valeur statique ("A"). Puis, nous la concaténons avec les lignes que nous passons en revue grâce à la variable compteur à incrémenter (Range("A" & compteur)). Dès lors, c'est la propriété Value qui permet de vérifier si la cellule en cours d'analyse porte encore une donnée (Value <> "").

De fait, tant que la condition est respectée, nous exploitons la méthode AddItem de l'objet zone de liste déroulante pour injecter le contenu de la cellule en cours d'analyse dans la liste déroulante du document Word. Bien sûr, nous n'oublions pas d'incrémenter la variable compteur (compteur = compteur + 1) pour porter l'analyse sur la cellule de la ligne suivante au prochain passage. Et nous ajoutons un garde-fou arbitraire (If (compteur >200) Then Exit Do) pour sortir de la boucle quand nous estimons que les passages sont trop importants.

Fermer l'application et vider les objets
Avant de tester le bon fonctionnement de la connexion et de la récupération des informations, nous devons coder proprement. Nous devons donc fermer les objets ouverts pour libérer la mémoire et nous devons même les détruire pour qu'aucun parasite ne subsiste.

• A la suite du code VBA, donc après la boucle, ajouter les instructions suivantes :

...
classeur.Close
fenetre.Quit

Set classeur = Nothing
Set fenetre = Nothing
...

Nous exploitons donc les méthodes Close et Quit des objets respectifs. Et surtout, grâce au mot clé Set qui avait servi à l'instanciation, nous l'exploitons cette fois pour vider complètement ces objets en les affectant à la valeur Nothing. En d'autres termes, nous les détruisons.

• Enregistrer le travail (CTRL + S),
• Puis, pour une simulation, fermer le document et le rouvrir,

En déployant la liste déroulante, vous pouvez constater qu'elle s'est parfaitement remplie des données émanant de la feuille Excel.

• Fermer de nouveau le document Word puis double cliquer sur le classeur Excel pour l'ouvrir,
• En cellule A7, ajouter le département suivant : 84-Vaucluse,
• Puis, fermer le classeur en l'enregistrant et rouvrir le document Word,

Comme vous pouvez l'apprécier, la liste déroulante du document Word s'est enrichie en même temps que la source de données Excel a évolué.