Writing a macro on Python for LibreOffice Calc

Description

LibreOffice Calc allows users to create their own macros using the built-in programming language StarBasic. This language is very similar to VBA , however, has limited functionality because it is rarely used.

Modern programming languages, such as Python, provide much more possibilities and have a wider range of additional libraries for all occasions.

So, let's learn how you can create your own macros using such a popular programming language as Python.

StarBASIC Code for HelloWorld

First, let's remember how you can write your own program using the standard LibreOffice Calc tools for writing macros.

Below is the code for the macro HelloWorld , which prescribes the phrase "Hello World!" in the cell "B7" using the built-in programming language StarBasic.

To add a new macro, open the menu Tools - Macros - Edit Macros... , select Module1 and copy the following text into the module:

Sub HelloWorld
    GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
    
    'REM "doc" it's a current LO Calc workbook (active worksheet)
    doc = CreateScriptService("Calc")
    doc.SetValue("B7", "Hello World!")
End Sub

Then, close Macro Editor, return to LibreOffice Calc and run our new macro via the menu Tools - Macros - Run Macro...

Python Folder

Before writing a macro on Python, we need to first create a *.py file (myPyScripts.py) in the following folder:

%APPDATA%\LibreOffice\4\user\scripts\python

If folders scripts and python are missing, you need to create them manually.

Python Code for HelloPython

Below is the Python script for the macro HelloPython , which prescribes the phrase "Hello Python!" in the cell "B8" in the LibreOffice Calc program.

To add a new macro, open the file myPyScripts.py by means of Notepad++ (or Windows Notepad) and copy the following text there:

from scriptforge import CreateScriptService

def HelloPython():
    doc = CreateScriptService("Calc")
    doc.SetValue("B8", "Hello Python!")
    return None

Then, close Notepad++ and return to LibreOffice Calc. To confuse this script, open the menu Tools - Macros - Organise Macros - Python... , select "My Macros" , open the folder "myPyScripts" and choosing HelloPython press the button Run .

Using the extension

You can also use a ready-made set functions and utility written on Python by installing the extension YLC Utilities .

After that, this functionality will be available in all files that are opened in LibreOffice Calc.