Cannot remove external links in Excel, no add-ins tab

[mlouie]

We have an Excel spreadsheet with links to external spreadsheets. These links all need to be removed so that the spreadsheet can be edited in Excel Online in SharePoint by multiple people simultaneously. SharePoint does not support external links in files so it will not allow online editing of this file while the links exist. I am unable to find and remove all the links. I did a search on "[" and that only found one of the links, so removing it did not solve the issue. I then followed the instructions in this article from Microsoft to break links and it removed one link but when I click "break link" it will not remove the other link that it finds: https://support.office.com/en-us/ar...n-Excel-f1ca8b08-4f24-4af6-92e5-f4fdb1442748I. then found this add-in that will find and delete links but I cannot run this add-in because the Add-Ins tab will not show up in my Excel 2013 no matter what I do: http://www.manville.org.uk/software/findlink.htmI have spent half an hour researching on the web and trying stuff to make the Add-ins tab show up in Excel, to no avail. How can I get these links to external files out of my spreadsheet? I did not create the spreadsheet and it has many, many tabs, so it is not practical to go manually looking thru it.


I have basically done everything listed here except I can't run the Find Link Add-In because the Add-Ins tab will not appear. The Name Manager does not find any names:
http://blog.contextures.com/archives/2013/11/12/find-external-links-in-an-excel-file/

 

[baitmaster]

welcome to the board

don't know if it will help, but you often find links hidden away either within objects - charts, images etc - or within data validation. You may also have some hidden names that you've not seen yet

On any worksheet, you can find all data validation using F5 (goto) > Special > Data Validation. You could set up a VBA loop to go through every worksheet and do this, as well as remove any objects, although it might start deleting stuff you don't want deleted

Here's some code for unhiding all names. Run this then check your name manager again:

Sub revealNames()
Dim n As Name
For Each n In ActiveWorkbook.names
    n.Visible = True
Next n
End Sub

 

[RoryA]

I have basically done everything listed here except I can't run the Find Link Add-In because the Add-Ins tab will not appear. The Name Manager does not find any names:
http://blog.contextures.com/archives/2013/11/12/find-external-links-in-an-excel-file/

Try this:

In Windows Explorer, locate the FindLink.xla file that you downloaded
Right-click it and choose Properties
You may see either an Unblock button or Checkbox. If you do, press/check the option and then Apply and OK out.
Now try loading the add-in in Excel.

 

[baitmaster]

here's a piece of code that will remove every conditional format and data validation from your active workbook

Sub removeCFandDV()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.FormatConditions.Delete
    ws.Cells.Validation.Delete
Next ws
End Sub

 

[mlouie]

welcome to the board

don't know if it will help, but you often find links hidden away either within objects - charts, images etc - or within data validation. You may also have some hidden names that you've not seen yet

On any worksheet, you can find all data validation using F5 (goto) > Special > Data Validation. You could set up a VBA loop to go through every worksheet and do this, as well as remove any objects, although it might start deleting stuff you don't want deleted

[/CODE]

Thank you, it turned out there was a link within data validation. Once I removed that, all the links are gone. Unfortunately, it still can't be edited in SharePoint, so there is something else in the spreadsheet besides external links that SharePoint doesn't like, but I don't know what.

 

[mlouie]

Thank you, that was the problem. It was blocked. I unblocked it and now the Add-ins tab shows up in Excel. I've never used add-ins in Excel before (I'm an IT person, not an Excel expert).