Hyperlink to target within Microsoft Excel

[wolflore]

Hi all

I'm having trouble hyperlinking to a target within calc from draw.

I've used the automated function, added the file, the added the target information. But it only seems to allow linking to the sheet and not a target within a specific sheet!?

Can anyone help.

Regards

Anthony

[Villeroy]

Did you read the Survival Guide?
Which version on which operating system? What is the URL of the hyperlink? I tried 2 hyperlinks from Draw to Calc using the wizzard:
1. Hyperlink to cell C99 on sheet "DFUNCTIONS". I added ".C99" manually:
/home/andreas/Documents/OOo/aggregate.ods#DFUNCTIONS.C99
2. Hyperlink to a named range:
/home/andreas/Documents/OOo/aggregate.ods#criteria1

[wolflore]

Thanks Villeroy

The url is
L:/ISO 9000/Quality Manual/Internal Audit/Internal Audit Schedule.xls#Nonconformance.h20

Could it be that I am trying to hyperlink across platforms? I'm actually hyperlinking from OOo 2.3.1 Draw to a Microsoft Excel '97 spreadsheet. You gave me the answer to part of my problem. I wasn't sure as to how to link to the actual cell. The ".C99" bit helped here. This isn't in the manual But this doesn't work when linking to Excel. I have now been able to hyperlink to a target within calc, can this thread be used to resolve the link to Excel or do I have to start another?

Regards
Anthony

[Villeroy]

Sorry, this is over my head. OOo uses system independent standard-URLs since it is designed to run under many operating systems. The actual URL (displayed when hovering over a hyperlink) is file:///path/name.xyz#JumpMark. This is the same type of URL (uniform resource locator) that works with any internet browser including Explorer. Whatever Excel considers to be a valid URL, it is valid in it's own Microsoft universe alone. However, OOo should be able to convert that stuff when opening a Microsoft file format.

[Villeroy]

No, keep this unresolved thread. I'm shure that others are reading this. You may change the subject line of your initial post to something like "Incompatible hyperlink-URLs imported from Excel". At this point it would be interesting to know what the hyperlink URL looks like in Excel. Knowing this, there might be a way of conversion ... Possibly Excel puts "C:\path\name.xyz" into the hyperlink
Could you upload a simple xls containing a few hyperlinks that are valid in Excel? You can upload small documents to this forum.

[wolflore]

I don't think I have the permissions necessary to upload attachments!

[wolflore]

I had a play with Excel, here is what I could figure out.

the hyperlink creates the code as

\\server\path\test.xls - sheet2!G9

[Hagar Delest]

I don't think I have the permissions necessary to upload attachments!

If the file is bigger than 128 kB, use a file sharing web site (like mediafire.com).

[wolflore]

Sorry, finally found the right button to press! The BBcode user instructions don't point you to the tab at the bottom of the page but talk about a dropdown and also give the attachment code to put in to the post itself.

[Villeroy]

