0

My spreadsheet has external links that I cannot find. It pops up the "workbook contains links to other data sources" warning upon opening. I don't want to just suppress the link warning, I need to remove the links.

I've tried all the basic ways to find external links that I'm aware of, and it's still happening. I've tried:

  • Searching for "[" in formulas in the entire workbook
  • Charts
  • Checking the named ranges from the Formulas/Name Manager menu
  • Checking objects
  • Conditional formatting menus

Is there another way to find external links? Thanks.

ELW
  • 380
  • 2
  • 19

3 Answers3

1

It can come from several sources. In my case, it came from the formula of a rule in the conditionnnal formatting. And no Excel search tool could find it.

In the case of an xlsx file, you can find it with an automatical approach:

  1. In the Data tab, click on Edit links. All your links should be displayed. Mark down the values of the Location fields.
  2. Unzip the xlsx file. Technically, an xlsx file is a zip container. See this post for more information.
  3. Now search the whole directory for the Location strings.
  4. Figure out to what your links are related. In my case, it was inside a x14:conditionalFormatting xml node. No wonder the Search tool did not work, it was not in a cell.
  5. Modify that formula

Game over.

MrWorfFire
  • 11
  • 3
0

I would check the names collection in your workbook.

If you have a named range, for example, that has links to another workbook this will do this also... You can examine these in Excels Name Manager, or some VBA code executed in the debug window...like:

for i = 1 to names.Count: debug.Print Names(i): Next
PaulG
  • 1,051
  • 7
  • 9
  • Thanks Paul. It's not a named range. All of the named ranges in the name manager box are accounted for and internal to the workbook. The dubug.print code doesn't result in any external links. – ELW Jan 05 '16 at 16:52
  • 1
    Hhhmm hard to tell. I would try 1. removing a sheet at a time as saving as a temp file and open the temp file to try to narrow it down to a sheet... then 2. remove every named range - both scoped at the workbook and worksheet level - saving to the temp file and re-opening.. then 3. remove all the references in the VBIDE - Tools:References. This will sort of target in on the problem area... hopefully :).. After this -> any addins? maybe.. – PaulG Jan 06 '16 at 12:30
  • Removing one sheet at a time let me find it finally. It appears to have been in a list for data validation. Thanks for your help! – ELW Jan 13 '16 at 17:23
0

I had an Excel 2013 file that whenever opened displayed a message regarding a missing external link. I could not find such a link (and location in file) using many suggestions and tools (Kutools, FormulaDEsk etc.). Finally, I changed the file from xlsx to zip, opned and searched and deleted the gokder relating ton External Links (and changed back). Problem solved!

Danny Saville
  • 1