Etablir la connexion aux bases de données avec Excel

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


Sujets que vous pourriez aussi aimer :


Liaison dynamique d'un classeur et d'une base de données externe

Dans ce deuxième volet consistant à établir la connexion permanente entre un classeur Excel et une base de données externe, nous proposons précisément d'établir et de paramétrer ces liaisons dynamiques. Dans l'étape précédente, nous avions préparé les requêtes de synthèse éliminant les doublons, ainsi qu'un formulaire de saisie avec listes déroulantes reliées entre elles, depuis une base de données Access. Dans cette deuxième étape, l'objectif est de constater que tout ajout ou modification depuis la base de données centralisée, se répercute automatiquement sur les feuilles du classeur Excel, par le jeu des connexions dynamiques.



L'enjeu d'entreprise est colossal. Les salariés pourront dès lors manipuler, synthétiser et extraire de l'information, à partir de données perpétuellement actualisées. C'est ce que nous appliquerons dans le troisième et dernier volet. Les utilisateurs n'ont pas à se soucier des mises à jour et n'ont pas à accéder à la base de données centralisées. Il sera intéressant de constater que la mise en oeuvre de ce mécanisme est relativement simple. Ni le code VBA Excel ni les calculs ne sont nécessaires.

Sources et présentation de l'objectif
Pour établir ces liaisons dynamiques, nous avons besoin d'un classeur structuré. Il facilitera la réception des informations. De même, il est nécessaire de récupérer les travaux initiés depuis la base dedonnées Access, lors de la formation précédente.

• Télécharger le fichier liaison-dynamique-donnees-externes.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

La décompression fournit les deux fichiers de l'application. Vous notez tout d'abord la présence du classeur source, nommé liaison-base-donnees.xlsx, pour la réception dynamique des données. De même, vous notez la présence de la base de données Access, nommée donnees-externes.accdb.

• Double cliquer sur le fichier donnees-externes.accdb pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

L'essentiel des travaux consiste à paramétrer les connexions depuis le classeur Excel, en fonction des données que nous souhaitons lier dynamiquement, pour les manipuler. Nous exploiterons néanmoins le formulaire de saisie Access, pour simuler l'ajout de données dans une base située sur un intranet ou un serveur distant. L'objectif sera de constater que ces modifications seront automatiquement intégrées dans le classeur Excel, sans aucune intervention de l'utilisateur.



Vous notez la présence de tous les objets créés dans cette base de données, répertoriés dans le volet des objets Access, sur la gauche de l'écran. Cette base recense les véhicules d'un parc automobile d'une société de distribution. Le formulaire illustré par la capture ci-dessus, est construit sur la table Parc. Il permet la modification et l'ajout de nouvelles voitures. Nous l'exploiterons donc quand il sera temps de réaliser des vérifications sur le bon fonctionnement de la liaison dynamique.

De nombreuses requêtes de synthèse ont été créées. Majoritairement, elles permettent de lister tous les modèles d'une marque automobile, sans doublons, comme R_Renault et R_Peugeot par exemple. Il est intéressant de les lier dans l'application Excel pour profiter régulièrement des actualisations et mises en jour. Nous bâtirons même des listes déroulantes dynamiques sur la base de ces sources évolutives. Deux requêtes spécifiques apparaissent. Il s'agit de R_Marques et R_Modeles. La première extrait toutes les marques automobiles référencées, sans doublons. La seconde est conçue avec un critère dynamique, qui attend l'information de la marque depuis le formulaire, pour livrer tous les modèles correspondants. C'est l'imbrication de ces deux requêtes qui a donné vie aux listes déroulantes en cascade, pour simplifier la sélection.

• Dans le dossier de décompression, double cliquer sur le fichier liaison-base-donnees.xlsx, pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de trois feuilles. Les feuilles Lien_base et Marques concernent cette partie de la formation. Dans la première, il s'agira de réceptionner toutes les données de la table Parc, issue de la base de données donnees-externes.accdb. Dans la seconde, il s'agira de lier des plages de cellules aux informations dynamiques restituées par toutes les requêtes que nous avons présentées précédemment.

