Fonctions Excel dans une requête Access

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


Sujets que vous pourriez aussi aimer :


Fonctions Excel dans Access

Excel est un fabuleux calculateur entre autres. Il met à disposition un très grand nombre de fonctions de calcul puissantes. Et même si Access n'est pas en reste, sa source de propositions se tarit inévitablement bien avant les possibilités offertes par Excel. C'est la raison pour laquelle cette nouvelle astuce tient à démontrer comment exploiter les fonctions Excel dans Access, avec une facilité déconcertante.

Source et objectif
Pour mener à bien ces travaux, nous proposons de récupérer une petite base offrant déjà des données à manipuler.

• Télécharger le fichier fonctions-excel-access.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Access,
• Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,

En consultant le volet de navigation sur la gauche de l'écran, vous constatez que cette base de données n'est constituée que d'une table nommée Produits.

• Double cliquer sur la table Produits pour l'afficher en mode feuille de données,

Cette table archive des articles de vêtements à la vente. C'est le deuxième champ qui retient notre attention. Il concerne les désignations des produits et se nomme produit_nom. Toutes ces désignations sont inscrites en majuscules. Pour une présentation moins agressive, nous souhaitons transformer cette casse. L'objectif est de ne conserver que la première lettre de chaque mot en majuscule. Pour cela, nous pouvons bâtir une requête avec un champ calculé exploitant une fonction Access de traitement de texte. En scrutant les propositions du générateur d'expression, vous constaterez bien la présence des fonctions Majuscule et Minuscule. En revanche la fonction NomPropre n'existe pas. Avec Excel, elle permet précisément de transformer chaque première lettre de mot en majuscule tout en contraignant les autres en minuscules.

Appeler une fonction Excel
L'astuce consiste à créer une fonction personnalisée dans un module VBA. Cette fonction personnalisée doit être capable d'appeler la fonction Excel souhaitée. Pour cela, nous devons ajouter une librairie spécifique au projet.

• En haut de la fenêtre Access, cliquer sur l'onglet Créer pour activer son ruban,
• Dans la section Macros et code du ruban, cliquer sur le bouton Module,

Nous basculons ainsi dans l'éditeur de code Visual Basic Access.

• En haut de l'éditeur, cliquer sur le menu Outils,
• Dans la liste des propositions, choisir Références,

Une boîte de dialogue apparaît. Toutes les librairies disponibles y sont organisées par ordre alphabétique croissant.

• Cocher la case de la référence Microsoft Excel 16.0 Object Library,

Le numéro (16.0) est variable. Il dépend de la version d'Office installée sur votre machine.

• Cliquer sur le bouton Ok de la boîte de dialogue pour ajouter la référence,

Grâce à cette liaison, nous pouvons désormais communiquer facilement avec Excel par le biais du code VBA Access. La classe que nous venons d'ajouter offre des objets dédiés avec des propriétés et méthodes.

• Dans la feuille de code, créer la fonction suivante :

Function PremMaj(Le_Texte As String) As String

End Function

Comme vous le savez, c'est le mot clé Function qui permet d'annoncer la fonction à suivre. Arbitrairement, nous la nommons PremMaj. Et nous la déclarons avec un paramètre (Le_Texte) en attente. C'est lui qui permettra de transmettre le champ de la désignation à convertir, lorsque nous appellerons cette fonction depuis un champ calculé d'une requête Access. Cette fonction doit retourner un texte transformé. C'est pourquoi nous la déclarons en tant que tel (As String).

• Entre les bornes de cette fonction, ajouter l'instruction VBA suivante :

PremMaj = Excel.WorksheetFunction.Proper(Le_Texte)

En VBA, la valeur retournée après calcul se fait par le nom de la fonction. C'est pourquoi nous l'affectons tout d'abord. Et pour ce faire, nous exploitons l'objet Excel de la librairie précédemment ajoutée. Puis, nous descendons dans la hiérarchie pour atteindre l'objet dérivé WorksheetFunction. Dès lors, nous pouvons appeler la méthode Proper. En VBA, les fonctions sont disponibles dans leur version originelle. Elles ne sont pas traduites. Proper est la version anglaise de la fonction NomPropre. Dès lors, nous lui passons le texte à transmettre en argument de la fonction PremMaj. C'est ainsi que la fonction affectée pourra retourner la chaîne à la casse convertie.

• Enregistrer le module (CTRL + S) sous la désignation : NomPropre,
• Puis, fermer l'éditeur VBA Access,

De retour sur la table Access, vous notez la présence du module fraîchement créé dans le volet de navigation.

Fonction Excel dans requête Access
Nous devons maintenant créer une requête sélection. Son rôle est de restituer tous les champs de la table Produits à l'exception du champ Produit_nom. En lieu et place de ce dernier, c'est un champ calculé exploitant la fonction PremMaj qui doit transformer la casse de ses intitulés.

• Dans la section Requêtes du ruban Créer, cliquer sur le bouton Création de requête,
• Dans la boîte de dialogue qui suit, sélectionner la table Produits,
• Cliquer alors sur le bouton Ajouter puis sur le bouton Fermer,
• Dans la représentation de la table, sélectionner tous les champs sauf Produit_nom,
• Glisser la sélection sur la grille de requête,

• Dans la zone Champ à droite du champ produit_code, saisir la syntaxe suivante :

maj_nom: PremMaj([produit_nom])

Le nom d'un champ calculé (maj_nom) est nécessairement suivi du symbole deux points (:) pour annoncer le traitement à suivre. Nous appelons alors la fonction PremMaj à laquelle nous passons la désignation à transformer (produit_nom) pour chaque enregistrement. Le nom de ce champ doit obligatoirement être inscrit entre crochets.

• Déplacer ce champ par son étiquette entre les champs produit_prix et produit_poids,
• Enregistrer la requête (CTRL + S) sous le nom r_produits,
• A gauche du ruban Créer, cliquer sur le bouton Exécuter,

Nous affichons ainsi les résultats de la requête en mode Feuille de données. Elle restitue toutes les informations de la table Produits au détail près que les désignations ont été transformées. Seules les premières lettres des mots apparaissent désormais en majuscules.

Grâce à ces démonstrations, nous savons désormais qu'il est possible d'exploiter n'importe quelle fonction de calcul Excel dans Access. Mais ne n'oubliez pas, ces fonctions doivent être appelées par leur nom anglais.