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

mlouie

New Member
Joined
Dec 1, 2016
Messages
3
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/
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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:
Code:
Sub revealNames()
Dim n As Name
For Each n In ActiveWorkbook.names
    n.Visible = True
Next n
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
here's a piece of code that will remove every conditional format and data validation from your active workbook
Code:
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

New Member
Joined
Dec 1, 2016
Messages
3
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

New Member
Joined
Dec 1, 2016
Messages
3
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,358
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top