Can't get Libreoffice Calc to recognise relative hyperlinks

[Woody363]

I have a folder of files that I need to link to from the spreadsheet using HYPERLINK()

This folder will be zipped up and sent to others, so all the links need to be relative.

I have tried every variation that I can think of from the online examples, each either says it cannot be found or there is an error in the format.

e.g.
=HYPERLINK(“example.png”,“hi”)
=HYPERLINK("./example.png",“hi”)
=HYPERLINK(“file:/./example.png”,“hi”)
=HYPERLINK(“file:///./example.png”,“hi”)
=HYPERLINK(“file:///example.png”,“hi”)
=HYPERLINK(“file:/example.png”,“hi”)

What is the correct syntax fro a relative link?

[Regina]

Hyperlink to what kind of target? What should happen, when the user clicks on the hyperlink?

[Woody363]

They are all either images pdfs or html documents, and the should be opened externally, where is unimportant. The default “insert hyperlink to document” behaviour is completely fine

[gtomorrow]

<a https://ask.libreoffice.org/u/woody363>Woody,
Posting here instead of answering because I’m not 110% sure but I don’t think you can use =HYPERLINK() with relative links in Calc. Hard links work, but I get the same results as you do using relative links. I’m sure someone will correct me if I’m wrong.

[erAck]

Hyperlinks to external URIs have to be absolute.

You can construct the path specifier from the current document:

• A1: =CELL("filename")
• A2: =MID(A1;2;FIND("'#$";A1)-2)
• A3: =A2&"/../"
• A4: =HYPERLINK(A3 & "example.png";"hi")

The resulting hyperlink string is “file:///path/to/document.ods/…/example.png” which will be normalized to the desired file:///path/to/example.png URI.

[Regina]

=A2&"/…/" is nice I have struggled to get the part without the spreadsheets file name.

[erAck]

Yeah, stripping the document part is harder, so I was taking advantage of knowing URI handling

Side note: the path and document extraction can be fooled if a name contains the '#$ character sequence…

[erAck]

This may not work if the path fragments are verified to exist and be directories, so it would need to strip the document part with a regular expression (need to be enabled under Tools → Options → Calc → Calculate) so that would be

• A2: =MID(A1;2;SEARCH("/[^/]+'#\$",A1)-1)
• A3: =A2&"../"

Note that A2 now contains the URI with the final slash after the last path.

[Regina]

You can use Insert > Hyperlink > Document. Use the browse button and do not enter the path manually. The path is stored relative to the document, as long as the documents are on the same drive. Some is for using a push button.

If you want to use the HYPERLINK function, than you need to adapt the part with the absolute path manually. I do not know a way to extract that part by a function, not using a macro. But you can make a trick and get the complete absolute path to the spreadsheet document by the CELL function. Then extract the absolute part from it. I have done it with a trick. I have used the string for the spreadsheet file name (which should be known) and so find the part of the URL, which has the absolute path. Then you can combine the complete absolute path to the media out of this absolute part and the media file name.

You can see an example here: rhenschel.homepage.t-online.de/LO/movable_folder.zip