Why does LibreOffice Calc ask about updating links in Excel 2007 .xls file when Edit | Links is disabled?

Question

(I don't believe this is duplicate of LibreOffice Calc prompting to update “links to other files”. Why?. See below for why.)

The title basically says it. I have an .xls file originally created with Excel 2007. As far as I'm aware, it doesn't have any links in it, and Links... on the Edit menu is disabled (greyed-out). When I open it in LibreOffice 3.4.4, it asks

This file contains links to other files.

Should they be updated?

[Yes] [No]

I've always said No. Again, as Edit menu, Links... is greyed-out, apparently there aren't really any external links. I don't think the file even has internal links. It does have multiple worksheets in it.

I've seen the question I mentioned above, but the OP wasn't sure whether Edit | Links... used to be active and stopped getting the question, and the only answer there assumes that Edit | Links... was active, so clearly not an answer to what I'm looking at.

How do I fix the file so I don't get this question every time I open it (without changing my options to always update links)?

---

Immediately after posting the question, I thought: I wonder if saving it as an .ods file will fix it. So I did that, and then opened the .ods file. I got the question, but when I say "No," the Edit | Links... menu item is enabled, and when I go into it, I find what LibreOffice thinks are links to other files.

So I thought I had an answer, but sadly no. If I tell it to break both of the links it displays and save the .ods file, the next time I go back in, one of the links reappears on the list. Removing it again, resaving, and going back in don't help; I did it at least four times and the link just keeps reappearing.

Answer 1

I found a workaround. Hopefully somebody will have a better answer, but if not, perhaps this will help anyone else having the same problem.

Here's the workaround (this was with LibreOffice 3.4.4):

1. Open the .xls file
2. Say "No" to the question
3. Save the file as an OpenOffice.org 1.0 spreadsheet (.sxc) (not .ods, that won't work, see the update at the end of the question)
4. Open the new .sxc file
5. Say "No" to the question
6. Go to Edit | Links...
7. Break any links you find
8. Save the file
9. Use "Save as" to save it back to whatever format you actually want (.xls, .ods, etc.)
10. Delete the temporary .sxc file

Now the links are well and truly broken. Seems like two different bugs in LibreOffice here (not showing the links when you open the .xls file, and not successfully breaking them when saving to .ods and using Break Link), which I'll go report.

Answer 2

I have had a similar problem for ages in multiple sheets. Edit -> Links is always greyed out and re-saving in other formats (like ODS) as referenced above and recommended elsewhere never works for me.

What did work for me (using Calc 5.1.6.2 under 32 bit Linux Mint 18.3 - yeah, I have some old kit !) was this:

1. COPY your file and try this procedure out on the copy first !
2. Open the copy of your .xls file
3. Say "No" to the question
4. Save the file as a Flat XML ODF Spreadsheet (.fods)
5. Close Calc
6. Find your .fods file and open it with a Text Editor. I am on Linux so I used xed but I imagine things like Notepad (on Windows) and Textedit (? on mac) will do just as well.
7. Search on file:/// - this precedes the name of the file being linked to. I found my file///: entries at the very bottom of the file, in an XML bounded area for Named Expressions. I'm not really sure what these are although judging from the individual entries, it looked like they have something to do with print ranges.

BE CAREFUL NOW: your spreadsheet may not be as simple as mine and what comes next may spoil what it tries to do

8. I deleted the entries bounded by <table:named-expressions> and </table:named-expressions> which had file:/// references found by my search. You may want to pick & choose which entries you delete - see below, but make sure that you keep the integrity of the XML. Like I said, TRY THIS ON A COPY first !
9. Save the TEXT file that you have been editing (maybe with a different name again e.g. file_modified_name.fods") and close your editor.
10. Use Calc to open this modified file.
11. Save as whatever format you want e.g. .xls, .xlsx, ods, etc. and then close Calc.
12. Reopen the file that you just created with Calc - if you have got your editing correct, you should find the prompt about updating links has gone.

What was interesting was that with one exception, the file names reflected the locations on USB sticks which this very long-lived spreadsheet had resided on over several years as my main PC has changed.

I have caveated this heavily because I don't know what effect this has on the spreadsheet, but it seems to work for me: your mileage may vary ;-) !!!

FWIW: within the bounds of the <table:named-expressions> and </table:named-expressions> tags that I removed were about 12 individual items looking similar to this: <table:named-expression table:name="Excel_BuiltIn_Print_Area_14" table:base-cell-address="$Key.$A$1" table:expression="[&apos;file:///home/myname/filename.xls&apos;#$&apos;&apos;.$A$1:.$T$25]"/>

In one case, the table:name part was pointing to one of the workbook's own spreadsheets i.e. NOT an external link. Maybe that should be kept, but really, I don't know.

In some cases, the table:name part was for Excel_BuiltIn_Print_Titles_11 (or whatever number).

---

This issue has annoyed me for years but this seems to have worked for me but there are no guarantees so do keep a "good" copy of the old file that you can go back to if this does not work for you. Hopefully, my experience may help somebody who has had similar difficulties.

Answer 3

The links may be in range names, try look at Insert|Names|Manage there may be range names created by Excel that were linked to external files

Answer 4

Expanding a little on the answer form @wkcsgm, Excel/LibreOffice/whatever let you name ranges to use as shortcuts elsewhere. It's described in the LibreOffice documentation. That led me to a reasonable resolution to this problem. Apparently, once upon a time, I had made a graph in my spreadsheet referencing data from another sheet. I then deleted the graph. But various names of things from the graph had been saved as "Managed Names"; they looked like label names. It was a long time ago. I've been getting a message about "...links to other files" every time I open the spreadsheet, but that's not super-often so I haven't cared until today.

But I digress. Although there were no links to other data, there were references to those labels in the "Manage Names" menu one gets to by Insert > Names > Manage. Once I removed those names, saved, and reopened my document, the dialog didn't come back.

I think this work today will save me upwards to two minutes' worth of unnecessary clicks on "no" over the course of my lifetime, but I feel better now that I understand where those links came from.