=HYPERLINK with cell references doesn't work reliably

[gtomorrow]

I did a search before I submitted this question but unfortunately nothing specific to my issue came up. So here I am…

I have a spreadsheet with a column (column A) listing hundreds of local files complete with its path (/path/to/folder of pictures/picture001.png through /path/to/folder of pictures/picture999.png). In another column (column B) I have only the filenames of the PNGs from column A (thanks to this nice bit of REGEX).

Now, in column C I have created the formula =HYPERLINK($A1;$B1) and filled down the rest of the entries. In theory, when I cmd-click on a cell in column C, LO should open the referenced PNG. And it does…sometimes! But more often than not I’m getting the dreaded

"file:///path/to/folder of pictures/picture003.png" is not an absolute URL that can be passed to an external application to open it.

The paths entered in column A are correct (I double-checked), the files referenced in column A exist (I double-checked) and are valid, and I also checked to see that Preferences > Load/Save > Save URLs relative to file system is selected.

I came across this question and followed the bug reports but the issue has since been closed. Am I doing something wrong? Could it be the spaces in the path are causing problems? But then, why do some links work correctly and others don’t? Should I file another bug report?

Thanks! – gt

EDIT: Version: 6.4.3.2, Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8, Mac OS X 10.13.6

EDIT 2: a much-reduced example of the document I’m working on. C:\fakepath\test_ods.zip.ods Change the extension to “.zip” and extract. PLEASE: the attached files are copyrighted and are included solely for demonstration purposes. Please delete after testing. Thank you

[mikekaganski]

Please provide an exact sample of error message with full URL cited.

[Lupp]

You didn’t mention your version of LibO. If I remember correctly it’s not too long ago that there was a bug causing issues of the kind you describe. That’s fixed for recent versions (at least for 6.4 0r higher).

[anon73440385]

Hello,
have you got some filenames using - (short dash) which have been translated into – (typographical dash) through setting Tools -> AutoCorrect Options... -> Tab: Options -> [x] Replace dashes? If yes, that may be the reason and it’s really hard to visibly recognize.

[gtomorrow]

Gentlemen, I love you all.

@mikekaganski, call me paranoid but I’d prefer not to. The example shown above is formatted exactly as if it were the exact sample. I will add that some filenames are normally named, some are camelCase, some VarIOUSly-titled. No strange/unallowed characters though.

@Lupp, I just added the pertinent information that you requested and that I should have added to begin with.

@anon73440385, this is not the case as some links with hyphens open correctly while others with no punctuation do not. No fiddly auto-correct has been applied: all cases (UC/lc) has been preserved and any hyphens have remained hyphens.

In the end, there’s no logic nor pattern that I can see behind why some work and (most) others don’t.

[mikekaganski]

Also remember that creating an URL by simply concatenating a local file path with file:// is not a correct method. Many characters - e.g. spaces - must be URL-encoded. There’s ENCODEURL to do that correctly.

[Lupp]

Hmmm. I personally still never create filenames (foldernames) containing spaces or any doubtable characters, but windows, e.g. does (program files) though they never were able to manage this to my satisfaction.
My old-style thinking:

1. Everyboy should only use names accepted by the classical name-syntax.
2. Ideas like “First line of text as filename” are absolutely evil - and everybody should know.

Concerning the version information: It refutes my suspicion. No additionaöl idea so far.

[mikekaganski]

Also note that not every OS allows for easy conversion to file: URLs, even using ENCODEURL. E.g., the mentioned Windows has things like backslashes \, which must be converted into forward-slashes /, instead of URL-encoded.

In StarBasic, there’s ConvertToURL exactly for that. In Calc, though not (yet: tdf#134896) documented, it’s easier to just pass plain local file path to HYPERLINK to do the magic - e.g. in form

=HYPERLINK("C:\windows-style path with any characters like абвгд\file.ext")

or

=HYPERLINK("/nix-style/path/to/file.ext")

… which would result in actual and correct hyperlink created.

For relative hyperlinks, it still might be problematic, and require ENCODEURL, because e.g. it’s impossible to reliably tell relative OS-dependent filepath from relative URL, esp. on Linux and the like.

[gtomorrow]

Gentlemen, if you’re still interested, I’ve uploaded an example document (see above). Thank you for your time.

[gtomorrow]

@mikekaganski, thank you, but your suggestion of ENCODEURL makes no difference. Actually it does: instead of getting…

"file:///path/to/folder%20of%20pictures/picture003.png" is not an absolute URL that can be passed to an external application to open it.

…using ENCODEURL I get…

"file:///path/to/folder of pictures/picture003.png" is not an absolute URL that can be passed to an external application to open it.

See the subtle difference? Nevertheless, the error remains.

@Lupp, yes, I know it’s “bad style” to create pathnames/filenames with spaces or other iffy characters. I usually use an underscore or a hyphen instead of spaces. But sometimes you gotta work with them, especially if you yourself didn’t create the directory structure.

[mikekaganski]

instead of getting… ... …using ENCODEURL I get… ...

or rather the other way round.

[mikekaganski]

In LibreOffice, launching executables are explicitly disabled for security reasons.

[gtomorrow]

@mikekaganski, you’re the best! I had suspected that the issue was non-supported formats (I had tried a test with PNGs instead of the OTFs with success) but didn’t realise some of these files were marked executable. Slightly offtopic: do fonts normally have the executable bit on? I didn’t think so…?

In any case, thanks for all your help!

[mikekaganski]

Slightly offtopic: do fonts normally have the executable bit on?

I actually don’t think they should. Executable bit means being able to start executing some payload when told so (e.g., started from command line simply using the file name). That doesn’t make much sense for fonts, whatever one may think about their internals. They are used by other processes; they may rather be thought of as libraries, or resources… Even “starting” them from command line, or by double-clicking, should be not considered as “starting” the font, but rather a request to start an associated viewer and load the font file as “document” into it.

But those are just speculations. I don’t use Linux much, and I don’t know the conventions and system-specific implications enough to provide a definite answer.

[makkev]

@mikekaganski, thank you very much for your answer. I turned off the execute permission for my link file (pdf) and I am now able to open the file. I nearly wanted to abandon LibreOffice for Excel which was able to open the pdf file with executable permission. Now I know better, that this is a security feature of LibreOffice.