Ask Your Question

LibreOffice Calc 6 - "batch" hyperlink to files in folder?

asked 2019-05-05 19:14:30 +0100

LOcalcQ gravatar image


Question regarding LibreOffice Calc (Version:

I have one folder with the spreadsheet and a bunch of image and pdf files.

In the sheet every line starts with a hyperlink, which I usually put in using Ctrl+K, then target the file, and then rename it to not show the full link to file but just the file name.

Since I have 36 file to put in, I've been wondering if there is a faster way to do this, instead of manually putting them in one by one?

(I'm on Fedora 29 if that makes any difference).


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-05-05 23:51:17 +0100

karolus gravatar image

Called from a Calcfile stored in target_folder the following code puts HYPERLINK(…)-formulas into Column A of active Sheet.

from os import path
from pathlib import Path
import uno

def link_my_folder(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentSelection.Spreadsheet

    p = Path(path.dirname(uno.fileUrlToSystemPath(doc.URL)))
    filenames = [(, x.as_uri()) for x in p.iterdir()
                if x.is_file() and not x.as_uri()==doc.URL]
    formulas = tuple(('=HYPERLINK("{}";"{}"'.format(b, a),) for a, b in filenames)

    targetrange = sheet.getCellRangeByPosition(0,0,0,len(formulas)-1)
edit flag offensive delete link more


Thank you for the answer! I don't know how to use this script. Where should I put it? How can I call it?

LOcalcQ gravatar imageLOcalcQ ( 2019-05-06 07:26:59 +0100 )edit

open your favourite Texteditor (no it isnt writer in this case) c&p the souce exactly into a new file and store it into path:

/home/$( you )/.config/libreoffice/4/user/Scripts/python/$(some_sensefull_name).py

run link_my_folder via >Tools>Makros>execute Makros>…

if the Script and the Funktion is not visible in the GUI you have probably to install libreoffice-scriptprovider-python with your Packet-Manager or apt-get or yum or whatever is used in Fedora.

karolus gravatar imagekarolus ( 2019-05-06 09:10:54 +0100 )edit

look also in this thread about APSO a very usefull Extionsion for managing python-Scripts in Libreoffice

karolus gravatar imagekarolus ( 2019-05-06 09:28:39 +0100 )edit

Thank you, this answered my question! Worked like a charm.

LOcalcQ gravatar imageLOcalcQ ( 2019-05-12 12:00:09 +0100 )edit

If so please tick the -mark left near my answer.

karolus gravatar imagekarolus ( 2019-05-12 15:09:22 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-05 19:14:30 +0100

Seen: 68 times

Last updated: May 05