Communication entre Access et Excel par le code VBA

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


Sujets que vous pourriez aussi aimer :


Liaison et communication entre Access et Excel

Dans cette formation, nous allons concevoir une petite application Access. Par le biais d'un formulaire recensant les clients d'une banque, le commercial pourra délivrer en temps réel, les montants des mensualités des prêts accordés à ses clients. Comme Excel propose les fonctions permettant de simuler les emprunts, au moment opportun, l'application Access communiquera avec Excel pour lui fournir les données permettant de calculer les mensualités d'un prêt. Une fois le calcul réalisé, Excel les retournera à Access, par le biais du code Visual Basic, afin d'afficher les résultats sur le formulaire du client. Et tout ce processus doit se dérouler de façon transparente pour le commercial de la banque.


Présentation de l'application
Comme l'illustre la capture ci-dessus, nous allons développer le programme permettant à Access et Excel de communiquer, en partant d'un formulaire pré-conçu. Ce formulaire a été bâti à partir d'une requête réunissant les informations de deux tables liées, celle des clients et celle de leurs emprunts. La partie supérieure du formulaire présente les informations détaillées du client. La partie inférieure, présente les données variables du prêt à accorder au client.

Des boutons permettent de naviguer au travers des enregistrements ou encore de calculer les mensualités de l'emprunt, en fonction de ces variables. C'est précisément au clic sur le bouton Calculer qu'un code VBA doit permettre à Access de transmettre ces variables à Excel. Ce dernier se chargera de calculer les mensualités, à l'aide de la fonction VPM, et de retourner les résultats à Access, pour les afficher sur le formulaire.

• Télécharger la base de données liaison-access-calculs-excel.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu si nécessaire,
• Télécharger le classeur calcul-prets-excel.xlsx, dans le même dossier que la base de données Access, en cliquant sur son lien,

Le classeur Excel est constitué d'une seule feuille nommée Calculs de prêts. Dans un petit tableau, figurent les données permettant de calculer les mensualités d'un prêt. On trouve le taux d'intérêt en D5, la période de remboursement, en nombre de mois en D6, et le montant total emprunté en euros, en D7. En fonction de ces variables, la fonction VPM bâtie en D9, calcule automatiquement les mensualités pour le remboursement de l'emprunt. Le coût total s'en déduit par un calcul simple en D10.

Le formulaire Access par le code VBA, doit donc être en mesure d'ouvrir ce classeur, d'inscrire les variables respectivement en D5, D6 et D7. Comme la mensualité se calcule automatiquement, ce même code VBA, doit être capable de récupérer la valeur de la mensualité calculée en D9 par Excel.


L'ossature de la base de données Access est quant à elle conçue sur deux tables, Clients et Emprunts reliées entre elles par le jeu des relations. Des types de données, formats et masques de saisie ont été paramétrés comme nous l'avait appris la formation Access sur les masques de saisie et formatage de champs. Les champs de ces deux tables sont réunis dans une requête sélection nommée Emprunts_clients. Et c'est grâce à cette requête que le formulaire final nommé lui aussi Emprunts_clients, a été conçu.

• Double cliquer sur le formulaire Emprunts_clients depuis le volet des objets Access, sur la gauche de la fenêtre,

Vous l'affichez ainsi en mode exécution. Hormis le bouton de la Loupe pour rechercher un client sur son nom, tous les autres sont à programmer. Ainsi si vous cliquez sur les boutons des flèches pour naviguer au travers des enregistrements, rien ne se produit. De même si vous cliquez sur le bouton Calculer, les deux zones de texte Mensualités et Rbst Total restent vides.


Naviguer au travers des enregistrements d'un formulaire
Avant de programmer le bouton Calculer pour fournir les données à Excel et récupérer les résultats du calcul pour les afficher sur le formulaire, nous allons coder les deux boutons permettant d'afficher le client suivant et le client précédent. Ils doivent en même temps vider les zones de texte potentiellement remplies par le calcul des mensualités pour un client précédent. Nous allons commencer par masquer la barre de navigation proposée par défaut en bas du formulaire Access, car elle fait doublon.