La feuille Extraire concernera le troisième et dernier volet de ces formations. Elle permettra d'articuler tous les travaux réalisés en amont pour produire l'extraction des données selon des critères recoupés, grâce à des listes déroulantes liées entre elles par le jeu des requêtes connectées.

Liaison dynamique des données depuis Excel
Maintenant que les présentations sont faites, il est temps d'établir le lien entre les deux applications. Nous proposons dans un premier temps, de réceptionner l'intégralité des données de la table Parc. C'est sur cette dernière que nous pourrons réaliser les requêtes afin d'extraire l'information, comme si nous le faisions directement sur la base de données distante.

• Cliquer sur l'onglet Lien_base en bas de la fenêtre Excel pour activer sa feuille,
• Puis, sélectionner la cellule B4 pour désigner le point de départ de l'importation,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Obtenir des données,
• Dans la liste, pointer sur la rubrique : A partir d'une base de données,
• Dans le sous menu qui apparaît, cliquer sur : A partir d'une base de données Microsoft Access,
• Dans la boîte de dialogue qui suit, accéder au dossier de décompression,
• Puis, double cliquer sur la base donnees-externes.accdb pour établir la connexion,

Une boîte de dialogue apparaît, comme l'illustre la capture ci-dessus. Elle liste tous les objets Access qui sont disponibles pour établir la connexion dynamique avec Excel. On y retrouve les requêtes de synthèse ainsi que la table principale. Nous souhaitons réorganiser les données de la table Parc avant de les importer. L'éditeur de requête Excel nous le permet.

• Dans la partie gauche, cliquer sur l'objet Parc pour sélectionner sa table,
• En bas de la boîte de dialogue, cliquer sur le bouton Modifier pour basculer dans l'éditeur de requête : Power Query,

Nous souhaitons simplement réagencer les champs pour une meilleure lecture et plus de clarté. Les colonnes doivent apparaître dans cet ordre : Marque, Modèle, Immatriculation et Chevaux.

• Glisser la colonne Marque par son entête en première position,
• Glisser la colonne Modèle par son entête en deuxième position,

Cet éditeur Power Query est très puissant, il permet de réaliser des requêtes spécifiques en aval, afin de spécifier précisément les données à lier, avant de procéder à l'importation.



Il ne reste plus qu'à spécifier l'endroit de l'importation pour établir la connexion à ces données externes réorganisées.

• Tout à fait à gauche du ruban Accueil, dans l'éditeur Power Query, cliquer sur la flèche du bouton Fermer et charger,
• Dans la liste, choisir Fermer et charger dans,
• Dans la boîte de dialogue qui suit, cocher la case : Feuille de calcul existante,
• Puis désigner la cellule B4 si ce n'est déjà fait,
• Enfin, cliquer sur le bouton Ok pour valider la connexion et l'importation,

Toutes les données de la table distante sont parfaitement restituées.

Paramètres de connexion et actualisations des données
Il est important de comprendre que ces données importées sont potentiellement connectées. Cela signifie qu'elles doivent évoluer au gré des modifications réalisées sur la base de données d'origine. Mais pour cela, un réglage est nécessaire. C'est d'ailleurs ce que nous proposons de découvrir et de paramétrer en accédant aux propriétés de la connexion. Si le volet des requêtes et connexions n'est pas présent sur la droite de la fenêtre Excel, vous devez l'afficher. Pour cela, il suffit de cliquer sur le bouton Requêtes et connexions dans le ruban Données.

• Dans le volet, cliquer avec le bouton droit de la souris sur la connexion Parc,
• Dans le menu contextuel, choisir Propriétés,
• Dans la boîte de dialogue Propriétés de la requête, cocher la case : Actualiser toutes les,
• Remplacer la période de 60 minutes par 5 minutes,
• Puis, cocher la case : Actualiser les données lors de l'ouverture du fichier,

