Listes liées et connexions externes en VBA Word

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


Sujets que vous pourriez aussi aimer :


Liste liée et connexion externe

A l'occasion de la précédente astuce VBA Word, nous avons vu comment charger dynamiquement une liste déroulante d'un document à partir de données contenues dans un classeur Excel. Ici, nous souhaitons remplir dynamiquement une liste déroulante des villes correspondant au code postal saisi en amont dans une zone de texte. L'objectif est d'améliorer l'ergonomie d'une interface d'accueil.



Sur l'exemple illustré par la capture, l'utilisateur renseigne les informations demandées dont le code postal du lieu de résidence. A validation, il déploie la liste déroulante des villes qui propose seulement les communes associées à ce code inscrit. L'utilisateur n'a plus qu'à choisir pour valider. Et ces informations sont toutes stockées en base de données Access. Il est donc question d'établir la liaison externe en VBA Word au moment de la validation du code postal.

Sources Word et Access - Présentation
Pour la découverte de cette astuce VBA Word, nous proposons d'appuyer l'étude sur des sourcces existantes.

• Télécharger le fichier compressé liaison-liste-deroulante.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 un modèle Word nommé modele-courrier accompagné d'une base de données Access intitulée bd-communes.

• Double cliquer sur le fichier de la base de données pour l'ouvrir dans Access,
• Dans le volet de navigation sur la gauche, double cliquer sur la table les_communes,

Les villes sont référencées en deuxième colonne dans un champ nommé Commune_nom. Les codes postaux sont stockés en troisième colonne dans un champ intitulé Commune_dep. Un même code postal peut représenter plusieurs villes. C'est tout l'intérêt de la liaison que nous souhaitons organiser sur un formulaire Word. La saisie d'un code postal doit instantanément isoler toutes les villes qui lui sont attachées. Pour cela, il va s'agir de questionner cette table avec une requête Sql dirigée par le code VBA. Pour information, seules les villes de la région PACA sont stockées dans cette petite table à l'exception du département du Vaucluse (84).



Cette petite source fera parfaitement l'affaire pour la démonstration de la technique.

• Fermer cette base de données,
• A la racine du dossier de décompression, double cliquer sur le fichier modele-courrier.docm,

Après avoir cliqué sur les boutons des bandeaux de sécurité (Activer la modification et Activer le contenu), vous notez l'apparition automatique d'un formulaire de saisie.



L'utilisateur doit renseigner toutes les informations demandées pour remplir automatiquement un texte à trous, au clic sur le bouton Créer. Il s'agit d'une solution que nous avions développée à l'occasion d'une précédente astuce. C'est la raison pour laquelle du code VBA existe déjà et accompagne ce formulaire. En revanche, si vous tapez un code postal de la région PACA et que vous déployez la liste déroulante des villes, celle-ci apparaît vide à ce stade fort naturellement.

La référence aux bases de données Access
Avant toute chose, pour pouvoir piloter les données d'une base Access, une librairie indispensable doit être ajoutée au projet VBA Word. C'est grâce à elle que nous pourrons hériter des objets de programmation capables d'établir la connexion.

• Fermer le formulaire en cliquant sur le bouton Annuler,
• Réaliser le raccourci ALT + F11 pour basculer dans l'éditeur VBA Word,
• En haut de l'éditeur, cliquer sur le menu Outils,
• Dans les propositions, choisir l'option Références,
• Dans la boîte de dialogue, cocher la référence Microsoft Office 16.0 Access Database...,

Le numéro dépend de votre version (13, 16 ,19 ...).

• Valider cet ajout en cliquant sur le bouton Ok.

Grâce à cette librairie, nous allons pouvoir instancier une classe permettant d'hériter d'objets avec leurs propriétés et méthodes pour questionner une base de données Access.

