[Solved] Relative path for hyperlink

[Coiske60]

Hi, this is my first question:

I made a table with all the drugs (medicine) we have in house. With photo's of the package and the pdf's of the notices.
I also mention the end-date, main use and warnings
For each item I inserted a thumbnail of the photo and I created a hyperlink to the photo in full size.
I created this with Left Click on the thumbnail, an chose "Edit Hyperlink" in the Context-menu.
In the dialog-box I used the option "Document" and clicked then on the file of the photo. Then I chose for the option "Button"
The photo's are in the same directory as the table, the PDF's in a subdirectory
so I got as path : "G:\documents-W\sante\medicaments\acatar.JPG" for the photos
and "G:\documents-W\sante\medicaments\notices\acatar.PDF" for the notices

If I want to use this on another computer (a laptop) the place of the table, photos and pdf's won't be the same, but they all will remain in the same directory.

In <Tools-Options-Load/Save-> I checked "Save URL's relative to file system", so I hoped to get a relative path
The question is now: can "G:\documents-W\sante\" be omitted or replaced by a placeholder or an alias, so that I can use this system on multiple computers?

sorry if this question has allready been treated, but I couldn't find any satisfying answer

thanks
(Later on I intend to use this system for other lists, so this is a first test)

[Villeroy]

PathURL.ods

(18.5 KiB) Downloaded 1141 times

[Coiske60]

Thanks for the swift answer Villeroy, but it doesn't work;

This is what I did:

The Hyperlink that now is attached to the thumbnail i!s:
G:\documents-W\sante\medicaments\acatar.JPG

This actually works, but everything before "\medicaments\acatar.JPG" will be different when I put the table and photos on another PC that was the problem...


