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...

Solution 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 Link button. It will ask you for confirmation. Say Yes. Now the links are 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

Solution 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.

Solution 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).

Solution 4

Another place where links to external files may exist is in the collection of defined names. These are easy enough to find, if they exist: on the "Sheet" drop-down menu, on the "Named Ranges and Expressions" fly-out menu, select the "Manage..." option (keyboard shortcut: CTRL + F3).

A dialog box appears with a 3-column list of all defined names; the columns are "Name", "Range or formula expression", and "Scope". You can enlarge the dialog box and change the column widths. The external links will appear in the "Range or formula expression" column, recognizable by text like file:///some/path/to/an/external/file.ods etc etc.

You can select each row with an external link and either Delete the defined name (and its external link), or edit the Range or formula expression to fix an invalid link (e.g., the external file may have been renamed) or otherwise correct the defined name.

Solution 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

View more solutions

Comments

[L. Levrel]

I have the same problem, or even worse: the file is .ods, Edit>Links is not greyed out, but following the procedure in Help (select linked file and click "break the link") does no good: after saving, closing, re-opening, the link is back! :angry:

[L. Levrel]

Found this so it looks like the linked file has to exist for the link to be removed! Wow. ("Break Link ... will embed the file.")

[L. Levrel]

The existence of the linked file is not enough: in Edit>Links, I could replace the non-existent linked file by another, randomly picked file (buttons Modify, then Update, then Close); after that, I saved, closed, reopened, Edit>Links, Remove link, saved, closed, reopened: "Do you want to update links"...

[L. Levrel]

Note: the greying out may be due to your file format. When I tried and saved my faulty file in xls format, the menu item was greyed out but LO still asked if I wanted to update links. So you may want to try and save your file in ods format, if only to see the Edit links window!

[LDC3]

You didn't read the second and third paragraphs. I gave you 2 options which will not have the pop up.

[LDC3]

You can delete all external links in the file by selecting File, Links and then deleting the files that are externally linked.

[equaeghe]

I did read the whole answer, but as is clear from my question, I neither manually added the links, nor am I interested in doing that. Furthermore, as stated in my question, Edit > Links (no File > Links present here) is greyed out.

[LDC3]

To find the sheet that has the link on it, move each sheet, one at a time, to a new file, close the spreadsheet and then open it to see if it asks to update. When it no longer asks, than the link is on the last sheet you moved.

[equaeghe]

As suggested by the source extract in my question, the link is not in any of the visible sheets, but in a hidden (or ‘anonymous’) sheet. I checked this by following your suggestion: even after removing all the sheets (adding an empty one was necessary for that), the pop-up message appears and in the source the same hidden sheet is present. (It shouldn't be, of course.)

[LDC3]

You could try saving the spreadsheet as a CVS file (I think it only saves one sheet at a time). At this point you can review the CVS file with Notepad to look for the link. Then read them into a new spreadsheet.

[L. Levrel]

Your solution worked for me, but you do not state clearly it worked for you! You should reword it. Note that one can copy several sheets at a time: simply select them and use Move/copy sheet as usual.

[equaeghe]

While this is a decent answer, in my case, “Edit > Links” is grayed out; see L. Levrel's second comment to my question for more about the “Edit > Links” functionality.

[PatrickT]

Good answer. This worked for me. But to clarify my experience: The search&replace pointed to the problematic cells. I then had to click on those cells to see the formula containing the 'file' reference. Fixing the file was done manually by fixing one formula and copy-pasting it across.

[AxelH]

While a full search failed to give me a result, this worked.

[Alexis Wilke]

@equaeghe Thank you for pointing that out. I updated my last paragraph to reflect what Levret said in his comment.