Excel-to-Calc migration problem with hyperlinks

[mfaynberg]

Hello everyone,
during the ongoing transition from MS Office to StarOffice I found out that when reading an Excel spreadsheet the calc does not understand the hyperlinks, if a cell contains anything but explicit text (number, date, etc.). Moreover, if having a number in the hyperlinked cell I go to Excel and change its format type to Text, it does not help unless it contains only digits. Say, a cell, which contains "1003" as a hyperlink text is not converted to a StarOffice hyperlink despite what format category was assigned to it, while a cell containing "1003 *" is.
It is a nuisance, because I have a lot of existing files to convert. Is there any way around?
Thank you in advance,
Mike

[Villeroy]

What are typical URLs, addressed by your hyperlinks?
"C:\path\name.doc" may work with Excel and Excel only. A valid file-url is "file:///C:/path/name.doc", a link to a bookmark of a text document: "file:///C:/path/name.doc#Ramblings", a spreadsheet: "file:///C:/path/name.xls#SheetX.A1:B5", in this document: "#SheetX.A1:B5"
Is there any way to extract the URLs from the displayed labels in Excel? If so, you could rebuild Calc hyperlinks by function =HYPERLINK(A1;B1) where A1 contains the URL and B1 the label.

[mfaynberg]

Villeroy, thank you!
In fact the links are file links formatted like:
../Dir1/Dir2/Dir3/Dir4/File.ext or Dir1/Dir2/.../file.ext
According to your reply a not fully qualified filename may not work - but I have hundreds of such links spread over my files (and almost all of them are represented by relative pathnames). What can I do about it?
Thank you again,
Mike

[Villeroy]

Open Excel and follow the instructions from

This UDF will extract the underlying address from a cell containing a Hyperlink

The Code

Function GetAddress(HyperlinkCell As Range, Optional n As Integer)
REM Villeroy: No, we don't want to remove any protocol "mailto:":
REM GetAddress = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")

If isMissing(n) then n = 1
GetAddress = HyperlinkCell.Hyperlinks(n).Address
End Function


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.

This function can be used like any other function to extract a hyperlink's URL from a given cell. I modified the function to accept an optional 2nd argument to get the 2nd, 3rd URL if there are more than one hyperlink in a cell =GetAddress(A1;2)
After you pulled out the URLs do a copy and paste special in order to convert the function results to stable text values.
Lists of hyperlinks in a spreadsheet are easy to maintain if you keep plain text URLs and labels to be assembled by function =HYPERLINK(A1;B1) so you can simply edit the cell values in A and B if anything changes.
Once you got the URLs you probably can not use them with Calc, but let's do this first and see the results before applying the next trick.

[Willer]

is there a way to build a Macro / Function to work like the Excel Follow.Hyperlink function..?

I'd like to write code to attach to different Images/Buttons etc..!
something like ..
Function Open_Hyperlink(address as String; Text as String) as Boolean
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Open_Hyperlink=False
dispatcher.executeDispatch(document, ".uno:Open", address, 0, Array())
Open_Hyperlink=True
end Function

This code obviously doesn't work .. ! lol

Thx for any assist