Code VBA Word à validation de la saisie
Il existe d'assez nombreux événements associés aux contrôles d'un formulaire Word. Et chacun d'entre eux permet d'engager des traitements par le code VBA en fonction d'actions spécifiques réalisées sur ces mêmes contrôles. Celui qui nous intéresse est celui capable de détecter la validation de la saisie du code postal. C'est en effet cette information qui doit nous permettre d'isoler dans la table Access, toutes les communes associées.

• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Renseignements,

Cette action a pour effet d'afficher le formulaire Word en mode conception.

• Sur le formulaire, double cliquer sur la zone de texte du code postal,

De cette manière, nous basculons dans l'éditeur de code VBA Word entre les bornes de la procédure événementielle CP_Change. CP est le nom de la zone de texte du code postal. Change est un événement associé. Mais il ne s'agit pas de celui qui nous intéresse. Nous ne souhaitons pas déclencher des actions VBA dès qu'une lettre est inscrite mais seulement à validation de la saisie.

• Dans la liste de droite en haut de l'éditeur VBA, choisir l'événement AfterUpdate,

Nous créons ainsi la procédure événementielle CP_AfterUpdate. C'est elle qui va se déclencher lorsque la touche Tab est enfoncée à la fin de la saisie, ou plus simplement lorsqu'un autre contrôle est activé après la saisie du code postal. De fait, la procédure CP_change peut être supprimée.

Les objets de base de données Access
Désormais, grâce à la référence que nous avons ajoutée au projet VBA Word, nous allons pouvoir déclarer les objets permettant de pointer sur la base de données cible et sur les enregistrements de sa table.

• Dans les bornes de la procédure, ajouter les instructions VBA suivantes :

Private Sub CP_AfterUpdate()
On Error Resume Next
Dim chemin_bd As String
Dim enr As Recordset: Dim base As Database

End Sub

Nous commençons par ajouter un garde-fou avec l'instruction : On Error Resume Next. Grâce à elle, si une erreur est rencontrée, elle sera ignorée. Elle peut survenir lors de la saisie d'un code postal incorrect ne conduisant à aucune concordance de ville par exemple. Ensuite, nous déclarons la variable chemin comme un texte pour mémoriser le chemin d'accès à la base de données Access à attaquer. Puis, grâce à la référence ajoutée, nous déclarons un objet (enr) capable de manipuler les enregistrements (Recordset) d'une base de données. Et pour pointer sur ces enregistrements, nous déclarons un autre objet (base) capable de pointer sur cette base de données en question (Databse).

Isoler les villes du code postal saisi
Maintenant que les objets de base de données sont déclarés, nous allons pouvoir instancier la classe permettant de créer l'objet héritant notamment d'une méthode capable d'engager une requête Sql sur la table de cette Bdd. Naturellement, cette requête doit se charger d'isoler les villes en fonction du code postal saisi par l'utilisateur sur le formulaire VBA Word.

• A la suite du code de la procédure, ajouter le code VBA suivant :

...
Ville.Clear
chemin_bd = ThisDocument.Path & "\bd-communes.accdb"
Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT Commune_nom FROM les_communes WHERE Commune_dep= '" & CP.Value & "'", dbOpenDynaset)
...

