Transmettre des données entre Excel et Access en VBA

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


Sujets que vous pourriez aussi aimer :


Transmettre entre Excel et Access

Les logiciels de la gamme Office font preuve d'une grande interopérabilité. En d'autres termes, ils communiquent très bien pour que l'un exploite les compétences de l'autre et vice-versa.



Sur l'exemple illustré par la capture, nous travaillons sur un formulaire énumérant les commerciaux d'une entreprise. Pour les faire défiler, il faut utiliser la barre de navigation personnalisée. Sur la droite, dans une zone d'intitulé CA, l'opérateur saisit le chiffre réalisé par le commercial. A validation, sa prime est automatiquement calculée et restituée dans la zone intitulée Prime. Mais comme ce calcul dépend d'une grille tarifaire avec de nombreux paliers, la main est donnée à Excel qui est l'expert des calculs. La valeur du CA lui est transmise. C'est alors le code VBA Access qui récupère le résultat de son calcul pour l'afficher sur le formulaire.

Base Access et classeur Excel à télécharger
Pour la mise en place de cette nouvelle astuce VBA Access, nous proposons d'appuyer l'étude sur une base de données et un classeur Excel existants.

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

La décompression livre en effet la base de données Access et le classeur Excel.

• Double cliquer sur le fichier de base de données pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité pour libérer les ressources,
• Dans le volet de navigation sur la gauche, double cliquer sur le formulaire fCommerciaux,

Fidèlement à celui de la présentation, ce formulaire énumère les informations des commerciaux un à un. Le chiffre d'affaires doit être inscrit dans la zone d'intitulé CA pour recevoir la prime correspondante dans la zone d'intitulé Prime. Ces deux zones sont liées à des champs de la table Commerciaux. Donc les valeurs seront mémorisées et stockées.

Le calcul des primes
Pour calculer ces primes, nous l'avons dit, l'application Access doit s'appuyer sur une feuille de calcul Excel.

• A la racine du dossier de décompression, double cliquer sur le fichier calculs-de-primes.xlsx,
• Dès lors, cliquer sur le bouton Activer la modification du bandeau de sécurité,

L'unique feuille de ce classeur présente le tableau des commerciaux entre les colonnes B et H. Les chiffres respectifs, issus du formulaire Access doivent être inscrits automatiquement en colonne G. La grille des primes fixe les règles entre les colonnes J et K. Par exemple, entre 50 000 et 100 000 Euros de chiffres, la prime allouée est de 10% et de 15% au-delà de 100 000.



Etant donné le nombre de tranches, c'est par recherche approximative de chaque CA que la prime de chacun est calculée en colonne H, selon la syntaxe suivante :

=RECHERCHEV(G4; $J$4:$K$9; 2; VRAI)*G4

L'objectif est donc le suivant. A validation de la saisie d'un CA sur le formulaire Access, le code VBA doit ouvrir ce classeur en arrière-plan pour y inscrire la valeur en colonne G sur la ligne du commercial concerné. Comme le calcul est automatiquement entrepris par Excel, ce même code doit réceptionner la valeur calculée en colonne H de la même ligne, pour restituer la valeur de la prime dans la zone prévue à cet effet sur le formulaire Access.

Code VBA à validation de la saisie
Nous devons donc premièrement être en mesure de déclencher un code VBA à validation de la saisie dans la zone de texte du chiffre d'affaires, depuis le formulaire Access.

• Fermer Excel et revenir sur Access (ALT + Tab),
• A gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans les propositions, choisir le mode Création,
• Sur le formulaire en conception, cliquer sur la zone de texte c_chiffre pour la sélectionner,
• Dès lors, activer l'onglet Evénement de sa feuille de propriétés,

Si elle n'est pas visible dans votre environnement, vous devez l'activer. Pour cela, vous devez cliquer sur le bouton Feuille de propriétés dans le ruban Conception de formulaires ou Création selon la version.

• Cliquer sur le petit bouton à droite de l'événement Après MAJ,

Après MAJ signifie Après Mise à Jour. Cet événement se déclenche donc à validation de la saisie dans la zone de texte désignée. C'est exactement ce que nous cherchions.

• Dans la boîte de dialogue, choisir le générateur de code et valider par Ok,

Nous basculons ainsi dans l'éditeur VBA Access, plus précisément entre les bornes de la procédure événementielle c_chiffre_AfterUpdate.

La référence à Excel
Désormais, pour pouvoir piloter les feuilles et cellules d'un classeur Excel à distance par le code VBA Access, nous devons ajouter une référence au projet. Il s'agit d'une librairie des classes Excel permettant d'hériter des propriétés et méthodes pour manipuler ses éléments.

• En haut de l'éditeur, cliquer sur le menu Outils,
• Puis, choisir le sous-menu Références,

Dans la boîte de dialogue qui apparaît, les librairies sont organisées par ordre alphabétique croissant quand elles ne sont pas encore cochées.

