Calc external links warning but search for file:/// does not find any links

[tarsajuma]

The issue I am having sounds very similar to this link

Calc document thinks there is an external link, but there is not

When I open a certain calc file I get the message, “Automatic update of external links has been disabled”. However, there are no external links in the document. There had been such a link in the past, but it was changed to a lookup of an range internal to the document. There are no macros in the document. The problem occurs only in this file; no other file has the same problem. So, two questions: I have used Search & Replace to try to find some external link, but no success. Is there anot…

But that link is 2 years and my issue is occurring for multiple files so I created a new post.

I am observing the issue with multiple *.xlsx files, all of which were initially created with MS Excel. When I open these with LibreOffic Calc, I get the warning message:

      Secuirty Warning Automatic update of external links has been disabled.

Based on other posts, I then used the Edit, Links to External Files command and it shows a link to a different *.xlsx file on my hard drive, for example…

      Sourcefile        file:///D:/Data/subdir/filename.xlxs
      Type                Dcoument
      Status             Manual

From the suggestions of several other posts, I used the Edit, Find and Replace command and searched for the string “file:///” in the entire workbook but nothing is found.

In this another post user sokol92 commented that one can search the *.ODS file as it is a zip archive. Using an online ODS search app I searched for the string “file” which returned a hit for every sheet in the workbook, but only one of the hits has any Cell Name, Cell Value, etc. This cell has the text string “FILE” in it so that search makes sense. All other hits only list the sheet name. See the sample ODS search output below:

Searched Text: file
===========================================
Sheet Name: Summary
===========================================
Sheet Name: Check-Save-VISA
Cell Name: K981
Cell Value: SRI-360OPS DES:SIGONFILE ID:X****
ID:XXX****
===========================================
Sheet Name: SCHWAB
===========================================

Oddly, if I open the original *.xlsx file in Excel, and search for external links, none are found.

I am aware that Excel files store a history of all formulas since file inception called a calc chain (learned this from a recent podcast Two behavioral scientists who study honesty accused of using falsified data : Planet Money : NPR). So I am suspicious Excel is leaving some sort of artifacts in the file. Oddly, if I open this *.xlsx file in Excel, no external links are found.

So after than long winded explanation, I am looking for a method to find and remove these artifacts in the ods files. A kludge solution I found is to separately move each sheet to a new workbook then save the new workbook to a new *.ods file. But since I have tens of workbooks to fix I am hoping there is a better solution.

[Zizi64]

Please upload a sample file here.

[Zizi64]

Just a note:
Not only the Cells can contain external links. A Copy/Pasted Conditional Format condition, a copy/pasted Named Cell or Named Range, and the Data Validity cells, and the Form Control elements can store external links too.

[mikekaganski]

@tarsajuma

But that link is 2 years and my issue is occurring for multiple files so I created a new post.
…
In this another post user sokol92 commented that one can search the *.ODS file as it is a zip archive.

Heh… the only comment there (indeed, from @sokol92) is:

@sokol92

First step: Menu / Edit / Links to External Files.
If it does not help, then please upload your problem file.

And you didn’t follow the advise to provide a sample problematic document
Note however, that it is not necessary that there will be any file: string in the references, because they might be relative ones, so instead of looking like file:///D:/Data/subdir/filename.xlxs, they can be stored as bare subdir/filename.xlxs in the XML streams inside the compressed ODS. These relative links would be transformed into absolute upon opening; and then converted into relative again, upon saving - based on Options → Load/Save → General, Save URLs relative to... settings. So your option could be to change that setting, save the file, and then search for file: string; or you might search for specific filename.xlxs instead.

[erAck]

Also note that this is about any external resources (and newer Calc says so), not only links to files. That could be http(s), web queries, formula expressions assembling URLs such that a search would not give any hits, and what not…

[tarsajuma]

tracking abc.ods (89.7 KB)

Thanks for the replies. Sorry about not including a sample file in the original post. After I composed the post, I realized the file contained potentially sensitive financial information. Uploaded version above has had the sensitive info removed and this version also shows the odd behavior:
At startup - Security Warning Automatic update of external links has been disabled
Edit → Links to External Files - finds a single link (see screen capture below)

Edit → Find and Replace - does not find any occurrences when searching for “file”, “file:///” or “JGEJ” in Formulas,Values or Comments.

[Zizi64]

There are broken references in the named ranges and expressions feature:

And here is the link too.

[tarsajuma]

The easiest way for me to remove the sensitive information from the file was to deleted quite a few cells that had identifying information such as bank names, etc. I did not bother to correct all the broken references.

But your Manage Names screenshot pointed me to a solution. The last item lists the named range “NoColor” with the same external link found in the Edit → Links to External Files, file:///D:/Data/Data/POP/… This is the culprit. I deleted the “NoColor” range and saved to a new filename. New filename does not show the warning on startup and Edit → Links to External Files is now grayed out indicating there are no longer external links in the file.

Thanks for the help.