Vous êtes plusieurs à nous approcher pour automatiser la création de rapports personnalisés dans Excel ou dans Power BI. Le requis est généralement très simple : Vous avez développé un rapport qui doit être répliqué et personnalisé pour tous les employés de l’entreprise ou pour tous les départements de l’entreprise. Dans cet article, je vais vous démontrer comment vous pouvez utiliser Excel et VBA dans un cas comme celui-là.
Création du rapport personnalisable
Tout d’abord, vous devez créer votre rapport de façon à ce qu’il soit personnalisable. Concrètement, ça veut dire que vous avez 1 seule variable à changer pour que tout votre rapport soit personnalisé pour un nouvel employé.
Pour cet article, j’ai préparé un rapport sommaire de ventes très simple qui ressemble à ceci :
Les valeurs personnalisables sont les suivantes :
- Nom et prénom de l’employé
- Ventes de la période
- Objectif annuel
Le pourcentage d’atteinte de l’objectif est calculé par la division des ventes de la période par l’objectif annuel.
Le graphique à barres est lié aux valeurs de ventes de la période et d’objectif annuel.
Toutes les formules de ce rapport sont liées à une table de ventes sous-jacente qui ressemble à ceci. J’ai nommé la table « InformationsVentes ».
La seule variable dont j’ai besoin pour générer mon rapport personnalisé est le numéro de l’employé. Je l’ai positionné dans une plage que j’ai nommée « NoEmploye »
Voici les formules pour générer les valeurs personnalisables :
Nom et prénom de l’employé :
="Pour "&RechercheV(NoEmploye,InformationsVentes,2,FAUX)&" "&RechercheV(NoEmploye,InformationsVentes,3,FAUX)
Ventes de la période :
=SOMME.SI.ENS(InformationsVentes[Ventes],InformationsVentes[No Employé],NoEmploye)
Objectif annuel :
=SOMME.SI.ENS(InformationsVentes[Objectif],InformationsVentes[No Employé],NoEmploye)
Le rapport est donc 100% dynamique en fonction du numéro d’employé. Essayons avec quelques employés pour tester les résultats.
Automatiser la génération de rapports Excel par VBA
Grâce à VBA, on peut automatiser la génération de tous les rapports en un seul clic. Ça peut être très pratique lorsqu’on doit générer ces rapports sur une base régulière et qu’on veut éviter de tout faire manuellement chaque fois.
Dans cet exemple, je vais vous montrer comment générer un rapport en format pdf pour chaque employé.
1. Définir la liste d’employés dans VBA
Lorsqu’on travaille avec des données stockées dans une table, on doit débuter par nommer les plages qui nous intéressent. Dans ce cas-ci, la plage qui m’intéresse est la liste de numéros d’employé qui se situe dans la première colonne de la table InformationsVentes.
Pour nommer la plage, je sélectionne tous les numéros d’employés et j’inscris le nom de mon choix dans la zone d’adresse de la plage.
Cette plage sera ajustée automatiquement lorsque de nouveaux employés seront ajoutés à la liste. Par exemple, si j’ajoute l’employé #135, voyons l’impact sur la plage.
On voit que la plage s’est ajustée automatiquement pour inclure l’employé 135. Cet ajustement automatique est possible parce que la référence de la plage nommée est liée à la colonne «No Employé » de la table « InformationsVentes ». Comme la colonne No Employé s’ajuste automatiquement lors de l’ajout d’employés, la plage s’ajuste également !
Pour en savoir plus sur les propriétés des tableaux dans excel, je vous invite à consulter l’article suivant : Mettre vos données sous forme de tableau dans Excel, un incontournable
Dans notre code VBA, on doit d’abord définir la variable comme étant une plage de valeurs. Je peux ensuite indiquer quelle plage utiliser à l’intérieur des données. J’utilise le code suivant pour ce faire :
Si vous n’êtes pas à l’aise avec la création de macros VBA ou la définition de variables, je vous invite à jeter un coup d’œil à la formation Excel VBA (niveau 1)
Vous souhaitez créer et personnaliser des outils de gestion automatisés dans Excel pour mieux répondre aux besoins spécifiques de votre entreprise ? Apprenez à les développer et les présenter avec des interfaces conviviales pour les usagers en suivant nos formations en programmation de macros VBA. |
|---|
2. Définir les actions à répéter pour tous les employés
La magie de VBA s’opére lorsqu’on peut automatiser la répétition de certaines actions plusieurs fois sans effort. Dans notre exemple, l’action à répéter pour chaque employé est l’impression en pdf du rapport personnalisé.
Pour ce faire, j’utilise le code suivant :
Il s’agit du code requis pour exporter l’onglet actif en pdf. Si on décortique le code, on voit que plusieurs paramètres sont présents :
- Type : type de fichier dans lequel l’exportation sera générée
- Filename : Chaîne qui indique le nom du fichier à enregistrer. (Dans mon exemple, cette portion est fixe mais on va la rendre dynamique un peu plus tard)
- IncludeDocProperties : Affectez la valeur Truepour indiquer que les propriétés du document doivent être incluses
- IgnorePrintAreas : Si la valeur est False, utilise les zones d’impression définies lors de la publication.
- OpenAfterPublish : Si la valeur est True, affiche le fichier dans la visionneuse après sa publication.
Pour en savoir plus sur la méthode ExportAsFixedFormat, je vous invite à consulter la documentation de Microsoft.
3. Créer une boucle
Maintenant que l’action à répéter a été créée, on doit créer une boucle dans VBA qui nous permettra d’appliquer le code à tous nos employés. Ça veut donc dire que la macro va produire un fichier pdf pour chaque employé.
Dans ce cas-ci, on utilisera une boucle du type For Each. En utilisant la boucle For Each, on demande à la macro de passer à travers toute la liste d’employés, de modifier la cellule contenant le numéro d’employé (mettant à jour le rapport) et d’appliquer l’action d’imprimer en pdf.
Lorsque toutes les actions à répéter ont été effectuées, la ligne « Next Employe » permet de recommencer la boucle avec le prochain numéro d’employé.
4. Personnaliser le nom du fichier
La dernière étape à effectuer serait de personnaliser le nom du fichier. Je suggère de créer un nom du type : Date du jour – No employé – Nom d’employé
Pour ce faire, nous allons créer ces valeurs dans le fichier Excel et nommer ces plages de façon à pouvoir les réutiliser facilement. J’ai nommé les plages DateJour et NomEmploye
Nous pouvons ensuite définir les variables qui contiendront toutes ces valeurs.
On va ensuite attribuer les valeurs dans les variables de la façon suivante :
Finalement, on peut modifier le nom du fichier de la façon suivante :
On peut observer le résultat dans le répertoire où les dossiers ont été créés.
Le code complet ressemble à ceci :
Voici une courte vidéo qui vous démontre comment créer des rapports Excel personnalisés avec VBA. Bon visionnement !
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour une introduction à la programmation avec le langage VBA, qui permet de sauver du temps et de réduire les risques d’erreurs en automatisant certaines tâches et certains processus, afin de développer des solutions robustes dans Excel, suivez la formation Excel – VBA (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
