Macros are a great way to automate tasks in Spreadsheet applications, be it the good old Microsoft Excel or the equally efficient FOSS alternative, LibreOffice Calc. The best thing about macros is that they are written in a very easy language called Basic.
© The LibreOffice Project
As it’s very name suggests, Basic is a lenient programming language actually designed with ease of use in mind. For instance, upper/lower case doesn’t matter for variable names or keywords (
function/Function are equivalents), function braces are optional like Ruby and type-conversion happens automatically. This makes Basic equally useful for both power users and programmers. A LibreOffice Basic macro is just a function or sub procedure which does a specific useful task. In this tutorial, we will see ten such useful macros that can help you with various automation tasks.
- Recipe 0: How to create a LibreOffice macro
- Recipe 1: Read cell contents
- Recipe 2: Change cell contents
- Recipe 3: Search and Replace text
- Recipe 4: Regular Expressions
- Recipe 5: Show File-open dialog
- Recipe 6: Show File-save dialog
- Recipe 7: File I/O: Read from files
- Recipe 8: File I/O: Write to files
- Recipe 9: Load data from a CSV file
- Recipe 10: Copy text to clipboard
Whilst macros can be created in Writer and Draw too, in this specific tutorial, we will restrict ourselves to spreadsheets (Calc). To create a macro, just open the spreadsheet in LibreOffice and go to
Tools->Macros->Organize Macros->LibreOffice Basic menu. After that, if you want to create a macro specific to your spreadsheet (as usually is the case), expand your spreadsheet file on left and select
Standard and click
New. This will open the LibreOffice Macro Editor as a separate window.
One of the most basic things needed for automation is reading a cell’s contents. The following piece of code does exactly this:
dim is a keyword used to declare a variable but declaration is totally optional unless
Option Explicit is specified at the beginning of the module.
ThisComponent is the LibreOffice object that references the current spreadsheet (or a written document in case of Writer). The important thing here is the expression,
sheet.getCellByPosition(0, 0).String which gets the contents of first cell in the first row. Cells can be referenced using the co-ordinate system where (0,0) refers to cell at row-0 and column-0. Thus, any value across the entire spreadsheet can be fetched using this simple method.
To run a macro from the editor, just place the cursor inside the
function body of any macro and press
F5 (or alternatively, click the
Run BASIC icon on the toolbar).
Another often needed thing is the ability to change the cell contents. The following code sets the first cell in the first row to “Hola! Mundo”, the Spanish expression for “Hello! World”:
Searching and replacing specific strings could be an important part of your automation routine. Below is a fun macro that searches for the first names of some Linux experts (like Linus, Richard, Peter, etc.) and replaces it with their last names (Torvalds, Stallman, Anvin):
surnames() are actually arrays. Unlike C and Java, arrays in Basic are declared and accessed using round braces and not square ones. Also, what gets declared in an array declaration is the upper-bound, not the total size. Thus,
foo(2) is actually a size-3 array ranging from indices 0 to 2.
Regular expressions are very useful in searching and replacing text based on specific patterns. The following macro searches for all the email addresses in your spreadsheet and replaces each one with
Showing the File-open dialog is a very common requirement, especially when you want to open an external file for processing. The below code uses the
FilePicker object to show the file-open dialog and return the selected file-name:
createUnoService is a LibreOffice specific method for creating helper objects like
FilePicker in this example. The
appendFilter method is used to filter out only
CSV files in the dialog.
For showing a File-save dialog, the same
FilePicker object is used, initializing it with the
Raw file I/O is a feature provided by almost every language and Basic macros make it almost too easy. Below code is used to read a CSV file with three columns. Name of the file is set in the
filename variable. The variable
num is a numerical tag used to reference the file-handler and
FreeFile() returns a free available number that can be used for tagging. The
open statement is self-explanatory. In Basic, files can be opened in Input, Output and Binary modes. Finally, the
input statement is used to actually read the file into the variables line after line.
For writing to files, a handler is opened in
output mode instead of
input, and the
write statement is used to actually write the variables to a file.
Apart from working in raw I/O mode, it is sometimes required to load a complete CSV as a sheet in the current document. Using the
show_open_dialog function that we studied earlier, the following macro first prompts a user with a File-open dialog and then loads the specified CSV file as a new sheet:
fileProps(0) is a property variable used for specifying the CSV file format, while
fileProps(1) specifies the default formatting options for the CSV (such as a delimiter, charset, etc.)
Your custom processing might involve putting a specific text to the clipboard from LibreOffice Calc. Following code shows how to put the string “Hola!” to the system clipboard:
Second function is a callback and is used for storing the string to clipboard. The last two are helper functions used by the
XTransferable helper objects and are required.
Finally, the working LibreOffice Calc spreadsheet implementing all these examples can be found here.