How to find and remove external links in (Libre|Open)Office Calc spreadsheet

Question

I have a LibreOffice Calc spreadsheet which pops up a message asking me whether I wish to update external links. I did not add external links myself, but did copy data from another Calc window at one point, after which this issue started. (I assume this issue is not specific to LibreOffice, but also present in OpenOffice.)

The only hints I found were on the LibreOffice Forums: http://en.libreofficeforum.org/node/8150

Based on these hints, I investigated and found out the following:

• The menu Edit > Links is greyed out. (Edited, had erroneous External > Links before.)
• Searching for ///, ://, *.*s did not return any results.

So: how do I find and remove the alleged external links.

EDIT: I am using a .fods file, so searching through the xml source is something I can do; I found:

   ...
   <table:table table:name="&apos;file:///path/to/somefile.fods&apos;#Sheetname" table:print="false" table:style-name="ta_extref">
    <table:table-source xlink:type="simple" xlink:href="relative/path/to/a/temporary/copy/I/once/made/of/somefile.fods" table:table-name="Sheetname" table:mode="copy-results-only"/>
      ...
   </table:table>
   <table:named-expressions/>
   <table:database-ranges>
    <table:database-range table:name="__Anonymous_Sheet_DB__0" table:target-range-address="Sheetname.A1:Sheetname.C1048572">
    ...

So it seems that there is a hidden sheet in my file...

Answer 1

I also got that popup on startup:

I had the same problem and found out today that there is an option in the Edit menu called Links.... Select that option and you get a dialog with the list of external links found in your document.

Select the link(s) you want to remove and then click on the Break Links button. It will ask you for confirmation. Say Yes. Now the link is gone. Make sure to save. To test close LibreOffice and reopen your document. It should not ask you to update anything if you removed all those links.

What if Edit » Links... is Greyed Out?

As per a comment above by L. Levrel, it looks like LibreOffice keeps the Links... menu item greyed out if you load a file which is not a .ods file. In that case, try saving to a .ods, close everything then try reloading the new file. This time the menu item should not be greyed out. Of course, you may lose some formatting when converting between formats. Watch out!

Source: https://help.libreoffice.org/Common/Edit_Links

Answer 2

Every time I opened up a Calc Sheet I had copied, I received the same pop up "This file contains links to other files. Should they be updated?"

The spreadsheet I was opening was copied from another one, but each spreadsheet should stand on its own.

I came to this page looking how to get rid of that pop up - that is:

How can I find cells in my spreadsheet that have formulas linked to other files, so I can change the contents of THOSE cells so that they no longer link to other files?

Following snippets of the conversation here, I tried this:

Used Find and Replace, I changed the options to look in "Formulas", and to look at "All sheets" in the book. In the "Find:" text I typed in "file" (without the quotations).

By clicking on "Find Next" I was able to step through the sheet, finding each cell that had a formula with the text string "file" in it (which represents a link to another file), and removed the complete link to the other file, typically only leaving the actual cell references. (which then refer to the current sheet)

EXAMPLE:  the search found the formula:

    'file///c:documents/myusername/example.ods'B62

and I removed the file reference within the single quotes to leave

    B62

When "Find and Replace" did not find any more instances of "file" in forumulas, I saved and re-opened the sheet.

The pop up regarding links no longer appeared.

Answer 3

Had the same problem myself, and after reading the discussion here, I tried copying the sheets I wanted to a new file (in my case this was easy as I only had one sheet), and that fixed it for me. It may be that the problematic hidden sheet is in that file, and so creating a new file could avoid the problem. I'm not sure how this would go with a complex spreadsheet with lots of sheets though.

Alternatively, you could try looking up how to un-hide a sheet? (I've done this in Excel - VBA lets you get at additional levels of hiding too - but I think this might not actually be the problem, so I'd try the "copy to new sheet" first).

Answer 4

When performing the suggested approach 'Find all "file" formulas I noticed that this alone may not be sufficient. In my case Change recording was enabled which resulted in the link to the remote file still being active. Once all recorded changes were accepted and Track Changes was disabled, I could succesfully break the link to the remote file.

Answer 5

There is a simple way of blocking by going to, on the top toolbar: Tools/Options/LibreOffice Calc/General Top right of window is 'Updating/Update links when opening' Choose from: Always/On request/Never

Answer 6

For me the solution was simply to copy the file over to /tmp, open it in gnumeric, which listed all my fields with the wrong/incorrect references.

Then went back to libreoffice, and cleaned up the cells from gnumeric's error message.

To be sure, I have also explicitly "broke" the link before saving the file.

Answer 7

I encountered a similar problem. I had copied some rows from one sheet that contained links to a second sheet in the same spreadsheet file, to a second spreadsheet file. I then deleted the copied rows from the first spreadsheet. It turns out that there were other links in the first spreadsheet to the copied rows. I found that these links were, e.g., "=sheet1.#REF". The links did not update with the new spreadsheet name, but were left behind in ambiguous form. However, Calc somehow tagged them as links to external files. When I deleted the cells with the ambiguous links, the problem went away.

Answer 8

You may not want to remove the external link. An external link is a connection to a cell in another spreadsheet. To make an external link, press =, switch to the second spreadsheet and select a cell, press Enter. It acts as any reference to a cell, so you can use it in calculations. If the value changes in the second sheet, the first sheet will update the value when you update external links.

If the cell has a constant value, then you could copy and paste the cell from the second sheet to the first.

You could also combine the 2 spreadsheets into 1, so everything is in the same file. The external link becomes link to another sheet.