Back in parts 8-12 (FCM issues 53, 55-58), I took you from a blank spreadsheet to a full working budget spreadsheet. Something similar is what I use twice a month to do my home budget. I keep a whole year of budget spreadsheets in one document, which means twice a month I copy the sheet, rename it, and make sure I get all the settings correct to put a copy of the current sheet at the end of the sheet tabs. Believe me, mistakes have been made. Curses have been breathed. Innocent computers have been threatened. In time, I decided, since I was such a fallible, flawed human being, I needed to reduce the number of steps necessary to complete this task in order to lessen my chances for mistakes. The result was a macro where all I have to do is give the sheet a new name. The macro handles all the rest, making sure it is copied and placed at the end of the tab list. The task fits perfectly into the idea behind macros, a repeatable process that automation can speed up, or prevent mistakes. Today, I share it with you.
Retour en arrière sur les parties 8 à 12 (FCM numéros, 53, 55-58) : je vous ai amené d'une feuille de tableau blanc à un tableau de budget pleinement opérationnel. J'utilise quelque chose de similaire deux fois par mois pour mon budget familial. Je garde une année complète de feuilles de budget dans un document, ce qui signifie que deux fois par mois je copie la feuille, je la renomme et je m'assure que tous les réglages sont bons pour mettre une copie de la feuille courante à la fin des onglets. Croyez-moi, des erreurs ont été commises. Des jurons ont été crachés. Des ordinateurs innocents ont été menacés.
En temps voulu et puisque je suis un être humain faillible et imparfait, j'ai décidé que je devais réduire le nombre d'étapes nécessaires à la réalisation de cette tâche, de façon à réduire les risques d'erreur. Le résultat fut une macro où tout ce que j'ai à faire est de donner un nouveau nom à la feuille. La macro gère tout le reste, s'assurant de la copie et du positionnement à la fin de la liste des onglets. La tâche colle parfaitement à l'idée des macro : un processus répétitif que l'automatisation peut accélérer tout en évitant les erreurs. Aujourd'hui, je vous les présente.
The Manual Method In order to appreciate a macro, you really have to understand what it's doing for you, and the mistakes you can make. Therefore, I will start with the description of the manual process that the macro makes simpler. There is actually more than one way to copy a sheet in Calc, but I will show you the dialog method with all the options. Right-click on the tab of the sheet you want to copy and select Move/Copy Sheet. The Move/Copy dialog appears. You then select Copy. One of the mistakes I have made is to forget this. I end up just renaming my sheet. Under “Insert Before” select the option at the end “-move to end position-”. If I forget this one, the sheet is placed before the current sheet rather than at the end where I want it. Finally, I have to rename the sheet. There have been times while trying to remember to get the other settings right, that I have forgotten to actually change the name. When I do, the copied sheet gets the name of the current sheet with a “_2” appended to the end. Click OK to execute the settings. I know, you're thinking that it's not that bad, but after a few times getting it wrong, your inner Hulk comes out and you want to smash things. Okay, maybe that's just me. Trying to balance the home budget is frustrating enough without the bonus of mistakes while copying the spreadsheet.
La méthode manuelle
Pour pouvoir apprécier une macro, vous devez bien comprendre ce qu'elle va faire pour vous et quelles erreurs sont possibles. Par conséquent, je commencerai par la description du déroulement manuel que la macro simplifie. Il y a réellement plus d'une façon de copier une feuille dans Calc, mais je vous montrerai la méthode interactive avec toutes les options.
Faites un clic droit sur l'onglet de la feuille que vous voulez copier et sélectionnez Déplacer/Copier la feuille… La boîte de dialogue Déplacer/Copier la feuille apparaît. Ensuite, sélectionnez Copier. Une des erreurs que j'ai commises a justement été d'oublier ceci. À la fin, j'avais seulement renommé ma feuille. Dans « Insérer avant », choisissez la dernière option « placer en dernière position ». Si j'oublie celui-ci, la feuille est insérée avant la feuille active au lieu d'être placée en dernière position, comme je le souhaite. Enfin, je dois renommer la feuille. Il y a eu des fois où je faisais tellement attention de bien faire les autres réglages que j'oubliais de changer le nom. Dans ce cas, le nom de la feuille copiée prend un « _2 » à la fin. Cliquez sur OK pour appliquer les réglages.
Je sais ; vous allez dire que ce n'est pas si dur, mais après vous être trompé plusieurs fois, votre nature brute ressort et vous voulez tout balancer. D'accord, ce n'est peut-être que moi. La mise en équilibre du budget familial est suffisamment pénible sans ajouter en prime des erreurs lors de la copie des tableaux.
The CopySheet Macro The macro is not very long (next page, top right) and you can easily type it in. You can also copy it from http://pastebin.com/s3iTGjN6. The micro starts by declaring 3 variables used in the body of the macro. “Sheet1” and “Sheet2” are strings that will contain the names of the current sheet (Sheet1) and the new sheet (Sheet2). The “Doc” variable will hold a reference to the current document. It is declared as a type Object because the reference is to a LibreOffice API object. Doc = ThisComponent ThisComponent is the current active document in LibreOffice. In this case, the macro is looking for a Calc spreadsheet. If NOT Doc.supportsService(“com.sun.star.sheet.SpreadsheetDocument”) then MsgBox “This Macro Only Works with Calc Spreadsheets” Exit Sub End If
La macro CopySheet
La macro n'est pas très longue (page suivante, en haut à droite) et vous pouvez aisément la saisir. Vous pouvez aussi la copier sur : http://pastebin.com/s3iTGjN6.
La macro commence par la déclaration de 3 variables utilisées dans le corps de la macro. « Sheet1 » et « Sheet2 » sont des chaînes de caractères qui contiendront les noms de la feuille courante (Sheet1) et de la nouvelle feuille (Sheet2). La variable « Doc » contiendra une référence au document en cours. Elle est déclarée comme un type Object parce que la référence est celle d'un objet API LibreOffice.
Doc = ThisComponent
ThisComponent est le document courant actif dans LibreOffice. Dans ce cas, la macro cherchera un tableur Calc.
If NOT Doc.supportsService(“com.sun.star.sheet.SpreadsheetDocument”) then
MsgBox "This Macro Only Works with Calc Spreadsheets" Exit Sub
End If
The “If” statements check to make sure that the current document is a Calc spreadsheet. It checks to see whether the document supports the SpreadsheetDocument service, identifying it as a Calc spreadsheet and not some other document type. If it is not a spreadsheet, the macro pops up a message box telling the user the macro works only with spreadsheets. The macro then executes an “Exit Sub” that exits the macro without running any more of the code. Sheet1 = Doc.CurrentController.ActiveSheet.Name The macro uses the Doc object to extract the name of the current sheet. The breadcrumb dot notation goes through a progression of getting more specific. CurrentController is a reference to the service that controls the document. ActiveSheet is a reference to the currently active sheet in the document. Finally, Name gets the name of the current sheet and this is assigned to the variable Sheet1. Sheet2 = InputBox(“Enter Name for Copied Sheet:”, “Copy Sheet”, Sheet1) To get the name of the new sheet, the macro uses an InputBox function. The InputBox takes 3 arguments: • The prompt to show the user (“Enter Name for Copied Sheet:”). • The title of the InputBox window (“Copy Sheet”). • The default text (it just uses the name of the current sheet [Sheet1] as the default text).
L'argument « If » s'assure que le document courant est bien un tableur Calc. Il vérifie si le document supporte le service SpeadsheetDocument, l'identifiant comme un tableur Calc et non comme un autre type de document. Si ce n'est pas un tableur, la macro ouvre une fenêtre de message pour prévenir l'utilisateur que la macro ne fonctionne que pour un tableur. Ensuite, la macro exécute un « Exit Sub » qui termine la macro sans exécuter d'autre code.
Sheet1 = Doc.CurrentController.ActiveSheet.Name
La macro utilise l'objet Doc pour extraire le nom de la feuille courante. La notation avec une ribambelle de points marque une progression vers plus de détail. CurrentController fait référence au service qui contrôle le document. ActiveSheet se réfère à la feuille active du document. Enfin, Name récupère le nom de la feuille actuelle et celui-ci est affecté à la variable Sheet1.
Sheet2 = InputBox(“Enter Name for Copied Sheet:”, “Copy Sheet”, Sheet1)
Pour récupérer le nom de la nouvelle feuille, la macro utilise la fonction InputBox. InputBox contient 3 arguments : • Une invite destinée à l'utilisateur (« Enter Name for Copied Sheet: »)[Entrez le nom de la feuille copiée : ] • Le titre de la fenêtre InputBox (« CopySheet »). • Le texte par défaut (le nom de la feuille courante [Sheet1] est le texte par défaut).
If the user clicks the OK button, the InputBox will return the string entered in the text box or the default text when no changes are made. If the user clicks the Cancel button, a blank string is returned. If Sheet2 = “” Then Exit Sub Do While Doc.Sheets.hasByName(Sheet2) Sheet2 = InputBox(Sheet2 + _ “ already exists, select a different name:”, “Copy Sheet”, Sheet2 + “2”) If Sheet2 = “” Then Exit Sub Loop Now, the macro must use some logic to determine how to proceed. The “If” checks to see if the returned string is blank. If so, that means that the Cancel button was pressed, therefore the “Exit Sub” is executed. “Exit Sub” exits the macro without running any more of the remaining code.
Si l'utilisateur clique sur OK, la fonction InputBox retourne la chaîne de caractères entrée dans la zone de texte ou le texte par défaut s'il n'a pas été modifié. Si l'utilisateur clique sur Annuler, une chaîne vide est retournée.
If Sheet2 = “” Then Exit Sub
Do While Doc.Sheets.hasByName(Sheet2)
Sheet2 = InputBox(Sheet2 + _ " already exists, select a different name:", "Copy Sheet", Sheet2 + "2") If Sheet2 = "" Then Exit Sub
Loop
Maintenant, la macro doit utiliser un raisonnement logique pour déterminer comment procéder. Le « If » teste si la chaîne de caractères est vide. Si c'est le cas, ça signifie que le bouton Annuler a été cliqué, donc la commande Exit Sub est exécutée. Exit Sub termine la macro sans exécution de code supplémentaire.
The Do While…Loop checks to see if another sheet in the Calc document has the same name. The hasByName method checks the supplied name against the names of all the sheets in the collection. If a match is detected, the macro uses an InputBox to prompt the user for a new, unmatched name. The Do While…Loop will loop until the name in Sheet2 does not match the name of any other sheet. The “If” inside the loop exits the macro if Cancel is pressed. If the name is unmatched from the start, the loop never runs. This prevents two sheets from having the same name. NOTE: The underscore (_) in the InputBox statement is used to break a long line up into shorter lines. LibreOffice Basic requires that the underscore is the last thing on the line. Nothing, not even a space, can follow it. When lines are joined in this way, LibreOffice Basic sees them as one line. Doc.Sheets.CopyByName(Sheet1, Sheet2, Doc.Sheets.Count)
La boucle Do While… vérifie si une autre feuille du document Calc a le même nom. La méthode hasByName compare le nom aux noms des autres feuilles du classeur. Si une correspondance est détectée, la macro utilise une fonction InputBox pour demander un nouveau nom différent à l'utilisateur. La boucle Do While… tournera jusqu'à ce que le nom dans Sheet2 soit différent des autres noms de feuilles. Le test « If » dans la boucle provoque une sortie de la macro si vous cliquez sur Annuler. Si, dès le départ, le nom est sans correspondance, la boucle ne se lance pas. Ceci évite que deux feuilles puissent avoir le même nom.
NOTE : Le trait de soulignement (_) dans la déclaration InputBox est utilisé pour couper une longue ligne de texte en morceaux plus courts. Le Basic de LibreOffice exige que le trait de soulignement soit le dernier caractère de la ligne. Rien, pas même une espace, ne peut le suivre. Quand des lignes sont reliées par ce biais, LibreOffice les considère comme une seule ligne.
Doc.Sheets.CopyByName(Sheet1, Sheet2, Doc.Sheets.Count)
The last line of the macro brings all this preparatory work together to finally execute the copy. Sheets is a reference to the collection of sheets in the document. CopyByName is the method that actually copies the sheet and moves it to the end of the sheet tabs. The CopyByName method has 3 parameters: • The sheet to copy from (Sheet1). • The sheet to copy to (Sheet2). • The position of the new sheet (Doc.Sheets.Count). Count is the total number of sheets in the collection (Sheets). Since the sheet numbers are referenced starting with 0, the use of Count here puts the new sheet at the end. Creating the Calc Macro Module When I developed and wrote the CopySheet macro, I created a macro module for Calc. Here I could store this macro as well as any future macros designed for the Calc program. It's a good idea to group like macros together.
La dernière ligne de la macro regroupe tout le travail préparatoire pour enfin exécuter la copie. Sheets est un appel à l'ensemble des feuilles du classeur. CopyByName est la méthode qui exécute la copie de feuille et qui la place à la fin des onglets. La fonction CopyByName a 3 paramètres : • Le nom de la feuille à copier (Sheet1) ; • Le nom de la feuille copiée (Sheet2) ; • La position de la nouvelle feuille (Doc.Sheets.Count).
Count est le nombre total de feuilles du classeur (Sheets). Comme les feuilles sont comptées à partir de 0, l'utilisation faite ici de Count met la nouvelle feuille à la fin.
Créer le module de macro Calc
Quand j'ai développé et écrit la macro CopySheet, j'ai créé un module de macro dans Calc. Ici je pouvais sauvegarder la macro ainsi que toute autre macro que je développerai pour le programme Calc. C'est une bonne pratique de regrouper les macros ainsi.
To create the module, Tools > Macros > Organize Macros > LibreOffice Basic. The LibreOffice Basic Macros dialog opens. Click Organizer to get the LibreOffice Basic Macro Organizer dialog. Under My Macros > Standard, there is a default module named Module 1. Select it and click Delete. With Standard selected, click New. Name the new Module “Calc” and click OK. Click Close. Now back in the LibreOffice Basic Macros dialog, select the new “Calc” module you just created and click Edit, opening the LibreOffice Macro Editor. Delete the automatically created “Sub Main” and “End Sub”. Type in or copy and paste the SheetCopy macro into the editor. Save the module and close the editor.
Pour créer le module Outils > Macros > Gérer les macros > LibreOffice Basic… La boîte de dialogue Macros LibreOffice Basic s'ouvre. Cliquez sur Gérer… pour ouvrir la boîte de dialogue Gestion des macros de LibreOffice Basic. Sous Mes macros > Standard, il y a un module par défaut appelé Module 1. Sélectionnez-le et supprimez-le. Sélectionnez Standard et cliquez sur Nouveau… Appelez « Calc » ce nouveau module et cliquez sur OK. Puis cliquez sur Fermer.
De retour dans la boîte de dialogue LibreOffice Basic, sélectionnez le module « Calc » que vous venez de créer et cliquez sur Éditer, ce qui ouvre l'éditeur de LibreOffice Basic. Effacez les lignes « Sub Main » et « End Sub » créées automatiquement. Saisissez ou copiez/collez la macro CopySheet dans l'éditeur. Sauvegardez le module et fermez l'éditeur.
Test The Macro After typing in the macro and saving it, you will want to test it to make sure you typed everything correctly. First, open a Calc document or create a new one. You can then test the macro by going to Tools > Macros > Run. Under Library, select My Macros > Standard > Calc. Under “Macro Name” select CopySheet and click Run. Enter a name for the sheet like “New Sheet.” Click OK. If all goes well, a new sheet is created with the name you gave it. You will want to repeat the test and not change the name to see if the macro prompts you to change the name. Also, test to make sure the macro stops when you click on Cancel rather than OK. For the final test, open a text document and run the macro. You should get the message telling you that the macro works only on spreadsheets.
Tester la macro
Après avoir saisi et sauvé la macro, vous voulez la tester pour être sûr que toute la saisie est correcte. D'abord, ouvrez le document Calc ou créez-en un nouveau. Vous pouvez maintenant faire le test en allant à Outils > Macros > Exécuter la macro… Dans Bibliothèque, sélectionnez Mes macros > Standard > Calc. Sous Nom de la macro, sélectionnez CopySheet et cliquez sur Exécuter. Entrez un nouveau nom pour la feuille comme « New Sheet ». Cliquez sur OK. Si tout va bien, une nouvelle feuille est créée avec le nom que vous avez saisi. Vous devez répéter le test sans changer le nom pour voir si la macro vous demande un nom différent. Faites également un test pour vérifier que la macro s'arrête quand vous cliquez sur Annuler plutôt que sur OK. Pour le dernier essai, ouvrez un document texte et lancez la macro. Vous devriez avoir un message vous disant que la macro ne fonctionne que pour les tableurs.
NOTE: Back in Full Circle issue 64 (LibreOffice Part 17: Macros), I showed you how to create a menu shortcut to a macro. This is a good candidate for such a shortcut. Create the menu and shortcut in Calc. Macros like CopySheet can speed up your processes and prevent you from making mistakes, which is the whole idea behind macros. This is just an example of something you can do with macros, but you can write your own macros that can expand the use or capabilities of any of the LibreOffice programs, or just reduce a task you do often. A Google search for “LibreOffice Basic” is a good place to start learning more.
NOTE : Dans l'article du n° 64 du FCM (LibreOffice Partie 17 : Macros), je vous avais montré comment créer un raccourci du menu vers une macro. Vous avez ici un bon candidat pour un tel raccourci. Créez le menu et le raccourci dans Calc.
Les macros comme CopySheet peuvent accélérer vos processus et vous éviter de faire des erreurs, ce qui est toute l'idée derrière les macros. Ce n'est qu'un exemple de ce que vous pouvez faire avec les macros, mais vous pouvez écrire vos propres macros qui vont étendre l'utilisation ou les capacités de n'importe quel programme de LibreOffice, ou tout simplement raccourcir une tâche que vous faites souvent. Une recherche dans Google sur « LibreOffice Basic » est un bon endroit pour commencer à en apprendre plus.