So I followed your advise (although I don't understand what I am doing):

First: Menu Insert - Named Expressions > - Define...

I entered these lines:

Name: PathURL
Range or Formula: MID(CELL("acatar");2;SEARCH("/[^/]+'#";CELL("acatar"))-1)
Scope: Document (Global)



Then: Instead of the original hyperlink-text (see above) I put this:

Document
Path: =HYPERLINK(PathURL&"medicaments/"&A2&".jpg";A2)
Button


When I Ctrl-Click on the thumbnail I get this message:

"=HYPERLINK(PathURL&"medicaments/"&A2&".jpg";A2)" is not an absolute URL that can be passed to an external application to open it."

So where did I go wrong?

[Villeroy]

Enter =PathURL into a cell and see. It should return the path of this document.
If it returns #VALUE! then you propably have to check Tools>Options>Calc>Calculation: "Regular Expressions" = ON

Simple alternative: Store the PathURL as a string (with "double-quotes") rather than formula.

[Coiske60]

I slept it over and tried again; this time I understand more or less the formula (In fact I typed an existing filename where you put "filename", so of course it didn't work!)
Now it works just fine, (i'll mark it as solved). But i didn't think the solution would be so complicated. I remember another program (dreamweaver?) that assumed that if you just put a filename in the hyperlink, it had to look in the same directory where the original file was and I got confused by "relative" and "absolute" paths.
But this works fine, so thank you very much.

[Lupp]

(Next correction:)
I also got cases where V5.2.3 saved links wrongly absolute in <draw:a ... > elements. <text:a ...> was ok.
(End correction)

(Editing again:)
Being upset about this I changed my plans and tested again with LibO V5.2.3 . It worked as expected and as I originally described first by me in this post with file-links anchored to text and to images as well. There is a bug in 5.3.
(End second editing)

(Editing:)
Sorry! I tested and found that relative paths in the file system were not found correctly in many cases. LibO V4.4.7 didn't adapt correctly at all on opening. V5.3.1.RC1 found relative linked files correctly if anchored to text. If the anchor was an image it didn't. The error may be due to bugs in the RC. I cannot spare the time for further testing at the moment. As I would judge the handling of relative paths a rather fundamental thing, I have to state: It's a mess.

AOO cannot use an image at all as an anchor for a link.
(End Editing)

As I understood you, you created the links without the HYPERLINK function.

If you do so, and later hover over the object the link was attached to, you get always shown the absolute path, independent of the settings. As long as the document isn't saved at all, there doesn't even exist something lika a relative path.

Having all the files in one folder when picking the links, and saving the masterfile into the same folder with the setting ...relative to the file system, everything should work also after moving (or copying first on a medium and later physically moving the medium) the complete folder elsewhere.

It should also work if there are more than one folders but names and relative positions are unchanged with respect to the topmost (farest from the FS root) common node in the file system tree.

Did you actually experience a problem working this way (except, probably the much time needed), or were you just afraid of such problems eventually arising?

[Coiske60]

Indeed, I tried with a small file an 3 links as text and 3 as bullets to PDF-files in the same directory. All worked fine.
Then I created an new directory, and Cut-Paste the previous one in it.
The links created as a text still work, and by hovering the new path is shown, but the links created as a bullet didn't change, and I got an error-message when I tried to activate the link.

This solution is of course easier, but indeed, the links on the bullets should have to work too.

Thanks for the hint

[Lupp]

This solution is of course easier, but indeed, the links on the bullets should have to work too.

Indeed.
I was eager to know about this and did some research. I even went to the common roots of LibO and AOO and can state now: There is a "legacy" bug. Hyperlinks anchored to graphical objects (tag in the content.xml starts with "<draw:a ") are always saved absolute disregarding the respective setting.
The longevity of bugs is terrific, truely. I could not find a clearly related bug report, and will file one when I find the time. Did nobody ever try the feature of linkable graphics? Free software should actually more care about reliabilty than about richness in features, shouldn't it?

An aditional remark: I did not yet try the working of such links after moving a respective subtree of files to the web. The links might work? Your bets, please.

[sqgl]

I managed to get the HYPERLINK function to work with PathURL (Thanks) however I cannot do the equivalent with a "cross-reference" (or whatever is the correct term?). I better give an example:

I tried converting a cell from
='file:///E:/TODO/POA/MONEY/Accounting/CSV/CBA/anastojsicnbs.csv'#$Sheet1.C39
unsuccesfully to
='PathURL&"/CSV/CBA/anastojsicnbs.csv"'#$Sheet1.C39

I also tried converting it to
=INDIRECT("'file:///E:/TODO/POA/MONEY/Accounting/CSV/CBA/anastojsicnbs.csv'#$Sheet1.C39")
and then incorporating the PathURL but again nothing I tried worked

I am thinking the problem is that single quotation marks make the expression literal and therefore PathURL is not expanded out? But how can I avoid using the single quote?

EDIT: Solved (albeit rather inelegantly)
=INDIRECT(CONCATENATE("'";PathURL;"CSV/CBA/anastojsicnbs.csv'#$Sheet1.C39"))

[Jayky]

PathURL.ods

Dear Villeroy,

I analyzed your example file and tried to build an own one. And I found something strange:
When I create an ODS file and try to get an output for =SUCHEN("/[^/]+'#";ZELLE("filename")) then I get only an error message. (Thats the German version of =SEARCH("/[^/]+'#";CELL("filename")))
The I've done exactly the same in a copy of your file and there was a number as an output.

Can you or someone else explain to me why? Do I have used a new file with the wrong ASCII? IF yes, how to check and change it?

Fun fact:
Instead of using the string of CELL("filename"), I cross linked to the other file to the string output cell and the other way. The result was, that in my file there was an error and in the other file there was a integer as a result.

By the way: With LibreOffice 6 it was NOT POSSIBLE to define an own variable, due to the option does not exist anymore I had to use a LibreOffice 5 version to create it. Really strange...

[Villeroy]

By the way: With LibreOffice 6 it was NOT POSSIBLE to define an own variable, due to the option does not exist anymore I had to use a LibreOffice 5 version to create it. Really strange...

Everything works exactly the same in all versions of OpenOffice and LibreOffice 5,6,7. LO hides named expressions in the new Sheet menu but shortcut Ctrl+F3 still works anyway.
German formula =SUCHEN("/[^/]+'#";ZELLE("filename")) still works with regular expressions in formulas enabled in a document that has been saved somewhere. The options under Tools>Options>Calc>Calculation are per-document options. They are stored in the document.