relative HYPERLINK

users
#1

Hello!

I'm working with Libre Office 3.6.0.4 on Windows 7 64-bit.
In Calc I used the formula
=HYPERLINK(VERKETTEN("B\";B974;".jpg");VERKETTEN("B\";B974;".jpg"))
to produce an Hyperlink to a local Image based on the reference to B974.
When moving the cursor above the cell the corresponding destination is shown
and is OK (and its relative).

When clicking oder Strg-clicking on the cell I get:
B\20123.jpg ist keine absolute URL, die zum Öffnen an eine externe Anwendung
übergeben werden kann.

something like "Cannot submit URL to external Application because URL is
relative."

When I produce an absolute URL it works fine.
Exporting to Excel with relative URL works.

But I have to pass the spreadsheet to users with libre Office who want to
put the Sheet and Images in a different directory than on my computer :slight_smile:

Is it a bug or do you know a workaround?

#2

Am 15.08.2012 17:01, chilobo wrote:

Hello!

I'm working with Libre Office 3.6.0.4 on Windows 7 64-bit.
In Calc I used the formula
=HYPERLINK(VERKETTEN("B\";B974;".jpg");VERKETTEN("B\";B974;".jpg"))
to produce an Hyperlink to a local Image based on the reference to B974.
When moving the cursor above the cell the corresponding destination is shown
and is OK (and its relative).

When clicking oder Strg-clicking on the cell I get:
B\20123.jpg ist keine absolute URL, die zum Öffnen an eine externe Anwendung
übergeben werden kann.

something like "Cannot submit URL to external Application because URL is
relative."

When I produce an absolute URL it works fine.
Exporting to Excel with relative URL works.

Excel does not even know what an URL is. Excel uses Windows specific path names.

Open some local *.html file in your favourite browser and look at the file:// URL in the address bar.
More specific information about the internet world:

http://de.wikipedia.org/wiki/URL

Save the document somewhere so it has some path.

Calculation: "Enable regular expressions in formulae"

Insert [Ctrl+F3]

[Add]
Name: MyPath (or something else)
"Range" (idiotic label since "named ranges" are formula expressions):
MID(CELL("filename");2;SEARCH("/[^/]+$";CELL("filename"))-1)
in German:
TEIL(ZELLE("filename");2;SUCHEN("/[^/]+$";ZELLE("filename"))-1)
[Add], [OK]

Test with cell formula =MyPath should return the URL-path including a trailing slash.

=HYPERLINK(MyPath&"B/"&B974&".jpg") creates a hyperlink pointing to a .jpg file in subdirectory B with the name in cell B974.

#3

Andreas Säger wrote

wrote:

Excel does not even know what an URL is. Excel uses Windows specific
path names.

Open some local *.html file in your favourite browser and look at the
file:// URL in the address bar.
More specific information about the internet world:

http://de.wikipedia.org/wiki/URL

Save the document somewhere so it has some path.
>>>Calculation: "Enable regular expressions in formulae"
>>Insert [Ctrl+F3]
[Add]
Name: MyPath (or something else)
"Range" (idiotic label since "named ranges" are formula expressions):
MID(CELL("filename");2;SEARCH("/[^/]+$";CELL("filename"))-1)
in German:
TEIL(ZELLE("filename");2;SUCHEN("/[^/]+$";ZELLE("filename"))-1)
[Add], [OK]

Test with cell formula =MyPath should return the URL-path including a
trailing slash.

=HYPERLINK(MyPath&"B/"&B974&".jpg") creates a hyperlink pointing to a
.jpg file in subdirectory B with the name in cell B974.
chived and cannot be deleted

I was thinking about a similar solution: My users of the spreadsheet would
have to enter the absolute directory where they store the spreadsheet (and
the directory for the images) and I would use this path to produce an
absolute path.

This enlarges my sheet and because I also have hardlinked (without formula,
just relative links) data in the spreadsheet requires further action ....

Why can't Calc handle relative links correctly?

Thank you for your answer.

#4

Am 15.08.2012 17:50, chilobo wrote:

Why can't Calc handle relative links correctly?

Let's do a simple test with a relative URL:
=HYPERLINK("./B/file.xyz")
[Click]
Security warning: "For security reasons, the hyperlink can not be executed."

Whenever you use an ordinary static hyperlink anywhere in this office suite, it will be treated as a relative one even when it is displayed as an absolute URL:

Hyperlink... point to ./B/file.xyz

Now copy the document together with the B folder to some other place and open the new document. The hyperlink will point to the file in the new B folder.

#5

Am 15.08.2012 17:50, chilobo wrote:
> Why can't Calc handle relative links correctly?
>

Let's do a simple test with a relative URL:
=HYPERLINK("./B/file.xyz")
[Click]
Security warning: "For security reasons, the hyperlink can not be
executed."

This happened in version 3.5.x.y
I installed 3.6.0.4 today.
There Calc states:
... ist keine absolute URL, die zum Öffnen an eine externe Anwendung
übergeben werden kann.

Whenever you use an ordinary static hyperlink anywhere in this office
suite, it will be treated as a relative one even when it is displayed as
an absolute URL:
>Hyperlink... point to ./B/file.xyz
Now copy the document together with the B folder to some other place and
open the new document. The hyperlink will point to the file in the new B
folder.

I found that too, but that doesn't help me. I have a growing database, now
about 1000 datasets, in future about 4000. I will have about 5000 links to
pictures and PDF-documents of which about 4500 will be generated by
formulas, about 500 entered directly, but relative because I have to pass
the sheet to different users using Excel and Calc.

#6

Am 15.08.2012 19:01, chilobo wrote:

I found that too, but that doesn't help me. I have a growing database, now
about 1000 datasets, in future about 4000. I will have about 5000 links to
pictures and PDF-documents of which about 4500 will be generated by
formulas, about 500 entered directly, but relative because I have to pass
the sheet to different users using Excel and Calc.

You can not misuse a spreadsheet like this and expect that it works in 2 applications. Things may change if Excel 13 really provides full ODF support but nobody believes that MS will go that far.
Needless to say that no spreadsheet application will ever work like a database.
This would be very easy to implement with a Base database and a few lines of macro code.

#7

Andreas Säger wrote

You can not misuse a spreadsheet like this and expect that it works in 2
applications. Things may change if Excel 13 really provides full ODF
support but nobody believes that MS will go that far.
Needless to say that no spreadsheet application will ever work like a
database.
This would be very easy to implement with a Base database and a few
lines of macro code.

I know that, ultimately we will transfer our data to a database. But during
the process of entering data it is easier for the different users to use the
well-know spreadsheet software :slight_smile:

Anyway: Shouldn't Calc be able to work with relative hyperlinks correctly?

#8

Am 15.08.2012 19:21, chilobo wrote:

Anyway: Shouldn't Calc be able to work with relative hyperlinks correctly?

Obviously, the developers have a different opinion. They block this feature with a warning message. At this point I trust that the developers know very well what they do.

#9

I am puzzled by the sharing of the spreadsheet. Are the files stored on a server with a fixed location or they passed out to users to install locally?

Also, I would try setting up the linked files in a sub-folder of the spreadsheet folder. If the the spreadsheet is in folder Project the sub-folder would be Project/Files

See the following from the LO help

#10

Jay Lozier wrote

I am puzzled by the sharing of the spreadsheet. Are the files stored on
a server with a fixed location or they passed out to users to install
locally?

Also, I would try setting up the linked files in a sub-folder of the
spreadsheet folder. If the the spreadsheet is in folder Project the
sub-folder would be Project/Files

See the following from the LO help

  *

    The following rules apply: A relative reference
    ("graphic/picture.gif") is only possible when both files exist on
    the same drive. If the files are on different drives in your local
    file system, the absolute reference follows the "file:" protocol
    ("file:///data1/xyz/picture.gif"). If the files are on different
    servers or if the target of the link is not available, the absolute
    reference uses the "http:" protocol ("http://data2/abc/picture.gif").

The image files are stored in a directory "B" (german "Bilder") relative to
the directory of the spreadsheet, the other links are stored in similarly
abbreviated subdirectories.
Because some oft the other files are quite large the whole structure is
copied to locally available subdirectories on the users private PCs - some
Windows, some linux.
This is _not_ the problem. The problem is, that relative links don't work in
spreadsheets in Calc.

#11

Am 15.08.2012 23:22, chilobo wrote:

The image files are stored in a directory "B" (german "Bilder") relative to
the directory of the spreadsheet, the other links are stored in similarly
abbreviated subdirectories.
Because some oft the other files are quite large the whole structure is
copied to locally available subdirectories on the users private PCs - some
Windows, some linux.
This is _not_ the problem. The problem is, that relative links don't work in
spreadsheets in Calc.

... because the developers account calculated relative hyperlinks as a security risk. As a work-around I showed you how to improve your formula so it uses a calculated absolute path in URL notation. Of course, the very same method can not work with Excel which does not know regular expressions.
You may try some switch to distinguish between Excel and Calc:
MID(ADDRESS(1;1;1;1;"foo");4;1) returns the sheet separator "." in Calc and "!" in Excel. Can be used as a named expression:
=HYPERLINK(IF(ShSep="." ; calc_calculation ; xls_calculation))