Dans l’un de mes derniers articles, je vous expliquais comment utiliser les boucles VBA pour créer un rapport personnalisé pour plusieurs départements/employés/vendeurs/produits/etc. en un seul clic Depuis ce temps, j’ai eu plusieurs questions sur l’application de cette méthode pour générer des fichiers Excel plutôt que des fichiers pdf. C’est effectivement possible de créer plusieurs fichiers Excel personnalisés en un seul clic avec les boucles VBA. Je vais vous démontrer ici comment faire.
Voici comment créer une boucle VBA pour automatiser vos rapports Excel
1. Créer le modèle de données
Pour y arriver, je vais utiliser des données budgétaires de l’année 2022 et des résultats réels de 2022 pour créer des gabarits pour préparer le budget 2023 par magasin. Dans un premier temps, je crée un modèle de données qui met en relation chacune des sources de données. Ce modèle de données me permet de créer des rapports contenant les résultats budgétés et les résultats réels de 2022 à l’intérieur d’un même tableau croisé dynamique.
Ce tableau croisé dynamique pourra servir de base à la création du budget 2023 par magasin et par département.
Pour en savoir plus sur la connexion à des données externe et la création de modèle de données dans Excel, je vous invite à jeter un coup d’œil aux formations Introduction à Power Query et Introduction à Power Pivot.
2. Définir la liste des magasins
Je souhaite maintenant automatiser la création de fichiers Excel pour chaque magasin. Pour y arriver, je vais utiliser le même principe de boucle que dans mon précédent article. Je débute par définir la liste de magasins pour lesquels je dois créer un fichier. Pour y arriver, je nomme la plage de numéro de magasins « ListeMagasins »
3. Définir les variables
Je peux maintenant débuter mon code en définissant les variables à utiliser ainsi que la plage de numéros de magasins.
4. Débuter la boucle VBA
Ensuite, je débute la boucle en indiquant que je veux que ces actions se répètent pour tous les magasins de la plage « ListeMagasins » qu’on a identifiée plus tôt. Ensuite, je dois modifier le filtre du tableau croisé dynamique. Comme l’objectif est de créer un fichier par magasin, on veut s’assurer de modifier le filtre de magasin avant de créer le fichier Excel personnalisé.
Lorsqu’on interagit avec les tableaux croisés dynamiques, je débute habituellement par utiliser l’enregistreur de macro pour créer le code initial de l’action à accomplir. Dans ce cas-ci, je veux modifier le filtre du numéro de magasin donc j’ai démarré l’enregistreur, j’ai modifié le filtre pour passer du magasin 103 au magasin 104 et j’ai observé le résultat. Le voici :
À partir de ce code, le seul changement que j’ai fait est de rendre « dynamique » la sélection du magasin. Le code final ressemble à ceci :
5. Créer un fichier Excel propre au magasin sélectionné
La deuxième partie de ma boucle consiste à créer un fichier Excel propre au magasin sélectionné. Encore une fois, pour me donner un coup de main, j’ai utilisé l’enregistreur de macro pour écrire le code initial. Ça m’a donné le code suivant :
On constate donc que le bout code « .Copy » génère un nouveau fichier Excel contenant une copie de l’onglet « Sheet1 ». Ensuite, le bout de code « ActiveWorkbook.SaveAs » sert à enregistrer le nouveau fichier à l’endroit souhaité avec un nom personnalisé au contenu. Finalement, le bout de code « ActiveWindow.Close » sert à fermer le nouveau fichier Excel et à rebasculer dans le fichier Excel maître.
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. |
|---|
En modifiant légèrement le code, je rends dynamique le nom du fichier pour qu’il porte le bon numéro de magasin selon le filtre qui a été appliqué au tableau croisé dynamique et le tour est joué !
6. Fermer la boucle VBA
À ce stade-ci, toutes les actions à répéter ont été codées. Reste à fermer la boucle avec le code suivant :
La macro est maintenant prête à être lancée! On peut voir que tous les fichiers ont été crées et le budget 2023 est maintenant prêt à être produit.
Dans cet exemple, j’ai décidé de conserver les données sous un format de tableau croisé dynamique. Par contre, si les usagers avec qui vous partagez ces fichiers n’ont pas accès au fichier « maître », ils ne pourraient pas actualiser le tableau croisé dynamique et pourront potentiellement rencontrer des erreurs.
Dans un cas semblable, vous pourriez copier/coller les données en valeurs pour éviter ce problème.
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é.