When you place the mouse pointer over your text-link you may notice that Excel's URL gets converted to a file-URL file:///c:/test.xls#sheet2!g9
See the wrong sheet delimiter "!" in the jump-mark? OOo seems to fail here. It does not convert the entire URL including the jump-mark. When you want to jump to a target within the same file, you don't need a full url pointing to this file. It requires a #jump-mark only (in Calc at least).
Try this formula: =HYPERLINK("#Sheet2.G9";"TEST") and click the resulting text "TEST".
Well, you see that it is possible to create hyperlinks from plain text by cell function HYPERLINK. A spreadsheet provides another function to get a cell's address by positional arguments #row and #column together with an optional sheet name. Try this with A2,B2,C2 or any other group of cells:
A2 =ADDRESS(9;7;1;"Sheet2")
B2 hyperlink [arbitrary text to be displayed]
C2 =HYPERLINK(A2;B2)
The formula in A2 should return the address-string of the cell in row #9, column #7 on Sheet2. In Excel it should return Sheet2!$G$9 (excl.mark) whereas in Calc it should return Sheet2.$G$9 (dot).
This way the hyperlink gets adjusted to the used application. You may want to format the HYPERLINK cells to make them appear more like hyperlinks (blue, underlined, italic text).
If you have many hyperlink fields like the one in your example file you may want to extract the URLs out of the grey boxes in order to make them accessible for manipulation and editing to be used with the HYPERLINK function.
[Calc, Basic]Introspective cell functions provides a function CELL_URL(#sheet; #row; #col) which is designed to pull out one or more URLs out of text-hyperlink(s) in a cell.
I used it in your sheet's B1 like this:
=CELL_URL(SHEET();ROW();1)
SHEET() returns this sheet's number, ROW() returns this cell's row number, 1 refers to column one. This is equivalent to reference $A1 from this sheet's row #1. "Normal" cell referencing does not work with this ugly hack of introspective cell functions.

I assume that Calc converts the main part (before jump-mark) of the hyperlink-URLs correctly. If there remain problems with the main part, we can solve them by ourselves once we pulled the URLs out of those nasty grey text-fields.

[wolflore]

When you place the mouse pointer over your text-link you may notice that Excel's URL gets converted to a file-URL file:///c:/test.xls#sheet2!g9
See the wrong sheet delimiter "!" in the jump-mark? OOo seems to fail here.

If you place it over it in excel you get C:\test\xls
I agree that the delimiter seems to be the cause of the confusion between the two programs.

It does not convert the entire URL including the jump-mark. When you want to jump to a target within the same file, you don't need a full url pointing to this file. It requires a #jump-mark only (in Calc at least).

It is the same with excel, you need only put in the target in the hyperlink box.

Try this formula: =HYPERLINK("#Sheet2.G9";"TEST") and click the resulting text "TEST".
Well, you see that it is possible to create hyperlinks from plain text by cell function HYPERLINK.

I used this formula in excel - yours did not work in excel.
=HYPERLINK("sheet2!g9","Goldfish")
There are similarities but they are not identical in their format. This again seems to be where OOo falls down.

A spreadsheet provides another function to get a cell's address by positional arguments #row and #column together with an optional sheet name. Try this with A2,B2,C2 or any other group of cells:
A2 =ADDRESS(9;7;1;"Sheet2")
B2 hyperlink [arbitrary text to be displayed]
C2 =HYPERLINK(A2;B2)
The formula in A2 should return the address-string of the cell in row #9, column #7 on Sheet2. In Excel it should return Sheet2!$G$9 (excl.mark) whereas in Calc it should return Sheet2.$G$9 (dot).
This way the hyperlink gets adjusted to the used application.

Yes I got the same return. How do I apply it to my problem?

If you have many hyperlink fields like the one in your example file you may want to extract the URLs out of the grey boxes in order to make them accessible for manipulation and editing to be used with the HYPERLINK function.
[Calc, Basic]Introspective cell functions provides a function CELL_URL(#sheet; #row; #col) which is designed to pull out one or more URLs out of text-hyperlink(s) in a cell.
I used it in your sheet's B1 like this:
=CELL_URL(SHEET();ROW();1)
SHEET() returns this sheet's number, ROW() returns this cell's row number, 1 refers to column one. This is equivalent to reference $A1 from this sheet's row #1. "Normal" cell referencing does not work with this ugly hack of introspective cell functions. I assume that Calc converts the main part (before jump-mark) of the hyperlink-URLs correctly. If there remain problems with the main part, we can solve them by ourselves once we pulled the URLs out of those nasty grey text-fields.

Sorry, my brain is slowly dribbling out of my ear. Are the grey boxes we are talking about the same grey boxes that we see when you click on the hyperlink button within draw?

This really is taking me up a level on my knowledge of both Calc and Excel - Thank you

[wolflore]

Hi Villeroy
I have tried editing the url manually now and have had no success in hyperlinking from draw to excel. I have tried to use many of your different formats but draw seems to stumble on the first hurdle. Namely #jump marks. If you put in the hyperlink file:///C:/test.xls this works perfectly. But it only opens on the first sheet. Try and add a # or a ! or a . to get it to go elsewhere and draw says that the link does not exist. This is beginning to be a real pain. Glad of your help though. Thank you.

[Villeroy]

Sorry for bothering you. Those text-hyperlinks impose several problems because they are so hard to edit.
- The target may have moved (dead link)
- You may want to the link target to point somewhere else
- You may want to do something smart, so the link points to some target depending on other parameters.
- Your particular problem is that one spreadsheet application does not import the URLs from another application 100% correctly.
The HYPERLINK function in Excel and Calc provides a fairly easy way out, even if you are not a software developer. You can keep a list of editable links and labels and let the function create the clickable link. However HYPERLINK needs an URL (or file name in Excel), which you have hidden in a text-field.
I have written a userdefined cell function CELL_URL which can pull URLs out of text-fields.

I found a similar userdefined function for Excel. It's named "GetAddress": http://www.ozgrid.com/VBA/HyperlinkAddress.htm

I'll attach another xls where I demonstrate how one may convert the URL according to the currently used application Excel or Calc. I started with your text-field in B1, extracted the URL in B2, replaced the formula with it's result (paste special) and split down the process into tiny steps from cell C2 to H2.
As demonstrated in row 6, I could have used the userdefined Excel function "GetAddress" as well (in Excel) to extract the URLs.

Edit: You could also refer to your target range by means of named ranges: =HYPERLINK("#Target1") where "Target1" is a named range pointing to $Sheet2.$G$9 or whatever. This works with text-hyperlinks as well since range names convert well between Excel and Calc and the jump-mark no longer contains the wrong sheet separator. Again, the problem remains that hyperlinks in text-fields are hard to edit.

[wolflore]

I am either totally incompetent or doing something wrong. I can't get the UDF GetAddress to work.

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

I followed the instructions on the web page but it told me there were syntax errors. I am in no way versed with Visual Basic and so didn't know where to start sorting it out.

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=GetAddress(A1)
Where cell A1 has a Hyperlink within it.
I understood and was able to do this second bit but it is here that I hit a wall!

I'm going to put the gun down and walk way from this one for a short while. My brain needs to regroup. I need to learn.
I don't know what it is you did, but i can't figure it ok. My knowledge is lacking when it comes to Excel (or anything else) and its functions.

[Villeroy]

Mmmh, syntax errors? I can't help you with VBA since the only Excel I have at the moment crashes as soon as I hit Alt+F11 to call the VBA editor
Copying code from a browser often includes unwanted spaces and other quirks.
Try to merge the split line:

Code: Select all

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

I would even prefer

Code: Select all

Function GetAddress(HyperlinkCell As Range)
GetAddress = HyperlinkCell.Hyperlinks(1).Address
End Function

because removing any prefix "mailto:" makes no sense to me and has nothing to do with our problem.

In OOo my function CELL_URL is by far less elegant due to some esoteric restriction in OOo, but I'm shure it works.
Menu:Tool>Macros>Organise>Basic... button [Organizer...], tab "Modules"
Browse "My Macros">Standard button "New..." creates a new module. Give a name such as "CellFunctions" or keep the default "Module1". Copy my code from the code-box in you browser, select all default content of the new module (Ctrl+A) and replace it with the clipboard content (Ctrl+V).
All usable cell functions have names starting with "CELL_" extracting something from a cell which is not the value. All those functions can not work like this: =CELL_FUNCTION(Sheet1.A1) because the code would only receive the value of the referenced cell Sheet1.A1 (this is the above mentioned esoteric restriction).
To get something from the very first cell in the first sheet you've got to use =CELL_FUNCTION(1;1;1) so the macro can reference the cell-thingy at the specified position in the document and extract whatever the function should extract. Functions SHEET(), ROW() and COLUMN() help us to get relative references which adjust to the right sheet, row and column when copied down. This sheet's cell in column 1 of the same row: =CELL_FUNCTION(SHEET();ROW();1)

[Villeroy]

Hi Villeroy
I have tried editing the url manually now and have had no success in hyperlinking from draw to excel.

Ooooooooooo, we are in the Draw forum, aren't we? Sorry, cell functions can not work in a drawing.
But you may be able to paste them into a sheet?
Well, I have that sinking feeling that we lost each other. Your links are text-fields in a drawing, calling spreadsheets which are supposed to be opened in Excel?

[wolflore]

Your links are text-fields in a drawing, calling spreadsheets which are supposed to be opened in Excel?

Hi Villeroy,

Yeah, that is why I was getting so confused.The hope was that I could maybe circumvent the issue that you've just noticed by linking to a link within Excel - or something along those lines.

I had the thought over night. Could I send Draw to my Excel spreadsheet to a reference cell containing a function and then somehow have Excel automatically jump from there? It seems a long shot but is there any way that Excel could recognise it was being opened by an OOo hyperlink and activate the link within Excel?

Regards
Anthony

[Villeroy]

Please, be patient with me! Would you please confirm or correct the following statements?
You have a drawing with hyperlinks.
The hyperlinks (or at least some of them) should open spreadsheets and jump to a defined mark by means of cell address or range address.
The hyperlinks' origin was Excel. You opened the Excel file(s) in Calc, copied the hyperlink-fields and pasted tem into the drawing.
Now, the hyperlinks work partially. They open the respective spreadsheet without jumping to the specified mark.
This is due to the fact that Excel's resource locators are correctly (or at least reasonably) translated to "file:///"-URLs but the jump-marks wrongly include Excel's sheet-separator "!".

[wolflore]

Hi Villeroy,

I'm patient, I'm surprised you're still here though

Please, be patient with me! Would you please confirm or correct the following statements?
You have a drawing with hyperlinks.

Correct

The hyperlinks (or at least some of them) should open spreadsheets and jump to a defined mark by means of cell address or range address.

Correct

The hyperlinks' origin was Excel. You opened the Excel file(s) in Calc, copied the hyperlink-fields and pasted tem into the drawing.

This is incorrect - the hyperlinks were generated within Draw.

Now, the hyperlinks work partially. They open the respective spreadsheet without jumping to the specified mark.

They do not work if there is a locator within the url - if the url is just the file path then the file will open to sheet 1 (default)

This is due to the fact that Excel's resource locators are correctly (or at least reasonably) translated to "file:///"-URLs but the jump-marks wrongly include Excel's sheet-separator "!".

Correct partly - see previous.

Thanks for all of this. You are being helpful above and beyond what I would ever have expected
Cheers
Anthony

[Villeroy]

OK, I think I've got it now. Of course we found another issue. Hyperlinks can load spreadsheets in Calc, disregarding any valid jump mark. Once the spreadsheet is already open the hyperlink works as expected. I tested the following hyperlinks in Writer and Draw, pointing to an Excel file to be opened in Calc.

Code: Select all

file:///tmp/test.xls
file:///tmp/test.xls#Sheet2
file:///tmp/test.xls#Sheet2.G9
file:///tmp/test.xls#Sheet2.A1:B2
file:///tmp/test.xls#NamedCell
file:///tmp/test.xls#NamedRange

The following ones try to open a non-existing file "/tmp/test.xls#Sheet2!A1:B2"

Code: Select all

file:///tmp/test.xls#Sheet2!A1:B2
file:///tmp/test.xls#Sheet2!G9

This is what I would expect:
1. Resolve the file-URL before the "#".
2. Load the file
3. Resolve the jump mark behind "#" after the file is loaded
4. Jump to the mark.

and a quick test reveals that it sees to work for Writer documents with bookmarks:
file:///tmp/test.odt#Bookmark

With spreadsheets it seems to parse the whole URL for validity and load the file if the whole URL is valid. If anything is wrong, pass over the URL to the browser and forget.
If the file is loaded already, jump to the mark.

Edit: Surprise, surprise. It works as expected with links pointing to native Calc documents (*.ods)

Code: Select all

file:///tmp/test.ods#Sheet2
file:///tmp/test.ods#Sheet2.G9
file:///tmp/test.ods#Sheet2.A1:B2
file:///tmp/test.ods#NamedRange
file:///tmp/test.ods#NamedCell

[wolflore]

My bad... Thought I'd resolved this and then realise I was linking to an OOo spreadsheet!