Jusqu'alors les données étaient bien connectées à la base de données d'origine. Mais l'actualisation nécessitait une action manuelle par le biais du bouton Actualiser tout du ruban Données. En cochant les deux cases précédentes, nous avons défini une liaison dynamique et automatique. Désormais, la connexion s'établit toutes les 5 minutes, comme à l'ouverture du classeur, pour réactualiser les informations importées, si des changements étaient décelés.

Nous vérifierons plus tard que les données se mettent correctement à jour. Avant cela, nous devons établir la liaison avec les requêtes de la base de données, sur la feuille Marques. Les paramétrages d'actualisation automatique devront être définis pour chaque connexion.

La seule requête qui n'était pas proposée dans la boîte de dialogue d'importation des données, était la requête R_Modeles. En établissant la connexion, Excel a détecté que le critère de cette dernière était dynamique et que seule, elle ne pouvait restituer aucune donnée. Toutes les autres requêtes de synthèse doivent être importées.

• Cliquer sur l'onglet Marques en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule B4 pour définir le point de départ de la première importation,
• Dans le ruban Données, cliquer sur le bouton Obtenir des données,
• Comme précédemment, pointer sur la rubrique : A partir d'une base de données,
• Puis, cliquer sur l'option : A partir d'une base de données Microsoft Access,
• Dans la boîte de dialogue qui suit, double cliquer sur la base donnees-externes.accdb,
• Dans la fenêtre qui suit, sélectionner la requête R_Marques sur la gauche,
• Cliquer alors sur la flèche du bouton Charger en bas de la boîte de dialogue,
• Dans la liste, cliquer sur l'option : Charger dans,
• Dans la boîte de dialogue qui suit, cocher la case : Feuille de calcul existante,
• Accepter la cellule B4 désignée par défaut,
• Enfin, cliquer sur Ok pour finaliser la connexion et l'importation des données de la requête,
• Dans le volet Requêtes et connexions, cliquer droit sur la connexion R_Marques,
• Dans le menu contextuel, choisir Propriétés,
• Dans la boîte de dialogue, cocher la case : Actualiser toutes les,
• Puis, définir l'intervalle de temps sur 5 minutes comme précédemment,
• Cocher de même la case : Actualiser les données lors de l'ouverture du fichier,
• Puis, valider les paramétrages de la connexion par Ok,

La liaison est parfaitement établie. Elle restitue toutes les marques uniques, recensées dans la base de données, selon le critère de la requête sélectionnée. Si de nouvelles marques venaient à être ajoutées dans le parc automobile, elles seraient automatiquement intégrées dans la requête et donc resituées sur la feuille Excel.

Il s'agit désormais de réitérer exactement les mêmes actions (Importation et paramétrage), pour établir la connexion avec les autres requêtes.

• Importer les données de la requête R_Audi en cellule C4,
• Importer les données de la requête R_Bmw en cellule D4,
• Importer les données de la requête R_Citroen en cellule E4,
• Importer les données de la requête R_Fiat en cellule F4,
• Importer les données de la requête R_Mercedes en cellule G4,
• Importer les données de la requête R_Nissan en cellule H4,
• Importer les données de la requête R_Peugeot en cellule I4,
• Importer les données de la requête R_Renault en cellule J4,
• Importer les données de la requête R_Volkswagen en cellule K4,
• Pour chacune de ces connexions, définir une actualisation toutes les 5 minutes ainsi qu'à l'ouverture du classeur,

Chacune des plages importées est repérée par un nom, attribué automatiquement par Excel. Ce nom est d'ailleurs celui de la requête importée, telle qu'elle a été nommée depuis Access, lors de sa conception. Il sera intéressant de constater que ces plages de cellules sont dynamiques et évolutives. Ces noms ne désignent pas des plages aux bornes fixes. Et nous pourrons nous en inspirer pour produire des calculs d'extraction dynamiques, capables de considérer les nouvelles données.

