Comment automatiser des rapports dans Excel

Informations concernant l'auteur.e

L'une des nombreuses fonctionnalités de Microsoft Excel est la possibilité d'automatiser des rapports. Vous pouvez créer des feuilles de calcul interactives pour simplifier la capacité d'autres utilisateurs à entrer des données dans ses classeurs et pouvez également automatiser la génération de ses rapports. Ces deux fonctionnalités requièrent une certaine connaissance de Visual Basic.

Méthode 1

Méthode 1 sur 2:
Créer une feuille de calcul interactive

1
Décidez de la disposition de votre feuille de calcul. Votre feuille de calcul devrait être définie de telle sorte que les autres personnes qui l'utiliseront puissent trouver rapidement les champs dont elles ont besoin pour entrer des données.
- Vous pouvez définir votre tableur horizontalement ou verticalement. La plupart des utilisateurs préfèrent la disposition verticale qu'ils trouvent plus simple à utiliser, en particulier si vous prévoyez d’imprimer votre feuille de calcul.
2
Créez des étiquettes de texte pour votre feuille de calcul. Disposez une étiquette en haut de chaque colonne, ainsi qu'une étiquette dans la cellule située à gauche de chaque cellule dans la colonne où vous souhaitez placer les champs de saisie.
3
Appuyez simultanément sur les touches Alt et F11. Cette manipulation ouvrira l'éditeur Microsoft Visual Basic.
4
Double-cliquez sur Ce classeur. Vous le trouverez dans le volet Projet -Projet VBA en haut à gauche de votre écran. Cela ouvrira une fenêtre de codage dans la section principale de l'éditeur.
5
Sélectionnez Procédure dans le menu Insertion. Cela affichera la boite de dialogue Ajouter une procédure.
6
Entrez un nom pour la procédure dans le champ prévu à cet effet. Donnez à la procédure un nom significatif, tel que « SommeDépenses », si votre feuille de calcul interactive doit vous permettre de déclarer vos frais de voyage. Cliquez sur OK pour fermer la boite de dialogue.
- Votre nom de procédure ne peut pas inclure d'espaces, mais vous pouvez utiliser un trait bas (_) à la place d'un espace.
- Une fois que la boite de dialogue Ajouter une procédure se fermera, vous verrez une ligne intitulée « Sous-ensemble public » suivie du nom de votre procédure s'afficher dans votre feuille de calcul. Sous cette ligne s'affichera un espace et les mots « fin du sous-ensemble ».
7
Entrez un code pour chaque champ de saisie dans la feuille de calcul. Vous devrez écrire deux lignes de code pour chaque entrée.
- La première ligne de code prendra la forme "Rang("nom de la cellule"). Sélectionner", où "nom de la cellule" représentera la cellule dans laquelle ira le champ de saisie. Cela devrait être la cellule immédiatement à droite d'une étiquette de texte. Si vous avez une étiquette de texte dans la cellule A2, vous placerez un champ de saisie dans la cellule B2 "Rang("B2").Sélectionner". Incluez les guillemets autour du nom de la cellule, mais pas ceux autour de l'énoncé de code complet.
- La deuxième ligne de code prendra la forme suivante "Valeur.CelluleActive = CaseDonnées("Entrée")", où "Entrée" représente le texte qui indique à l'utilisateur quel type de données entrer dans la cellule. Par exemple, si la cellule d'entrée est destinée aux frais de repas, vous devez remplacer "Entrée" par "Entrer le total de tous les repas, y compris les pourboires". (Incluez les guillemets autour de la présentation du type de données, mais pas ceux entourant la formule complète).
8
Entrez un code pour chaque champ de calcul. Vous utiliserez à nouveau les mêmes deux lignes que celles décrites ci-dessus, mais cette fois, votre Valeur.CelluleActive sera une fonction de calcul ou numérique, telle que SOMME, et non la fonction CaseDonnées utilisée pour afficher une invitation de saisie.
9
Ajoutez une ligne de code pour la sauvegarde de la feuille. Le format sera "Classeur.Actif.EnregistrerSous NomFicher:="NomFicher.xls", où "NomFichier" représente le nom de votre feuille de calcul interactive. (Incluez les guillemets autour de "NomFicher.xls", mais pas ceux encadrant la formule complète).
- Si vous utilisez Excel 2007 ou une version ultérieure, vous pouvez remplacer le format « .xlsx » par « .xls », mais si certaines personnes qui utilisent votre feuille de calcul interactive utilisent Excel 2003 ou une version antérieure, elles ne pourront pas utiliser la feuille de calcul sans un programme permettant la lecture de plug-in.
10
Appuyez simultanément sur les touches Alt et Q. Cela permettra de fermer l'éditeur Visual Basic.
11
Appuyez simultanément sur les touches Alt et F8. Cela ouvrira la boite de dialogue Macro.
12
Cliquez sur le nom de votre procédure dans la liste Macro. Si la procédure que vous venez de créer est la seule de la liste, elle sera automatiquement sélectionnée.
13
Cliquez sur le bouton Options. Vous serez invité à entrer un caractère de clavier à utiliser comme raccourci avec la touche Ctrl. Choisissez une lettre significative qui n'est pas déjà utilisée comme caractère de raccourci, tel que la lettre « e » pour « entrée ».
14
Cliquez sur OK pour fermer la boite de dialogue Options de macro. Vous pouvez maintenant partager votre feuille de calcul interactive avec ceux qui l'utiliseront. Après l'avoir ouvert, ils pourront utiliser la touche de raccourci pour activer l'entrée et suivre les instructions que vous avez créées pour entrer leurs données.

