Ceci est une ancienne révision du document !
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.
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.
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
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).
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.
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)
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.
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.
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.
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.