L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Power Query

Remplacer le VBA par Power Query

16 décembre 2016
Imprimer E-mail

Petite astuce pour le fun

Dans cet article je vais vous montrer comment utiliser Power Query à la place du VBA pour automatiser une action : Soit une base de données qui doit être alimenté automatiquement à partir d'un formulaire.

Base de données exemple :

  • Pour que l'astuce fonctionne, il est important que celle-ci contienne une colonne dite identifiante ou clef primaire. C'est à dire qu'elle contient une donnée unique pour chaque enregistrement (aucun doublon). Ici c'est simplement un nombre qui sera incrémenté.
  • Cette base de données doit être sous forme de Tableau, sur l'onglet Accueil, cliquez sur le bouton Mettre sous forme de tableau.
  • Vous devez également donné un nom explicit à ce tableau. Dans l'exemple : t_BD

Power Query - La base de données

Formulaire de saisie :

Ce formulaire contient plusieurs colonnes :

  • la 1ère pour la saisie des nouveaux enregistrements ou modification d'un enregistrement existant
  • la 2e affiche les données de l'enregistrement sélectionné
  • la 3e calcule les données à enregistrer dans la Base de données. Cette colonne peut être masquée. Chaque cellule de cette colonne (entouré en rouge) est nommée. Ceci est important pour le fonctionnement avec Power Query.
    Powr Query - Formulaire de saisie

Le principe de fonctionnement est le suivant :

  • Pour un nouvel enregistrement :
    • vous n'inscrivez rien dans la zone Code.
    • Vous inscrivez les données de cet enregistrement dans la colonne sur fond blanc.
  • Pour modifier un enregistrement :
    • vous inscrivez le n° de l'enregistrement dans la zone Code.
    • la colonne en gris affiche les informations actuelles de cet enregistrement.
    • vous saisissez, dans les zones sur fond blanc, uniquement les données à modifier (comme dans la figure ci-dessous)
      Power Query Formulaire de saisie
  • Il faut ensuite une commande pour enregistrer les modifications dans la base de données. En VBA, vous pouvez écrire un code et affecter la macro à un bouton ajouté sur ce formulaire. Mais pour Power Query, il suffira de cliquer sur le bouton Actualiser tout de l'onglet Données.

1ère étape de la construction des requêtes

La 1ère requête doit être établie à partir de la base de données :

  1. Cliquez sur la commande Données > A partir d'un tableau.
    Power Query - Requête depuis un tableau
  2. Ne modifiez rien à la requête
  3. Renommez la requête Data
  4. Ouvrez le bouton Accueil > Fermer et charger puis sélectionnez Fermer et charger dans ...
  5. Sur la fenêtre qui s'affiche, sélectionnez Table et Nouvelle feuille de calcul. Il est inutile d'ajouter ces données au modèle de données.
    Power Query - Destination de la requête
  6. Une nouvelle feuille s'ajoute à votre classeur avec les même données que votre tableau source.
  7. Renommez ce nouveau tableau en t_Data. Ceci n'est pas obligatoire, mais cela vous évitera de confondre la requête et le tableau
    Renommer table Excel

Nous avons maintenant 2 fois notre base de départ. Mai au final, le tableau résultat doit être égal à lui-même + le nouvel enregistrement du formulaire de saisie.

Une requête Power Query égale à elle-même

  1. Dans le volet des requêtes, effectuez un clic-droit et sélectionnez Modifier
  2. Dans le volet étapes, sélectionnez l'étape Source
  3. Dans la barre de formule, modifiez le nom de la table Source : remplaceez "t_BD" par "t_Data"
    Power Query - Modifier la source de la requête
  4. Validez votre formule avec la coche
  5. Fermer et charger la requête.

2e étape de de préparation

Il faut maintenant modifier toutes les formules du formulaire de saisie afin que celui-ci fonctionne avec t_Data au lieu de t_BD

  1. Sur l'onglet Accueil > Rechercher et Sélectionner > Remplacer
  2. Remplacez t_BD par t_Data puis cliquez sur Remplacer Tout.
  3. Vous devez également modifier la source de la liste déroulante de la zone Code :
    • onglet Formules > Gestionnaire de noms
    • sélectionnez lst_Codes
    • remplacez t_BD par t_Data
      Excel - Modifier la source d'un nom
    • validez avec la coche
    • Fermez
  4. Vous pouvez maintenant supprimer la feuille BD et renommer la feuille Feuil1

