Microsoft Excel possède diverses fonctionnalités et l'une d'entre elles génère automatiquement des rapports. Vous pouvez créer des feuilles de calcul interactives pour simplifier le processus de saisie de données pour les autres dans le classeur, tout en automatisant la génération de rapports. Ces deux fonctionnalités nécessitent une connaissance suffisante de Visual Basic. Les étapes pour effectuer les deux tâches sont décrites ci-dessous.
Étape
Méthode 1 sur 2: Création d'une feuille de calcul interactive
Étape 1. Déterminez la mise en page de la feuille de calcul
La mise en page de la feuille de calcul doit être faite pour que d'autres puissent trouver les champs nécessaires à la saisie des données.
Les mises en page de feuilles de calcul peuvent être disposées horizontalement ou verticalement. La plupart des utilisateurs trouvent plus facile de travailler avec une mise en page verticale, surtout si la feuille de calcul est imprimée
Étape 2. Créez des étiquettes de texte pour la feuille de calcul
Écrivez une étiquette en haut de chaque colonne et à gauche de chaque cellule de la colonne que vous prévoyez d'utiliser comme entrée de données.
Étape 3. Appuyez simultanément sur les touches alt=""Image" et F11.</h4" />
Cette combinaison de touches ouvrira l'éditeur Microsoft Visual Basic.
Étape 4. Double-cliquez sur "Ce classeur" dans le volet "Projet-VBA Project" en haut à gauche
Une fenêtre d'écriture de code apparaîtra dans la section principale de l'éditeur.
Étape 5. Sélectionnez "Procédure" dans le menu Insertion
La boîte de dialogue Ajouter une procédure s'affiche.
Étape 6. Écrivez le nom de la procédure dans le domaine de nom
Saisissez un nom significatif pour la procédure, tel que « SumExpenses » si la feuille de calcul sera utilisée pour déclarer les frais de déplacement. Cliquez sur OK pour fermer la boîte de dialogue.
- Les noms de procédure ne peuvent pas contenir d'espaces, mais peuvent utiliser un trait de soulignement (_) pour remplacer les espaces.
- Après la fermeture de la boîte de dialogue Ajouter une procédure, une ligne apparaîtra intitulée "Sous-public" suivie du nom de la procédure. Au-dessous de cette ligne se trouvent un espace et les mots "End Sub".
Étape 7. Saisissez le code pour chaque champ de saisie de la feuille de calcul
Vous écrirez deux lignes de code pour chaque entrée.
- La première ligne de code se présente sous la forme de "Plage ("cellname"). Select", "cellname" représente la cellule dans laquelle l'entrée est saisie. Remplissez le nom de la cellule qui se trouve immédiatement à droite de l'étiquette de texte. Si l'étiquette de texte se trouve dans la cellule A2, le champ de saisie est la cellule B2 (Plage("B2"). Sélectionner). Les guillemets avant et après le nom de la cellule sont toujours écrits à ce stade, mais ne sont plus inclus dans l'instruction de code complète.
- Le code de la deuxième ligne est "ActiveCell. Value = InputBox("InputPrompt")". "InputPrompt" représente le texte qui apparaîtra pour informer l'utilisateur du type de données à saisir dans la cellule d'entrée. Par exemple, si la cellule d'entrée est remplie de dépenses alimentaires, remplacez « InputPrompt » par « Entrez les dépenses totales pour les repas, y compris les pourboires ». (Les guillemets pour le texte de l'instruction d'entrée sont toujours inclus, tandis que les guillemets avant et après la commande n'ont pas besoin d'être écrits.)
Étape 8. Saisissez le code pour chaque champ de calcul
Encore une fois, les deux lignes utilisées sont les mêmes que précédemment, mais cette fois ActiveCell. Value est un calcul d'une fonction numérique, par exemple SUM, où la fonction InputBox est utilisée pour afficher les instructions d'entrée.
Étape 9. Ajoutez une ligne de code pour enregistrer votre feuille de calcul interactive
Le format est "ActiveWorkbook. SaveAs Filename:="Filename.xls". "Filename" est le nom de la feuille de calcul interactive. (Les guillemets avant et après "Filename.xls" restent écrits, tandis que les guillemets pour toutes les commandes sont non requis.)
Si vous utilisez Microsoft Excel version 2007 ou ultérieure, utilisez ".xlsx" au lieu de ".xls". Cependant, s'il existe des utilisateurs de feuilles de calcul interactives avec la version Excel 2003 et inférieure, ils ne pourront pas utiliser la feuille de calcul sans un plug-in de lecteur
Étape 10. Appuyez simultanément sur les touches alt=""Image" et Q.</h4" />
L'éditeur Visual Basic se fermera.
Étape 11. Appuyez simultanément sur les touches alt=""Image" et F8.</h4" />
La boîte de dialogue Macro apparaît.
Étape 12. Cliquez sur le nom de la procédure dans la liste Macros
Si la procédure créée est la seule de la liste, elle sera automatiquement sélectionnée.
Étape 13. Cliquez sur le bouton Options
Il vous sera demandé d'entrer un caractère de clavier à utiliser comme raccourci avec la touche Ctrl. Choisissez une lettre significative qui n'a pas été utilisée comme caractère de raccourci, comme "e" pour "entrée".
Étape 14. Cliquez sur "OK" pour fermer la boîte de dialogue Options de macro
Vous pouvez désormais distribuer des feuilles de calcul interactives aux utilisateurs. Après l'avoir ouvert, l'utilisateur peut utiliser les touches de raccourci pour effectuer des entrées et suivre les instructions que vous avez créées pour remplir les données.
Méthode 2 sur 2: Automatiser la génération de rapports
Étape 1. Créez un rapport dans un tableau croisé dynamique
Les tableaux croisés dynamiques sont conçus pour résumer les données afin que vous puissiez comparer les chiffres et identifier les tendances. Le tableau croisé dynamique doit se rapporter aux données qui se trouvent dans l'un des processeurs de données ou importées d'une base de données spécifique.
Étape 2. Écrivez un script Visual Basic pour ouvrir et fermer le rapport
Le script doit être capable d'exécuter les différentes fonctions énumérées ci-dessous. Chaque fonction sera décrite suivie du code donné entre parenthèses pour l'implémenter. Lorsque vous écrivez du code réel, écrivez-le dans un seul bloc, en remplaçant le nom dans l'exemple par le vôtre, et n'incluez pas de parenthèses au début et à la fin de l'exemple de code.
- Ouvrez la feuille de calcul en mode lecture seule. [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
- Rechargez les données et enregistrez le rapport, dans cet exemple, il est enregistré au format PDF avec la légende de la date. [Truexlapp.activeworkbook. RefreshAllxlapp.activeworkbook. ExportAsFixedFormat xlTypePDF, \pdfloc\reportname_ & DatePart("aaaa, Now()) & "-" & Right("0" & DatePart("m", Now())), 2) & "-" Right("0" & DatePart("d", Now()), 2) & ".pdf"] Si le format du document de sortie est différent, remplacez le format ".pdf" par l'extension appropriée comme vous le souhaitez.
- Fermez la feuille de calcul sans l'enregistrer, puis fermez Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Utilisez ".xlsx" au lieu de ".xls" à la fin de la feuille de calcul si la feuille de calcul est enregistrée dans Excel 2007 et un format XML ultérieur.
Étape 3. Écrivez un script batch pour démarrer le script Visual Basic
Le but de l'écriture du script est de permettre aux scripts Visual Basic de s'exécuter automatiquement. Sans script batch, les scripts VB doivent être exécutés manuellement.
Le script est écrit dans le format suivant, en remplaçant par votre propre nom de dossier et nom de fichier pour le nom donné dans cet exemple: [cscript /nologo \fileloc\script.vbs]
Étape 4. Écrivez un script batch pour vous assurer que le fichier de sortie existe tel qu'il a été créé
Votre script doit exécuter les fonctions ci-dessous. Chaque fonction sera suivie du code donné entre parenthèses pour l'implémenter. Lorsque vous écrivez du code réel, écrivez-le dans un seul bloc, en remplaçant le nom dans l'exemple par le nom dont vous avez besoin, et n'incluez pas de parenthèses au début et à la fin de l'exemple de code.
- Vérifiez s'il existe un fichier de sortie. [Pour /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] Si le format du fichier de sortie n'est pas PDF, remplacez ".pdf" par l'extension appropriée.
- Si le fichier/rapport de sortie existe, envoyez-le par e-mail à la personne qui en a besoin. [S'il existe \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Rapport planifié -m Le rapport %%report% est joint. -a \pdfloc\%rreport% -s votreserveur:port -xu nom d'utilisateur -xp mot de passe)]
- Si le fichier/rapport de sortie n'existe pas à l'emplacement spécifié, créez une procédure pour qu'il puisse vous envoyer un message indiquant que la livraison a échoué. [Sinon (sendmail -f [email protected] -t [email protected] -u Le rapport n'a pas été exécuté -m le fichier %rreport% n'existe pas dans \pdfloc\ -s votreserveur:port -xu nom d'utilisateur -xp mot de passe)]
Étape 5. Assurez-vous que le dossier "Bureau" se trouve sur l'ordinateur
Vous devez vérifier l'existence du dossier Desktop pour les systèmes 32 bits et 64 bits. Sinon, Excel et la feuille de calcul doivent être ouverts manuellement.
- Emplacement du système 32 bits: c:\windows\system32\config\systemprofile
- Emplacement du système 64 bits: c:\windows\syswow64\config\systemprofile
Étape 6. Planifiez des tâches pour exécuter des scripts selon vos besoins
Les scripts batch doivent être exécutés de manière séquentielle et continue, même si personne n'utilise l'ordinateur. Les privilèges doivent être définis sur le paramètre le plus élevé possible.
