Convertir les montants numériques en texte en VBA Access

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


Sujets que vous pourriez aussi aimer :


Convertir les montants en toutes lettres avec VBA Access

Nous proposons ici d'améliorer les applications de facturation que nous avions conçues en VBA Access, en offrant la transcription textuelle des montants numériques des commandes. Et pour utiliser ce que nous avions déjà développé, nous proposons d'exploiter la fonction VBA Excel permettant de traduire les données numériques en toutes lettres.



Comme l'illustre la capture ci-dessus de l'application Access finalisée, le montant total de la commande du client est affiché sous les deux formes, dans la partie inférieure du formulaire. Et pour un développement productif, nous envisageons de faire communiquer Access et Excel par le code Visual Basic.

Sources et présentation du concept
Comme Excel, par le biais de la fonction VBA déjà conçue, est capable de réaliser cette transcription, l'application Access doit être en mesure de lui transmettre le montant numérique et de récupérer la donnée traduite en texte, afin de l'afficher sur le formulaire. Nous proposons donc de débuter avec des sources existantes, le formulaire Access d'une part et le classeur Excel d'autre part.

• Télécharger le fichier conversion-montants-textes.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous le constatez, la décompression fournit deux fichiers. Il y a tout d'abord la base de données Access nommée commandes-clients.accdb. Puis il y a le classeur Excel nommé convertisseur-nombres-textes.xlsm. L'extension xlsm de ce dernier confirme la présence de macros VBA, celles de la conversion des données numériques en lettres en effet.

• Double cliquer sur la base de données commandes-clients.accdb pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau jaune de sécurité,
• Dans le volet des objets Access sur la gauche, double cliquer sur le formulaire Clients pour l'afficher au centre de l'écran, en mode exécution,

Ce formulaire a été conçu sur la base d'une requête regroupant les quatre tables de la base de données. Ces tables sont reliées entre elles par le jeu des clés primaires afin d'obtenir les commandes d'un client, de connaître le détail d'une commande pour un client et enfin de piocher les articles du détail de la commande dans la table Catalogue. Un niveau de regroupement a été réalisé sur la clé primaire du numéro de client afin que tout le détail de ses commandes lui soit relié dans un sous formulaire attaché.



Comme l'illustre la capture ci-dessus, des zones de texte sont prévues en bas du formulaire, à droite des boutons de navigations. Mais elles sont vides pour l'instant. Celle de gauche doit consolider le montant total de la commande du client, en valeur numérique. Celle de droite doit traduire ce nombre en toutes lettres en faisant appel à Excel, par le biais de sa fonction VBA.

Consolider les données du sous formulaire
Comme nous l'avions appris dans certaines formations Access, nous pourrions très bien faire appel au générateur d'expression. Ce dernier permettrait de faire le lien entre un champ de synthèse du sous formulaire et un champ de résultat sur le formulaire. Mais comme les boutons de navigations ont été développés par le code VBA, nous proposons de consolider la somme des commandes d'un client de la même façon.



Comme l'illustre la capture ci-dessus, le principe consiste à réaliser une requête SQL de synthèse, destinée à sommer les valeurs du champ montant_com, lorsque le numéro de client du champ cli_com est identique au numéro de client en cours, sur le formulaire au moment de la consultation. Cette requête SQL avec clause Where doit être déclenchée par le code VBA.

• Tout à fait à gauche du ruban Accueil, cliquer sur la flèche du bouton Affichage,
• Dans la liste, choisir Mode création,
• Puis réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Access,

Nous affichons ainsi la page de code VBA associée au formulaire Clients, reconnu sous le nom d'objet Form_Clients, dans l'explorateur de projet sur la gauche.



Vous remarquez la présence des procédures de code pour les boutons de navigation, permettant de parcourir les clients et leurs commandes vers l'avant ou vers l'arrière. La procédure Form_Load existe de même, bien qu'elle soit encore vide. Elle permettra d'exécuter des instructions Visual Basic au chargement du formulaire.