Tout d'abord, nous exploitons la méthode Clear de l'objet liste déroulante nommé Ville. Cette dernière permet de vider la liste des potentielles anciennes villes en cas de modification du code postal par l'utilisateur. Ensuite, nous exploitons la propriété Path de l'objet ThisDocument pour pointer sur le dossier de l'application VBA Word. Dès lors, nous lui associons le nom de la base de données sans oublier l'antislash (\) qui permet d'entrer dans le dossier. Ce chemin d'accès est stocké dans la variable chemin_bd. C'est alors que l'instanciation de la classe débute grâce au mot clé Set appliqué sur la variable de base de données (l'objet base). Et c'est la méthode OpenDatabase de l'objet DBEngine qui permet de pointer sur la base de données dont le chemin lui est passé en paramètre grâce à la variable chemin_bd. C'est ainsi que la méthode OpenRecordset peut être appliquée sur cet objet fraîchement instancié pour exécuter une requête Sql en paramètre. Classiquement, elle récupère les villes (Commune_nom) depuis la table les_communes mais seulement pour lesquelles le code postal (Commune_dep) est identique à celui saisi par l'utilisateur dans la zone de texte CP (CP.Value). L'extraction de cette requête est stockée dans la variable objet enr. C'est elle qui va nous permettre de parcourir les enregistrements résultants pour récolter les villes restreintes à charger dans la liste déroulante liée.

Parcourir les villes isolées par la requête
Grâce à l'objet enr donc, il s'agit premièrement de placer le pointeur sur le premier enregistrement des résultats extraits par la requête. Dès lors, nous devons les parcourir tous un à un pour charger les communes correspondantes dans la liste liée du formulaire VBA Word.

• A la suite du code de la procédure, ajouter les instructions VBA suivantes :

...
enr.MoveFirst
Do
Ville.AddItem enr.Fields("Commune_nom").Value
enr.MoveNext
Loop Until enr.EOF = True
...

Tout d'abord, la méthode MoveFirst de l'objet enr fraîchement créé, permet de placer l'indice de lecture sur le premier enregistrement résultant. C'est alors que nous enclenchons une boucle Do. La propriété Fields de l'objet enr permet de désigner en paramètre le champ de la requête à partir duquel nous souhaitons récupérer la valeur (Value) pour l'enregistrement en cours de lecture. Et c'est la méthode AddItem de l'objet de liste déroulante qui permet d'ajouter son contenu, soit la ville. Et bien sûr, nous n'oublions pas de passer sur l'enregistrement suivant grâce à la méthode MoveNext. C'est la propriété EOF (End Of File) de l'objet enr qui permet de détecter que la fin des enregistrements est atteinte pour mettre un terme à l'exécution de la boucle, donc quand la liste déroulante est totalement chargée des villes concordantes.

Détruire les objets de base de données
Le développement n'est pas encore tout à fait finalisé même si le résultat devrait être atteint. Ces objets, issus d'une instanciation de classe, doivent être détruits pour libérer proprement la mémoire utilisée. Pour cela, il existe des instructions VBA bien précises.

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

...
enr.Close
base.Close

Set enr = Nothing
Set base = Nothing
...

C'est simplement la méthode Close appliquée respectivement sur l'objet des enregistrements et sur celui de la base de données qui permet de les fermer. Dès lors, nous réalisons une affectation inverse (Set) grâce au mot clé Nothing pour les sortir de la mémoire de l'ordinateur.

Il est temps de tester le fonctionnement de ce formulaire.

• Enfoncer la touche F5 du clavier pour exécuter le code,
• Dans la zone du CP, taper un code postal des Hautes Alpes comme 05700,
• Puis, déployer la liste déroulante des villes,

Comme vous pouvez l'apprécier, ce sont bien toutes les communes du code postal inscrit qui sont proposées. Et bien évidemment, si vous changez de code postal, ce sont toutes les nouvelles villes attachées qui sont suggérées pour simplifier l'inscription.

Le code VBA complet qui a permis d'établir la liaison avec la base de données Access pour remplir cette liste déroulante en fonction de la saisie réalisée en amont est le suivant :

Private Sub CP_AfterUpdate()
On Error Resume Next
Dim chemin_bd As String
Dim enr As Recordset: Dim base As Database

Ville.Clear
chemin_bd = ThisDocument.Path & "\bd-communes.accdb"
Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT Commune_nom FROM les_communes WHERE commune_dep = '" & CP.Value & "'", dbOpenDynaset)

enr.MoveFirst
Do
Ville.AddItem enr.Fields("Commune_nom").Value
enr.MoveNext
Loop Until enr.EOF = True

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

End Sub