[SOLVED] URL for relative path to local file in hyperlink

[bertram]

spreadsheet .ods is running from a folder that is two folders under program root
target txt file to open by hyperlink in .ods is 2 folders up and (x) folders down

I need to use =HYPERLINK() formula -- impractical to use Insert > Hyperlink dialog for 100s of links 1 by 1

Code: Select all

address with windows-\
folder .ods source       M:\3_patches\UpdateNotes\Windows7-x86
.ols fullpathname        M:\3_patches\UpdateNotes\Windows7-x86\Windows7-x86_setFiledates.ods

target file              M:\3_patches\Updates\Windows7-x86\Additional\_IE11\IE11-Windows6.1-Updates.txt

absolute address with URL-/)
works ok         file:///M:/3_patches/Updates/Windows7-x86/Additional/_IE11/IE11-Windows6.1-Updates.txt

relative address (two folders up from running .ods source)
fails                   file:///../../Updates/Windows7-x86/Additional/_IE11/IE11-Windows6.1-Updates.txt

where do I go wrong?

(I must be world's worst searcher -- search engines return file after file, none of which addressing this question ... URL to local file)
F1 Help is no help. It says file:/// url should be same as url string given in File Open. File Open opens file open dialog, which does not show relative URL string. I know the folder heirachy, but relative URL is failing ...

[RusselB]

I don't see anything in the help file that states or implies that a relative address can be used.
All of the examples that I see use an absolute address
This might be worked around to use a generated absolute address by storing the address(es) in a helper column and then referencing the cell address in the helper column for the URL

[Villeroy]

All paths in AOO, including the hyperlink fields, are relative paths even though they are displayed as absolute paths.
Of course, a literal absolute URL in a formula is absolute. But you can calculate the current document's path:

Code: Select all

=MID(CELL("filename");2;SEARCH("/[^/]+$";CELL("filename"))-1)

with Tools>Options>Calc>Calculate: "Regular expressions in formulas" enabled.
Now you can store only the path/file.xyz portion and concatenate it with the calculated path.

[MrProgrammer]

I need to use =HYPERLINK() formula -- impractical to use Insert > Hyperlink dialog for 100s of links 1 by 1

That's unfortunate. If you've specified option Load/Save → General → Save URLs relative to file system, the Insert → Hyperlink dialog would open the correct relative link after you provide the absolute link during link creation.

With the HYPERLINK function Calc opens the exact absolute link you've specified for the first parameter. However you do have control over what's opened because the first parameter can reference a cell with a formula. Let's arrange the work like this:

X2: Relative link you'd like to use
X3: =SUBSTITUTE(CELL("FILENAME");"'";"")  Current spreadsheet
X4: =LEN(X2)-LEN(SUBSTITUTE(X2;"../";"")) Count of relative level characters in X2 → ../../ is six characters
X5: =LEN(X3)-LEN(SUBSTITUTE(X3;"/";""))   Count of shashes in current spreadsheet file name
X6: =SUBSTITUTE(X3;"/";CHAR(9);X5-X4/3)   Mark end of what relative levels represent with special character
X7: =LEFT(X6;FIND(CHAR(9);X6)-1)          Determine represented levels for current spreadsheet
X8: =SUBSTITUTE(X2;LEFT(X2;7+X4);X7)      Replace relative level directories in X2 with X7

Then your hyperlink function would be =HYPERLINK(X8). I can imagine this is more complex than you'd prefer. Perhaps you can use Villeroy's simpler implementation of the same basic idea. My picture above shows the cell values I think you'll see on your system.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[bertram]

thanks Villeroy and MrProgrammer

first lesson for me was that every URL to a local file must begin with file:/// + drive letter + : + /

URLs in OO are not like in DOS where I can start with a command prompt location, and them go up 1 folder with ../, etc

thus for a URL with driveletter that goes up 2 folders from current location of .ods file

Code: Select all

=MID(CELL("filename"),2,SEARCH("/[^/]+/[^/]+/[^/]+$",CELL("filename"))-1)
thus for me
=CELL("filename")
produces: 'file:///M:/3_patches/UpdateNotes/Windows7-x86/Windows7-x86_setFiledates.ods'#$Sheet6

then expanding Villeroy's formula to find / two directories up
=MID(CELL("filename"),2,SEARCH("/[^/]+/[^/]+/[^/]+$",CELL("filename"))-1)
produces: file:///M:/3_patches/UpdateNotes/

This is just what I'm looking for ... THANKS!

=== edit ===
this might be a little cleaner -- to go up n directories from current folder for ods file,
use REPT("/[^/]+",n+1)&"$") for regex string
thus to go up 2 folders, repeat regex 2+1 = 3 etc.

Code: Select all

to start
=CELL("filename") >>
'file:///M:/3_patches/UpdateNotes/Windows7-x86/Windows7-x86_setFiledates.ods'#$Sheet6

then, to go up 2 directories, like ..\..\ at dos command prompt, then repeat regex 3 times
=MID(CELL("filename"),2,SEARCH(REPT("/[^/]+",3)&"$",CELL("filename"))-1) >>
file:///M:/3_patches/