Ask Your Question
0

Can't get Libreoffice Calc to recognise relative hyperlinks

asked 2018-01-23 23:26:27 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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?

edit retag flag offensive close merge delete

Comments

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

Regina gravatar imageRegina ( 2018-01-24 00:45:01 +0200 )edit

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

Woody363 gravatar imageWoody363 ( 2018-01-24 10:09:06 +0200 )edit

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.

gtomorrow gravatar imagegtomorrow ( 2018-01-24 14:43:08 +0200 )edit

2 Answers

Sort by » oldest newest most voted
2

answered 2018-01-24 19:46:18 +0200

erAck gravatar image

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.

edit flag offensive delete link more

Comments

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

Regina gravatar imageRegina ( 2018-01-24 19:53:37 +0200 )edit

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 gravatar imageerAck ( 2018-01-24 20:09:43 +0200 )edit

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.

erAck gravatar imageerAck ( 2018-07-10 22:06:40 +0200 )edit
0

answered 2018-01-24 19:43:50 +0200

Regina gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-23 23:26:27 +0200

Seen: 1,040 times

Last updated: Jan 24 '18