Searching invalid hyperlinks to files in spreadsheet

English
#1

There is a spreadsheet and when I open it , Calc gives me warning : “Automatic update of external links has been disabled.”

“List of External Links” lists 6 files. 2 of them are right and in use. The names of 4 others suggest they don’t even belong to the project. I suspect that the spreadsheet was created by using sheets from other projects for templates. Not actual spreadsheet, neither the other project were created by me.
.
I want to find those unused and invalid links in the document if they are there at all.
I’ve tryed to use search with “file” or part of their name, but I can’t locate those links, only the ones that are valid.
.
I’m thinking of that the bad links are stored among the spreadsheet properties ( if there is such a thing) and they don’t even need to exist indeed.
Or I’m not good enough to locate them.
.
How can I be 100% certain that those invalid links don’t hide somewhere in the spreadsheet?
.
Thanks!

#2

Do you looking Edit - Links to External Files?

#3

I did and now again. It helps to locate source files, but it doesn’t help to locate the links presence in the document.

#4

Could you upload a file with a similar error?

#5

OK I think it’s pointless to upload, but here you are. I’ve deleted the content of the sheets, though the links show up in “Edit - Links to External Files”

IMHO it’s a feature not an error. The help doesn’t really helped, but check out File->Properties->Custom Properties

However my real question not answered yet.

I’ve meant if any cell would contain one of those broken references.

#6

There are loads of named expressions / ranges (now broken in the uploaded sample), of which some (i.e. all Kert_…) contain references to external files. Hit Ctrl+F3 to inspect or Sheet → Named Ranges and Expressions → Manage. Also, change tracking is active (hence all the red bordered cells), see Edit → Track Changes → Manage, some old content has such references. (if deleting cell content was meant to redact the document it failed with change tracking being activated…)

1 Like
#7

Thanks, it’s sideway, but useful one. I wanted to delete the “live” content to see if the references to files still show up, but they do it because they are stored outside of the table of a sheet. Nothing sensitive left in it though.
.
My problem is to find a broken link or to find out there is none in sheets with 10k+ cells while showing “live” data through thousand of references with a number references across files.
.
I’ve tried to use search (Ctrl+F), but it seems to me that scope for search is one sheet only, not the document. And I’m not sure how to give the right expression to search that I’d convinced 100% that found all the references I wanted.

#8

To search in all sheets use the proper Find&Replace dialog (Ctrl+H) and enable All sheets. However, that still would not search in content of named expressions nor of conditional formats’ expressions. To be sure you’d have to inspect the content.xml stream of the document file (which is a zip container).

1 Like
#9

OK, Thanks!

That was I’m afraid of . Thanks anyway!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.