Publicité

Méthode 2

Méthode 2 sur 2:
Automatiser la génération de rapports

1
Créez votre rapport dans un tableau croisé dynamique. Les tableaux croisés dynamiques sont conçus pour résumer les données afin de vous permettre de comparer vos données chiffrées et d'identifier les tendances. Votre tableau croisé dynamique doit être connecté à des données de votre tableur ou des données importées à partir d'une base de données.
2
Créez un script Visual Basic pour ouvrir et fermer le rapport. Votre script doit effectuer les fonctions présentées ci-dessous. Chaque fonction sera décrite, suivie du code fourni entre parenthèses pour l'implémenter. Lorsque vous écrirez ce code, faites-le dans un seul bloc, substituez vos propres noms à ceux donnés en exemple et n'incluez pas les crochets encadrant la formule complète.
- Ouvrez la feuille de calcul en mode lecture seule. [DIM XLAppSet XLApp=CreerObjet("Excel.App")xlapp.visible=falsexlapp.workbooks.open\\excelloc\ filename.xls,3,].
- Actualisez les données et enregistrez le rapport, dans cet exemple en format PDF avec un tampon dateur. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, \\pdfloc\nomrapport_ & DatePart("aaaa,Date()) & "-" & Right("0" & DatePart("mois",Date()),2) & "-" Right("0" & DatePart("jour",Date()),2) & ".pdf"]. Si votre document de sortie doit être dans un format différent, remplacez « .pdf » par l'extension correcte pour ce format.
- Fermez la feuille de calcul sans la sauvegarder, puis fermez Excel. [XlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Utilisez « .xlsx » au lieu de « .xls » dans le suffixe de la feuille de calcul si elle a été enregistrée avec Excel 2007, puis en format XML.
3
Écrivez un script de commande pour lancer le script Visual Basic. Ceci est nécessaire pour que le script Visual Basic soit exécuté automatiquement. Sans le script de commande, le script VB devra être exécuté manuellement.
- Votre script sera exprimé dans le format suivant (substituez votre propre dossier et votre nom de fichier à ceux présentés ici, et n'incluez pas les crochets : [cscript/nologo\\emplacementfichier\script.vbs]
4
Écrivez un script de commande pour vérifier le fichier de sortie tel qu'il a été créé. Votre script doit effectuer les fonctions décrites ci-dessous. Chaque fonction sera suivie d'un code donné entre parenthèses pour l'implémenter. Lorsque vous écrirez ce code, faites-le dans un seul bloc, substituez vos propres noms à ceux donnés en exemple et n'incluez pas les crochets ou parenthèses.
- Vérifiez que le fichier de sortie existe. [Pour /f "tokens=2-4 delims=/ " %%a in ('date /t') rrapport=nomrapport_%%c-%%a-%%b.pdf)]. Si le format du fichier de sortie n'est pas un fichier PDF, remplacez-le par le format correct.
- Si le fichier / rapport de sortie existe, envoyez-le par courrier électronique aux personnes qui en ont besoin. [Si existant \\pdfloc\%rrapport% ( envoimail -expéditeur@domaineexpéditeur.com - à destinataire@domainedestinataire.com -u Rapport Programmé -m Rapport %%rapport% est attaché. -a \pdfloc\%rrapport% -s votreserveur:port -xu nomutilisateur -xp motdepasse)].
- Si le fichier/rapport de sortie n'existe pas à l'emplacement spécifié, demandez à la procédure de vous envoyer un message indiquant l'échec de la livraison. [Ou (mailenvoyé -f expéditeur@domaineexpéridteur.com -t expéditeur@domaineexpéditeur.com -u Le rapport n'a pas été exécuté -m file% rrapport% n'existe pas dans \\pdfloc\ -s votre serveur: port -xu nomutilisateur -xp motdepasse)].
5
Vérifiez que le dossier « Bureau » existe sur votre ordinateur. Vous devez vérifier l'existence du dossier Bureau pour un système 32 bits et un système 64 bits. Si vous ne le faites pas, Excel et votre feuille de calcul devront être ouverts manuellement.
- L’emplacement pour un système 32 bits est le suivant : c:\windows\system32\config\profilsystem
- L’emplacement pour un système 64 bits est le suivant : c:\windows\syswow64\ config\profilsystem
6
Enregistrez une tâche pour exécuter les scripts si nécessaire. Les scripts de commande doivent être exécutés de manière séquentielle sur une base continue, que quelqu'un utilise ou non l'ordinateur. Le privilège doit être réglé sur le réglage le plus élevé possible.

Publicité

Conseils

Les tâches planifiées sont mieux exécutées à partir d'un serveur, généralement à partir d'un compte système avec des privilèges d'administrateur. Le seul inconvénient de l'exécution de tâches à partir d'un compte système est que vous n'aurez pas accès à une interface utilisateur. Cependant, les tâches automatisées sont généralement destinées à s'exécuter en arrière-plan, sans que l'utilisateur ne les remarque.