Le calcul de la somme des montants pour un client, doit intervenir à l'occasion de tous ces événements, qu'il s'agisse de la navigation ou du chargement du formulaire. En effet, dans tous les cas, le client et ses commandes sont mis à jour, le recalcul doit donc être ordonné. C'est pourquoi, nous proposons de créer une procédure indépendante qui devra être appelée par chacune de ces procédures.

• En dessous de la procédure Suiv_Click, créer la procédure calculer_total, comme suit :

Private Sub calculer_total()
On Error Resume Next

End Sub

L'instruction On Error Resume Next permet d'ignorer les erreurs s'ils elles interviennent, pour éviter les plantages disgracieux. Lorsque le client n'a pas passé commande en effet, son numéro ne pouvant être trouvé, la requête Sql n'aboutira pas. C'est une façon de simplifier le codage.

Nous devons commencer par déclarer les variables nécessaires au traitement. Mais certaines d'entre elles sont particulières dans la mesure où il s'agit de variables objets destinées à manipuler les éléments de la base de données. Et comme nous l'avait appris la formation pour accéder aux données en VBA Access, une référence à une librairie est nécessaire.

• Cliquer sur le menu Outils en haut de la fenêtre de l'éditeur de code,
• Dans la liste, choisir Références,

Comme vous le remarquez, ces références ont déjà été ajoutées. Il s'agit de Microsoft ActiveX Data Objects 6.1 Library et de Microsoft Excel 16.0 Object Library. Les numéros dépendent de la version de votre système d'exploitation. S'ils diffèrent cela ne change rien. Mais notez que la première référence est indispensable pour accéder aux données de la base par le code et que la seconde est nécessaire pour établir la communication avec un classeur Excel précis.

• Fermer la boîte de dialogue des références,
• Dans les bornes de la procédure calculer_total, ajouter les déclarations de variables suivantes :

Dim base As Database: Dim ligne As Recordset

Grâce à la référence à la librairie Microsoft ActiveX Data Objects 6.1 Library, nous déclarons respectivement un objet pour désigner une base de données et un autre pour manipuler ses enregistrements.

Nous devons désormais instancier ces objets, afin qu'ils héritent des propriétés et méthodes permettant de manipuler les données de bases de données. Pour ce faire, nous répliquons les instructions de code VBA que nous avons apprises au travers de nombreuses formations.

• Après les déclarations, ajouter les deux affectations suivantes :

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT SUM(montant_com) AS tot_commande FROM Commandes WHERE cli_com=" & num_client.Value, dbOpenDynaset)

Grâce à la méthode CurrentDb de l'objet VBA Application, nous instancions l'objet base pour qu'il hérite de toutes les propriétés et méthodes afin de piloter la base de données en cours. Grâce à cela, nous exploitons sa méthode OpenRecordset afin cette fois d'instancier l'objet ligne. De fait, cet objet hérite de toutes les propriétés et méthodes afin de manipuler les enregistrements, selon notamment une requête SQL, dont la syntaxe est passée en paramètre de la méthode.

Il s'agit d'une requête de synthèse en témoigne l'exploitation de la fonction SQL SUM sur le champ montant_com de la table Commandes (FROM Commandes). Notez l'emploi de l'instruction As pour renommer ce champ de synthèse à la volée (AS tot_commande). L'objectif est de le désigner explicitement par le code VBA afin de récupérer la valeur totalisée qu'il renferme. Bien sûr cette somme ne peut pas se faire pour tous les enregistrements, d'où l'utilisation d'une clause WHERE dans la syntaxe SQL, afin de poser une condition sur le numéro de client, dont l'égalité doit être vérifiée (WHERE cli_com=" & num_client.Value). cli_com est le nom du champ dans la table, tandis que num_client correspond au nom du contrôle sur le formulaire. Sa propriété Value restitue son contenu, soit le numéro du client au moment de la demande.