C'est aussi un gros avantage de cette connexion dynamique aux données. Il ne sera pas nécessaire de faire appel à la fonction Excel Decaler pour adapter les bornes d'une source afin d'en extraire les données.



Actualisation automatique des données
Il est temps de vérifier que nos liaisons sont dynamiques et que les données sont mises à jour automatiquement. Pour ce faire, nous proposons de créer de nouveaux véhicules, grâce au formulaire de la base de données Access.

• Revenir sur la base de données Access donnees-externes.accdb,
• Dans le volet des objets sur la gauche, double cliquer sur le formulaire Parc pour l'exécuter,
• Dans la barre de navigation en bas du formulaire, cliquer sur le bouton Nouvel enregistrement,
• Créer le véhicule suivant en le saisissant : HY655VG, Mercedes, Classe B, 110,

Deux résultats importants sont à vérifier. Tout d'abord, nous avons créé un nouveau modèle pour la marque Mercedes. Comme le modèle Classe B n'était pas proposé dans la liste déroulante, nous l'avons saisi. Cette action est rendue possible grâce à sa propriété Limiter à liste réglée à Non, comme nous l'avons définie dans la formation précédente. Désormais, puisque les requêtes sont dynamiques et que le contenu de cette liste déroulante est lié à l'une d'entre elles, elle devrait automatiquement proposer ce nouveau choix à l'avenir. Ensuite, nous devons nous assurer que ce nouveau modèle est bien intégré dans la feuille Excel des liaisons dynamiques avec les requêtes.

• Cliquer de nouveau sur le bouton Nouvel Enregistrement, en bas du formulaire,
• Créer le nouveau véhicule suivant : VT896HV, Renault, Talisman, 150,

Même remarque que précédemment, le modèle Talisman n'existait pas jusqu'alors.

• Cliquer une dernière fois sur le bouton Nouvel enregistrement,
• Saisir les informations suivantes : LP136GF, Mercedes, Classe B, 130,

La première vérification est d'ores et déjà validée. Comme vous l'avez constaté à la création de ce nouveau véhicule, le modèle classe B a été suggéré par la seconde liste déroulanteliée. C'est toute la souplesse et la puissance d'Access, lorsque les formulaires et requêtes sont imbriqués pour créer des applications.

• Fermer le formulaire Access en cliquant sur la croix de son onglet,
• Revenir sur le classeur Excel et activer la feuille Marques,

La deuxième vérification est instantanément validée. Les nouveaux modèles ont été récupérés et intégrés automatiquement dans la feuille Excel, par le jeu des connexions dynamiques. L'actualisation des informations a été programmée toutes les 5 minutes. Il convient donc de patienter si les données n'ont pas encore été rafraîchies. De même et comme l'illustre aussi la capture ci-dessus, la plage des modèles de la marque Renault est toujours nommée R_Renault. Pourtant ses bornes ont évolués, puisqu'une nouvelle donnée a été inséré. Il est donc particulièrement intéressant de constater que nous pourrons exploiter ces noms pour produire des calculs d'extraction dynamiques, sur des plages de données évolutives.

• Cliquer sur l'onglet Lien_base en bas de la fenêtre Excel pour activer sa feuille,
• Réaliser le raccourci clavier CTRL + Fin pour atteindre le bas de la base de données,

Même constat que précédemment, les nouveaux enregistrements ayant été ajoutés dans la table grâce au formulaire, ils ont automatiquement été actualisés sur la feuille Excel. Là encore, cette importation est liée à une plage de cellules nommée que nous exploiterons pour les calculs d'extraction dans le dernier volet.



Ces mises à jour de données sont réalisées en arrière-plan. Mais vous pouvez visualiser la notification de connexion à la base distante, dans la barre d'état en bas de la fenêtre Excel. Si vous cliquez sur le bouton Actualiser tout dans le ruban Données, vous pouvez aussi visualiser les processus de liaison, dans le volet Requêtes et connexions.