• Cocher la case Microsoft Excel 16.0 Object Library,

Ce numéro (16.0) dépend de la version d'Office installée sur votre ordinateur.

• Valider l'ajout de cette référence par le bouton Ok de la boîte de dialogue,

Nous allons maintenant pouvoir piloter des classeurs Excel par le code VBA Access.

Les variables Objets Excel
Pour cela, nous devons commencer par déclarer des variables dont des objets destinés à hériter des propriétés et méthodes pour contrôler la feuille distante.

• Entre les bornes de la procédure événementielle, ajouter les déclarations suivantes :

Dim fenetre As Excel.Application
Dim classeur As Excel.Workbook
Dim feuille As Excel.Worksheet
Dim chemin As String: Dim compteur As Byte
Dim test As Boolean

Nous déclarons trois variables objets nommées respectivement fenetre, classeur et feuille. Grâce à leurs types respectifs (Excel.Application, Excel.Workbook et Excel.Worksheet), elles permettent respectivement de piloter Excel au sens large, un classeur dans l'instance Excel ainsi ouverte et une feuille dans ce classeur de cette instance.

Les trois autres variables sont plus classiques. La première (chemin) doit mémoriser le chemin d'accès au classeur à piloter. La deuxième (compteur) doit stocker le numéro de ligne sur laquelle le chiffre d'affaires doit être inscrit pour déclencher le calcul de la prime du commercial en cours de consultation depuis le formulaire Access. La dernière (test) est une variable booléenne. Elle doit servir de test lors de la recherche du commercial sur la feuille Excel.

Il est temps maintenant de passer à la phase d'initialisation et d'affectation de ces variables.

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

compteur = 4: test = False
chemin = Application.CurrentProject.Path & "\calculs-de-primes.xlsx"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)
Set feuille = classeur.Worksheets("Primes")
fenetre.Visible = False

Nous initialisons tout d'abord la variable compteur sur la première ligne du tableau de la feuille, soit la ligne 4. Nous initialisons la variable booléenne à False. Tant que le test n'a pas débuté ,nous considérons fort naturellement que la ligne du commercial cherché dans le tableau, n'a pas encore été trouvée. Ensuite, grâce à la propriété enfant Path de la propriété CurrentProject de l'objet Application, nous concaténons le nom du fichier ("\calculs-de-primes.xlsx") au chemin d'accès au dossier de l'application. C'est une technique que nous avons découverte à l'occasion d'une astuce VBA Access précédente.

Ensuite, nous initialisons les variables objets. Grâce à la fonction CreateObject, nous instancions la classe Excel.Application. De fait, l'objet fenetre hérite des propriétés et méthodes pour piloter un classeur. Et c'est ce que prouve la ligne suivante avec sa collection Workbooks et sa méthode Open pour ouvrir le classeur dont le chemin d'accès lui est passé en paramètre. Dès lors, l'objet classeur dispose des propriétés et méthodes pour piloter précisément le classeur désigné. Nous exploitons ainsi sa collection Worksheets à laquelle nous passons le nom de la feuille ciblée (Primes) en argument. Ainsi, l'objet feuille est désormais en mesure de manipuler les éléments de cette feuille pour ce classeur. C'est de cette manière que nous allons agir précisément sur ses cellules. Enfin, nous réglons à False la propriété Visible de l'objet fenetre qui désigne l'application Excel. De cette manière, le code VBA Access peut accéder en tâche de fond à ce classeur, donc sans qu'il ne soit ouvert aux yeux de l'utilisateur.

Trouver la ligne dans la feuille Excel
Puisque tous les éléments sont maintenant réunis pour accéder aux cellules du classeur Excel, nous devons partir à la recherche du nom du commercial dans le tableau de la feuille. Pour cela, nous devons engager une boucle qui poursuit son traitement tant que la cellule cherchée n'a pas été trouvée. Et c'est là que notre variable booléenne entre en action. La correspondance doit être avérée en même temps sur le nom et le prénom.

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

Do While test = False
If (feuille.Cells(compteur, 4).Value = c_nom.Value And feuille.Cells(compteur, 5).Value = c_prenom.Value) Then
test = True
Else
compteur = compteur + 1
End If
Loop

Nous engageons une boucle Do Loop qui tourne tant que le test est vérifié, soit tant que la valeur de la variable booléenne est toujours calée à False. A chaque passage, nous réalisons un double test grâce à une instruction conditionnelle VBA. Son rôle est de vérifier l'équivalence entre le nom du commercial sur le formulaire (c_nom.Value) et le nom du commercial en colonne 4 de la feuille (Cells(compteur, 4).Value), soit en colonne D, mais aussi entre le prénom sur le formulaire (c_prenom.Value) et le prénom sur la feuille (Cells(compteur, 5).Value). Et à ce titre, vous remarquez que nous retrouvons les objets VBA Excel comme l'objet Cells qui permet de piloter les cellules d'une feuille en fonction de ses coordonnées en ligne et en colonne à passer en premier et second paramètre. Sa propriété Value permet bien sûr d'accéder au contenu de ces cellules. Si ce double test est vérifié, nous basculons l'état de la variable booléenne à True. C'est ainsi que la boucle stoppe son traitement et que l'indice de la ligne du commercial trouvé reste conservé dans la variable compteur. Le cas échéant (else), nous incrémentons la variable compteur d'une unité (compteur = compteur + 1). C'est ainsi, aux prochains passages dans la boucle, que ce sont les lignes suivantes qui seront analysées par l'instruction conditionnelle à la recherche du bon commercial.