• Cliquer sur la flèche du bouton Affichage en haut à gauche du ruban Création,
• Dans la liste, choisir Mode création,
• Toujours dans le ruban Création, cliquer sur le bouton Feuille de propriétés si cette dernière n'est pas visible,

Nous affichons ainsi la feuille de propriétés du contrôle actif sur le formulaire en mode création. Par défaut, c'est le formulaire lui-même qui est sélectionné. Et ce sont ses propriétés que nous allons paramétrer pour masquer la barre de navigation afin de la remplacer par les fonctionnalités des boutons de déplacement.

• Activer l'onglet Format de la feuille de propriétés,
• Régler les propriétés Afficher le sélecteur et Boutons de déplacement sur Non,

Le sélecteur est matérialisé par une flèche, sur la gauche du formulaire, en direction des enregistrements. Il ne nous est pas utile.

• Exécuter le formulaire en enfonçant la touche F5 du clavier par exemple,

Comme vous le remarquez, la barre de navigation en bas du formulaire, ainsi que le sélecteur d'enregistrements sur la gauche ont disparu. Nous pouvons donc coder nos propres boutons de déplacement entre enregistrements.

• Afficher de nouveau le formulaire en mode Création,
• Sélectionner le bouton Precedent matérialisé par la flèche orientée vers la gauche,
• Activer l'onglet Evénement de sa feuille de propriétés,
• Cliquer sur le bouton de son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,

Nous basculons ainsi dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure Precedent_Click. Tout code saisi entre ces bornes se déclenchera donc au clic sur le bouton nommé Precedent.

• Entre les bornes de la procédure événementielle, saisir le code suivant :

On Error GoTo erreur

DoCmd.GoToRecord acDataForm, "Emprunts_clients", acPrevious
mensualites.Value = ""
rbt_total.Value = ""

erreur:

L'instruction On ErrorGoTo erreur permet d'ignorer l'erreur si elle se produit et de renvoyer l'exécution du code à l'étiquette erreur située en fin de procédure. Une erreur est susceptible de se produire lorsque l'utilisateur clique sur le bouton Précédent alors qu'il est déjà situé sur le premier enregistrement de la table. Ainsi aucun message d'alerte ne se déclenche. L'objet VBA DoCmd permet d'exécuter des commandes comme nous l'avait appris la formation VBA Access pour réaliser des interactions entre les objets. Sa méthode GoToRecord permet de placer le pointeur de l'objet sur l'enregistrement à désigner. L'objet en question est le formulaire Emprunts_clients passé en second paramètre. La constante acPrevious passée en troisième paramètre de la méthode, permet de déplacer ce pointeur sur l'enregistrement précédent.

Ensuite, comme nous changeons d'enregistrement et donc de client, nous vidons le contenu potentiel des zones de calculs (mensualites.Value = "", rbt_total.Value = "").

Le code à produire pour le bouton Suivant est quasi-identique. Il s'agit simplement de remplacer la constante acPrevious de la méthode GoToRecord par la constante acNext.

• Sélectionner et copier (CTRL + C) le code précédemment saisi,
• Sélectionner le bouton Suivant matérialisé par la flèche orientée à droite,
• Cliquer sur le bouton de son événement Au clic depuis sa feuille de propriétés,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,
• Dans les bornes de la procédure événementielle ainsi créée, coller le code (CTRL + V),
• Remplacer la constante acPrevious par acNext,
• Enregistrer les modifications et basculer de nouveau sur le formulaire,
• Exécuter ce dernier (F5) et cliquer sur le bouton pour les tester,