Il s'agit maintenant d'accéder aux données sélectionnées par l'exécution de la requête SQL et plus spécifiquement, au montant consolidé pour le client en cours depuis le formulaire. Pour ce faire :

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

ligne.MoveFirst
montant_num.Value = ligne.Fields("tot_commande").Value

ligne.Close
base.Close

Set ligne = Nothing
Set base = Nothing

Comme nous l'avions appris, la méthode MoveFirst d'un objet Recordset permet de placer le pointeur de lecture, sur le premier enregistrement résultant de la requête Sql. Il n'y en a qu'un ici nécessairement mais la méthode de placement reste nécessaire. Ensuite, grâce à la propriété Fields de l'objet Recordset, nous pointons sur le champ précisément défini en paramètre. Il s'agit du champ de synthèse dont nous avons défini le nom à la volée dans la syntaxe SQL, grâce au mot clé AS. La propriété Value permet de récolter la valeur synthétisée contenue dans ce champ. Nous l'affectons au contenu de la zone de texte montant_num, là aussi grâce à sa propriété Value. Il s'agit du contrôle TextBox pour l'affichage de la somme des commandes en valeur numérique, situé à droite des boutons de navigation, en bas du formulaire.

Enfin, nous répliquons les instructions classiques que nous avons pris l'habitude d'exploiter lors de l'accès aux données. Toute connexion aux données doit être fermée à l'issue pour ne pas engorger les ressources. Nous exploitons donc la méthode Close des objets base et ligne. De même, nous réaffectons ces deux objets sur la valeur Nothing afin de les vider de la mémoire.

Cette procédure pour s'exécuter, doit être appelée. Ce calcul de synthèse doit intervenir sur tous les événements cités précédemment.

• Dans chacune des procédures : Form_Load, Der_Click, Prec_Click, Prem_Click et Suiv_Click, ajouter l'appel de la procédure calculer total,

Par exemple pour Form_Load et Der_Click, le code est le suivant :

Private Sub Form_Load()

calculer_total

End Sub

Private Sub Der_Click()
On Error Resume Next

DoCmd.GoToRecord acDataForm, "Clients", acLast
calculer_total

End Sub

Dans le cas des procédures de navigation (comme Der_Click), il est important que l'appel intervienne après le changement d'enregistrement.

• Enregistrer les modifications (CTRL + S) et basculer sur le formulaire,
• Enfoncer la touche F5 du clavier,
• Utiliser les boutons de navigation pour changer de client,

Comme vous le constatez, au chargement du formulaire comme au changement de client par les boutons de navigation, le montant total des commandes se met à jour grâce à l'appel de la procédure VBA calculer_total que nous venons de créer.

Procédure VBA de conversion avec Excel
Nous avons réussi à consolider le montant total des commandes pour chaque client et à inscrire le résultat numérique de synthèse sur le formulaire. Nous devons désormais exploiter cette donnée pour la transmettre à Excel qui doit se charger de la convertir en texte. Mais pour cela, nous devons connaître le nom du classeur, le nom de la feuille de destination et plus précisément la cellule dans laquelle cette donnée doit être inscrite. Nous proposons donc de découvrir le classeur Excel fourni avec les sources.

• Avant cela, afficher de nouveau le formulaire Access en mode création,
• Dans le dossier de décompression des sources, double cliquer sur le classeur convertisseur-nombres-textes.xlsm, pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous affichons ainsi la feuille Transcription du classeur. Un montant numérique est inscrit en B5. La fonction VBA Excel doit être inscrite en C5 afin de traduire cette donnée en toutes lettres. Lorsque nous aurons réalisé ce réglage, le code VBA Access devra donc transmettre le montant total du client en B5 et récupérer la valeur traduite en C5.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,