Requête sur le formulaire de saisie

Sur l'onglet Données, sélectionnez Nouvelle requête > à partir d'autres sources > Requête vide

Nous allons maintenant construire la requête qui va récupérer les données saisies dans le formulaire. Ces données à récupérer se trouve dans les cellules de la colonne H et ont toutes étés nommées :

  1. dans la barre de formule, saisissez : = Excel.CurrentWorkbook()
  2. validez avec la coche ou Entrée
  3. La liste des noms de plages et tableau s'affiche
    Power Query - Excel.CurrentWorkbook
  4. Vous devez ne conserver que les noms ne commençant pas par lst_ ou t_ : Ouvrez le filtre de la colonne Name et sélectionnez Filtre de texte > Ne commence pas par
  5. Saisissez lst_ et t_
    Power Query - Filtre
    Le tableau se limite maintenant aux données que nous voulons récupérer.
  6. Cliquez sur le bouton situé à droite de la colonne Content afin de la développer
    Power Query - Développer une colonne de type Table

Transposer des lignes en colonnes

Les données se trouvent sur des lignes différentes et devraient être sur des colonnes différentes et 1 seule ligne afin de pouvoir ensuite les cumuler à notre tableau t_Data :

  1. Ordonnez les colonnes en déplaçant la colonne Name au début
  2. sur l'onglet Transformer sélectionnez la commande Transposer
    Power Query - Transposer des lignes en colonnes
  3. Pour remonter la 1ère ligne en titre, cliquez sur le bouton Transformer > Utiliser la première ligne pour les en-têtes
  4. Réordonnez les colonnes afin qu'elles soient dans le même ordre que dans le tableau t_Data : Code, Nom, Prenom, Genre, Club, Age, Piscine, Sauna, Hammam, Massage, Cotisation
  5. Renommez la colonne Prenom en Prénom afin qu'elle ait le même nom que dans t_Data. Il est important que chaque colonne ait le même nom dans les 2 tables avant de les cumuler, sinon on démultipliera les colonnes.
  6. Modifiez le type de données de chaque colonne afin qu'elles soient identiques à la requête Data.
  7. Renommez votre requête Formulaire
  8. Cliquez sur Accueil > Fermer et charger >Fermer et charger dans ...
  9. Cette fois, choisissez de Créer une connexion uniquement.

Cumuler 2 requêtes

Pour retrouver les données du formulaire dans la base de données, nous devons cumuler les 2 requêtes : 

  1. Dans le volet des requêtes, effectuez un clic droit sur la requête Data et sélectionnez Modifier
  2. Accueil > Ajouter des requêtes.
  3. sélectionnez la table Formulaire
    Power Query - Cumuler (ajouter) des requêtes
  4. Fermez et enregistrez votre requête

Vous pouvez maintenant tester le fonctionnement en saisissant un nouvel enregistrement sur la feuille Saisie puis cliquez sur le bouton Données > Actualiser tout

Mais si vous tentez d'effectuez la même chose en sélectionnant au préalable un code existant sur la feuille Saisie, vous pouvez constater que les données sont ajoutées et non modifiées. Nous devons donc ajouter une étape dans la requête Data afin de supprimer la ligne que vous voulez modifier avant de l'ajouter.

Erreur en cas de modification

Filtrer une requête avec une autre requête

  1. Dans le volet des requêtes, effectuez un clic droit sur la requête Data et sélectionnez Modifier
  2. Sélectionnez l'étape Type modifié
  3. Acceuil > Fusionner des requêtes
  4. Répondez Insérez à la boite de dialogue qui s'affiche
  5. Sélectionnez la table Formulaire et le champ Code dans chaque table.
  6. Mais surtout sélectionnez le type Gauche opposée pour le type de jointure. Ce qui va nous permettre d'exclure toutes les lignes de la 1ère table ayant le même code dans la 2e table.
    Power Query - Fusion de requêtes
  7. Cette fusion a bien effectué le filtre, mais a également ajouté une colonne dont nous n'avons pas besoin. Supprimez-la et acceptez l'insertion de cette nouvelle étape.
  8. Fermez et enregistrez votre requête.

Vous pouvez maintenant tester et ajouter ou modifier des enregistrements, un seul clic sur le bouton Actualiser Tout suffit à renseigner la base de données.

Le seul inconvénient qui reste est le "non effacement" du formulaire après un clic sur le bouton Actualiser Tout

Vous pouvez télécharger le fichier de base ici