Nous naviguons ainsi entre les enregistrements des tables Clients et Emprunts. Aucun message d'erreur ne se déclenche en bout de course. De plus, si vous saisissez un contenu dans les deux zones de calcul en bas du formulaire, celui-ci est réinitialisé au passage d'un nouvel enregistrement.


Tout fonctionne parfaitement à ce stade. Il reste à programmer le bouton essentiel, celui qui établit la connexion avec Excel afin de réceptionner les résultats des calculs.

Communication entre Access et Excel par le code VBA
Afin de pouvoir piloter les objets de programmation Excel par le code VBA depuis Access, nous devons ajouter une librairie sous forme de référence. C'est aussi ce que nous avions dû faire avec une autre librairie pour accéder aux fichiers du disque dur en VBA Access.

• Afficher le formulaire en mode Création,
• Sélectionner le bouton Calculer,
• Cliquer sur le bouton de son événement Au clic depuis sa feuille de propriétés,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,

Nous sommes de retour dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure événementielle calculer_Click cette fois. Avant de développer le code qui se déclenchera au clic sur ce bouton, nous devons ajouter la référence nécessaire pour piloter les objets Excel à distance.

• En haut de l'éditeur, cliquer sur le menu Outils,
• Dans la liste, choisir Références,
• Cocher la case Microsoft Excel 16.0 Object Library,

Le numéro, 16.0 ici, dépend de la version du pack Office installée sur votre ordinateur. Maintenant que cette référence est ajoutée, les objets de programmation sont disponibles par le code VBA Access. Nous allons pouvoir les instancier afin d'exploiter les propriétés et méthodes permettant de piloter Excel pour établir la communication. Mais avant cela et comme toujours, nous devons commencer par déclarer les variables nécessaires.

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

Dim le_taux As Single: Dim la_duree As Integer: Dim le_montant As Single
Dim fenetre As Excel.Application
Dim classeur As Excel.Workbook
Dim chemin As String

Les variables le_taux, la_duree et le_montant permettront de stocker les valeurs définies pour le prêt, depuis le formulaire Access. Single permet de déclarer des nombres réels tandis que la variable la_duree représentant une période en nombre de mois, est fort logiquement déclarée comme un entier (Integer). Nous déclarons ensuite deux variables objets grâce à la référence que nous avons ajoutée précédemment. La variable fenetre doit permettre de piloter un objet de type application Excel. Une de ses méthodes permettra ainsi de pointer précisément sur le classeur à ouvrir, grâce à son chemin d'accès. La variable objet classeur permettra quant à elle de piloter les éléments du classeur Excel (Workbook) ainsi instancié, notamment pour pointer sur des cellules précises. Enfin la variable chemin est déclarée comme une chaîne de caractères (String) puisqu'elle doit servir à mémoriser le chemin d'accès complet au classeur Excel. Nous devons maintenant initialiser ces variables.

• A la suite du code, ajouter les affectations suivantes :

le_taux = emprunt_taux.Value
la_duree = emprunt_duree.Value
le_montant = emprunt_montant.Value
chemin = Application.CurrentProject.Path & "\calcul-prets-excel.xlsx"

Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)

Nous mémorisons les valeurs du taux d'intérêt, de la durée et du montant du prêt dans les variables prévues à cet effet. Pour ce faire, nous accédons au contenu de chaque zone de saisie du formulaire par le biais de leur propriété Value. Grâce à la propriété dérivée Path de la propriété CurrentProject de l'objet VBA Application, nous récupérons le chemin d'accès à la base de données en cours. A celui-ci, nous concaténons (&), après un antislash le nom du classeur Excel avec lequel nous souhaitons établir la communication. Le chemin d'accès complet au classeur Excel est ainsi mémorisé dans la variable chemin. Puis grâce à la fonction VBA Access CreateObject, nous instancions notre objet ActiveX pour piloter Excel. Dès lors, grâce à la méthode Open de la propriété Workbooks de l'objet Excel ainsi instancié, nous initialisons (Set) notre objet classeur. Comme le paramètre passé à la méthode Open n'est autre que le chemin d'accès au classeur Excel, notre objet a désormais accès aux cellules des feuilles de ce classeur notamment.