Vous y notez la présence d'un code VBA relativement dense et complexe consistant à traduire les valeurs numériques dans leur homologue textuel. La fonction qui nous intéresse se nomme NbEnLettres. Elle requiert plusieurs paramètres comme le montant à traduire et la devise dans laquelle le résultat doit être affiché. Cette fonction VBA, propre à ce classeur, doit être exploitée dans la cellule C5 de la feuille Excel.

• Fermer l'éditeur de code VBA Excel,
• Sur la feuille Transcription, sélectionner la cellule C5,
• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Insérer une fonction,
• Dans la boîte de dialogue qui suit, choisir la catégorie Personnalisées,
• Double cliquer sur la fonction NbEnLettres qui apparaît,
• Dans l'assistant fonction qui suit, sélectionner la cellule B5 pour l'argument Montant,
• Dans l'argument Devise, taper le chiffre 1 pour indiquer l'Euro,
• Le dernier argument étant facultatif, valider en cliquant sur le bouton Ok,
• Puis, enregistrer le classeur Excel (CTRL + S),

Comme vous le remarquez, le montant numérique est parfaitement retranscrit dans son équivalent textuel, qui plus est accompagné de la devise choisie. Nous obtenons le résultat suivant : Mille cinq cent quinze Euros et trente-trois centimes. Si vous modifiez la valeur en B5, sa traduction est instantanément mise à jour en C5. C'est donc ce processus que nous devons matérialiser depuis Access, par le code VBA, de façon à questionner ce classeur Excel.

• Fermer le classeur Excel et revenir sur le formulaire Access,

Remarque : Selon la version d'Excel, il est possible que vous trouviez la fonction NbEnLettres préfixée du nom du classeur. C'est moins gracieux, mais cela ne change rien à l'efficacité du calcul, une fois la formule posée.

Traduire les montants numériques par communication VBA
Nous avons précédemment vérifié que la librairie nécessaire (Microsoft Excel 16.0 Object Library) était déclarée dans les références du projet VBA Access. De fait, nous allons directement pouvoir déclarer les objets permettant de prendre le contrôle à distance d'un classeur Excel, par le code Visual Basic depuis Access.

• Revenir dans l'éditeur de code VBA Access,
• Dans la partie déclarative de la procédure calculer_total, ajouter les déclarations suivantes :

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

Comme nous l'avions vu dans la formation VBA Access pour communiquer avec Excel, nous créons un objet pour piloter Excel et un autre pour piloter spécifiquement un classeur Excel. C'est la raison pour laquelle nous avons aussi déclaré une variable de type String, afin de mémoriser le chemin d'accès du classeur à contrôler.

Il s'agit donc d'initialiser et d'affecter ces variables.

• Après le code précédent (Set base = Nothing), ajouter les instructions suivantes :

chemin = Application.CurrentProject.Path & "\convertisseur-nombres-textes.xlsm"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)

L'attribut Path de la propriété CurrentProject de l'objet VBA Access Application, retourne le chemin d'accès complet à l'application Access en cours. En effet, le classeur Excel est volontairement situé dans le même dossier que la base de données Access. Il ne nous reste plus qu'à concaténer (&) avec le nom du classeur, sans oublier l'antislash (\) pour accéder au dernier dossier. Ce chemin d'accès est mémorisé dans la variable chemin.

Ensuite, grâce à la fonction VBA CreateObject et son paramètre Excel.Application, nous instancions la classe qui permet à l'objet fenetre d'hériter de toutes les propriétés et méthodes nécessaires pour piloter Excel. D'ailleurs dans la foulée, sa propriété Workbooks délivre la méthode Open qui permet à l'objet classeur d'hériter des méthodes nécessaires pour piloter le classeur, dont le chemin d'accès est passé en paramètre de la méthode.

Il ne reste plus qu'à pointer sur les cellules de la feuille de ce classeur, dans un premier temps pour inscrire la valeur à traduire en B5 et dans un deuxième temps, pour récupérer la valeur transcrite en C5.

• Pour ce faire, à la suite du code, ajouter les deux instructions suivantes :

