Why does LibreOffice Calc ask about updating links in Excel 2007 .xls file when Edit | Links is disabled?
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):
- Open the .xls file
- Say "No" to the question
- 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)
- Open the new .sxc file
- Say "No" to the question
- Go to
Edit | Links... - Break any links you find
- Save the file
- Use "Save as" to save it back to whatever format you actually want (.xls, .ods, etc.)
- 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.
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:
- COPY your file and try this procedure out on the copy first !
- Open the copy of your .xls file
- Say "No" to the question
- Save the file as a Flat XML ODF Spreadsheet (.fods)
- Close Calc
- 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.
- Search on
file:///- this precedes the name of the file being linked to. I found myfile///: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
- I deleted the entries bounded by
<table:named-expressions>and</table:named-expressions>which hadfile:///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 ! - 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.
- Use Calc to open this modified file.
- Save as whatever format you want e.g. .xls, .xlsx, ods, etc. and then close Calc.
- 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="['file:///home/myname/filename.xls'#$''.$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.