Transmettre la valeur à Excel et récupérer le calcul
Puisque la ligne du commercial ciblé est maintenant connue, nous pouvons inscrire son chiffre en colonne G, soit en colonne 7 pour l'objet Cells. Pour cela, nous devons récupérer la valeur dans le champ c_chiffre du formulaire pour l'inscrire dans la cellule de coordonnées compteur et 7, grâce à la propriété Value de l'objet Cells.

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

If test = True Then
feuille.Cells(compteur, 7).Value = c_chiffre.Value
c_prime.Value = feuille.Cells(compteur, 8).Value
Else
c_prime.Value = 0
End If

Nous débutons par une vérification sur l'état de la variable booléenne. Si sa valeur n'a pas été basculée, cela signifie que le commercial n'a pas été trouvé. Dans ce cas, la récupération ne peut être opérée et le traitement est avorté. Nous inscrivons donc le chiffre du commercial (c_chiffre.Value) sur sa ligne (compteur) en colonne 7 (feuille.Cells(compteur, 7).Value =). De fait, le calcul de la prime est immédiatement entrepris par Excel en colonne 8 voisine. Sur cette même ligne, nous le récupérons (feuille.Cells(compteur, 8).Value) pour l'affecter au champ c_prime du formulaire (c_prime.Value =).

Fermer le classeur et vider les objets
Nous en avons presque terminé mais comme nous en avons l'habitude désormais, nous devons fermer et détruire les variables objets pour libérer proprement les ressources.

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

classeur.Save
fenetre.Application.DisplayAlerts = False
fenetre.Quit
Set fenetre = Nothing
Set classeur = Nothing
Set feuille = Nothing

Tout d'abord, nous enregistrons les modifications grâce à la méthode save de l'objet classeur. C'est ainsi que nous conservons la valeur du nouveau chiffre et celle du calcul de sa prime. La propriété DisplayAlerts de l'objet enfant Application pour l'objet parent fenetre demande à l'application Excel de ne pas déclencher de message à la fermeture. En effet, dans l'enchaînement nous entreprenons la fermeture avec la méthode Quit de l'objet fenetre. Puis, nous détruisons les variables objets en les réinitialisant à Nothing.

Tester le transfert entre Access et Excel
Il est maintenant temps de tester le bon fonctionnement de l'application.

• Enregistrer les modifications (CTRL + S) et revenir sur le formulaire (ALT + Tab),
• Exécuter ce dernier en enfonçant la touche F5 du clavier par exemple,
• Atteindre pourquoi pas le sixième commercial avec la barre de navigation personnalisée,

Il s'agit de Monsieur Sansasse Lionel.

• Dans la zone du CA, inscrire un montant comme par exemple : 47500,
• Puis, le valider avec la touche Tabulation du clavier ou en cliquant dans un autre champ,

Comme vous pouvez le voir, le résultat de la prime calculée, tenant compte de multiples paliers, est aussitôt remontée. La communication entre Access et Excel est donc immédiate et fabuleuse.



Si vous ouvrez le classeur Excel, vous avez le plaisir de constater que la valeur transmise et la prime calculée ont bien été archivées. Et si vous réalisez une vérification rapide grâce à la grille des primes, vous constatez que le calcul est naturellement fort correct

Le code VBA complet que nous avons bâti pour établir la communication entre Excel et Access est le suivant :

Private Sub c_chiffre_AfterUpdate()
Dim fenetre As Excel.Application
Dim classeur As Excel.Workbook
Dim feuille As Excel.Worksheet
Dim chemin As String: Dim compteur As Byte
Dim test As Boolean

compteur = 4: test = False
chemin = Application.CurrentProject.Path &" \calculs-de-primes.xlsx"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)
Set feuille = classeur.Worksheets("Primes")
fenetre.Visible = False

Do While test = False
If (feuille.Cells(compteur, 4).Value = c_nom.Value And feuille.Cells(compteur,5).Value = c_prenom.Value) Then
test = True
Else
compteur = compteur + 1
End If
Loop

If test = True Then
feuille.Cells(compteur, 7).Value = c_chiffre.Value
c_prime.Value = feuille.Cells(compteur, 8).Value
Else
c_prime.Value = 0
End If

classeur.Save
fenetre.Application.DisplayAlerts = False
fenetre.Quit
Set fenetre = Nothing
Set classeur = Nothing
Set feuille = Nothing

End Sub