classeur.Sheets("Transcription").Range("B5").Value = montant_num.Value
montant_texte.Value = classeur.Sheets("Transcription").Range("C5").Value

Grâce à l'objet classeur et à ses propriétés hiérarchisées, nous parvenons à désigner la cellule B5 dans un premier temps. Nous exploitons tout d'abord sa propriété Sheets pour désigner la feuille par son nom en paramètre. Puis, la propriété Range permet de définir une plage ou une cellule comme ici, dont la référence lui est passée en argument. Enfin, la propriété Value comme souvent permet d'accéder à son contenu que nous affectons de la valeur du montant numérique, issue du contrôle du formulaire, dans lequel nous avions précédemment écrit. En d'autres termes, nous venons d'écrire en B5 de la feuille Excel, le montant total des commandes du client en cours depuis le formulaire Access. Comme un calcul Excel est dynamique, nous récupérons dans la foulée le résultat de la fonction VBA NbEnLettres calculé en C5, grâce aux mêmes propriétés. Cette traduction en lettres est affectée au contenu du contrôle montant_texte du formulaire Access, grâce à sa propriété Value.

Pour coder proprement, il est important de fermer les objets ouverts et de les vider, comme nous l'avons fait après avoir interrogé la base de données.

• Pour cela, à la suite du code VBA, ajouter les instructions suivantes :

fenetre.Application.DisplayAlerts = False

classeur.Close
fenetre.Quit

Set fenetre = Nothing
Set classeur = Nothing

Tout d'abord, la propriété booléenne DisplayAlerts réglée à False permet de ne pas afficher le message Excel consistant à confirmer l'enregistrement du classeur. En effet, nous avons réalisé des modifications sur la feuille et nous souhaitons que le déroulement du programme soit transparent pour l'utilisateur. Puis, nous utilisons les méthodes Close et Quit pour respectivement fermer le classeur et quitter Excel. Enfin, nous vidons proprement les objets en les réaffectant à la valeur Nothing, pour les décharger correctement de la mémoire de l'ordinateur. Il ne reste plus qu'à tester :

• Enregistrer les modifications(CTRL + S) et basculer sur le formulaire,
• Exécuter ce dernier en enfonçant la touche F5 du clavier,
• Naviguer au travers des clients à l'aide des boutons de navigation,

Moyennant un petit intervalle de temps, du fait de l'établissement de la communication entre Access et Excel, tous les totaux sont parfaitement convertis dans leur équivalent textuel, le tout synthétisé sur l'interface du formulaire Clients.

Nous aurions pu réécrire directement le code VBA de la fonction NbEnLettres dans Access mais finalement, comme l'objectif d'Excel est de réaliser des calculs, nous exploitons chacun des logiciels dans leur domaine de savoir-faire.

Le code complet de la procédure calculer_total est le suivant. Il reste relativement sobre sachant qu'il intègre la requête Sql destinée à consolider les montants numériques dans un premier temps.

Private Sub calculer_total()
On Error Resume Next
Dim base As Database: Dim ligne As Recordset
Dim fenetre As Excel.Application: Dim classeur As Excel.Workbook
Dim chemin As String

Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("SELECT SUM(montant_com) AS tot_commande FROM Commandes WHERE cli_com=" & num_client.Value, dbOpenDynaset)

ligne.MoveFirst
montant_num.Value = ligne.Fields("tot_commande").Value

ligne.Close
base.Close

Set ligne = Nothing
Set base = Nothing

chemin = Application.CurrentProject.Path & "\convertisseur-nombres-textes.xlsm"
Set fenetre = CreateObject("Excel.Application")
Set classeur = fenetre.Workbooks.Open(chemin)

classeur.Sheets("Transcription").Range("B5").Value = montant_num.Value
montant_texte.Value = classeur.Sheets("Transcription").Range("C5").Value

fenetre.Application.DisplayAlerts = False
classeur.Close
fenetre.Quit

Set fenetre = Nothing
Set classeur = Nothing

End Sub