Ask Your Question
0

How to import MS Excel spreadsheet with links? [closed]

asked 2014-06-23 07:32:23 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

When I open the xlsx file (or convert it to ods file) then the links within the spreadsheet do not work. The reason is that the format of Excel link starts with [#] whereas the one of Libre Office starts just with the # without []. Likewise with the separator ! and . between the Sheet name and Cell(s) name in the link. It is possible to edit the link by manual erasing the [ and ] characters. Then the link starts working even without replacing the exclamation mark by the dot. That is possible for a few links but not for hundreds. When I use the Search/Replace [#] to # then the link is corrected, however the displayed text in the cells disappears. In this case replacing the ! by . is also necessary. Batch replacing leads to the correct functionality of the links but also modifies the text strings where the ! marks were used as linguistic exclamation.

Is there any way how to batch correct the references in the spreadsheet when the source is xlsx and target ods? Why the conversion procedure between xlsx and ods does not the appropriate modification of the link format?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-26 09:56:21.261050

Comments

Sorry! Don't understand.

1) Assumed "the links within ..." refer to cells or to cell ranges: Calc should be able to open XLSX and to convert it automatically to its own standards. What did hinder this in your case?

2) Why do you get references "within the spreadsheet" using the full syntax of references into other files?

3) May you refer to something like (cell or range) ADDRESSES given as text for some reason?

4) Please QUOTE one of the cell contents you complain about.

Lupp gravatar imageLupp ( 2014-06-23 11:58:16 +0200 )edit

Here is an example. When I open the xlsx file then the link expression in the cell looks like this: =HYPERLINK("[#]Attributes!B4:D4";"Material Type") where Attributes is the name of another sheet in the document and Material Type is the text displayed in the cell.

I think that it should read like this: =HYPERLINK("#Attributes.B4:D4";"Material Type") I.e. without [] and . instead of ! mark. At least when I edit the expression to this form then it works as needed.

tichyb gravatar imagetichyb ( 2014-06-24 15:05:09 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2014-06-24 21:41:10 +0200

Lupp gravatar image

updated 2014-06-24 22:00:12 +0200

'Find & Replace', 'Regular expressions' enabled, 'Search in' 'Formulae'

'Search for'

\[#\]([^!]+)!

'Replace with'

#$1.

See also attached image! FindReplaceInternalHyperlinkExcelToCalc.png

(Here are to take EXACTLY the characters as given in the dedicated lines. These Including ! and . Regard this dot after $1)

[begin EDIT]

I First couldn't get the backslashes correctly displayed. Hope it's ok now.[end EDIT]

edit flag offensive delete link more

Question Tools

2 followers

Stats

Asked: 2014-06-23 07:32:23 +0200

Seen: 1,369 times

Last updated: Jun 24 '14