De fait, nous allons désormais pouvoir écrire dans les cellules de la feuille Calculs de prêts, les variables du problème. Il s'agit du taux d'intérêt en D5, de la durée en nombre de mois en D6 et du montant de l'emprunt en D7. Les calculs étant dynamiques dans Excel, nous n'aurons plus qu'à récupérer les résultats en D9 et D10 pour les inscrire dans les zones du formulaire client, prévues à cet effet.

• Ajouter les lignes suivantes pour écrire dans les cellules de la feuille Excel, depuis Access,

classeur.Sheets("Calculs de prêts").Range("D5").Value = le_taux / 100
classeur.Sheets("Calculs de prêts").Range("D6").Value = la_duree
classeur.Sheets("Calculs de prêts").Range("D7").Value = le_montant

La propriété Sheets de notre objet classeur permet de pointer sur la feuille passée en paramètre. La propriété dérivée Range avec en paramètre, la référence de la cellule permet de désigner cette dernière. Et enfin, la propriété Value permet d'accéder au contenu de cette cellule que nous affectons avec la variable appropriée. Notez que nous divisons le taux d'intérêt par 100 puisqu'il s'agit d'un pourcentage.

Il ne reste plus qu'à prélever les résultats du calcul, notamment effectués par la fonction Excel VPM, selon les variables transmises. Pour cela, nous exploitons de nouveau la propriété Value des cellules que nous affectons aux zones de texte du formulaire.

• Ajouter les deux lignes de code permettant de récupérer la mensualité du prêt et la valeur du remboursement total, calculées par les fonctions Excel :

mensualites.Value = classeur.Sheets("Calculs de prêts").Range("D9").Value
rbt_total.Value = classeur.Sheets("Calculs de prêts").Range("D10").Value

Ces résultats sont respectivement situés en D9 et D10. Nous y accédons de la même façon que précédemment. Puis, nous les affectons au contenu des zones de saisie du formulaire, là encore par le biais de la propriété Value de chacun des objets.

Comme nous l'avions vu au travers de la formation VBA Access pour accéder aux données, nous devons maintenant fermer les objets de programmation et vider la mémoire.

• Pour cela, ajouter les lignes de code suivantes :

fenetre.Application.DisplayAlerts = False
fenetre.Quit

Set fenetre = Nothing
Set classeur = Nothing

Tout d'abord, la propriété booléenne DisplayAlerts réglé à False permet de signifier à Excel de n'afficher aucun message visant à demander l'enregistrement des données mises à jour dans le classeur. La méthode Quit de l'objet de programmation Excel est l'équivalent de la méthode Close des objets DAO d'accès aux données, afin de fermer l'objet. Enfin, nous vidons la mémoire utilisée par ces objets (Set) grâce au mot clé Nothing.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire (ALT + F11),
• Exécuter ce dernier à l'aide de la touche F5 du clavier par exemple,
• Cliquer sur le bouton Calculer,

Quasi-instantanément, Access fournit les données à Excel par le code VBA, puis récupère les résultats du calcul et les affiche dans les zones de la mensualité et du remboursement total en bas du formulaire.

Si vous cliquez sur le bouton Suivant, vous affichez un nouveau client pour lequel ces zones sont vidées, grâce au code que nous avions développé précédemment. Il suffit alors de cliquer de nouveau sur le bouton Calculer pour obtenir les nouvelles mensualités selon les variables du prêt propres au client en cours de consultation. A chaque clic, VBA fournit au classeur Excel les nouvelles variables et retourne après calcul, les nouveaux résultats.


Le code est donc parfaitement fonctionnel. La communication entre Access et Excel est établie grâce à Visual Basic. L'intérêt est d'exploiter chacun des logiciels en fonction de ses avantages et de ses spécificités.