Forum Discussion
How to find and remove active content links in excel
Thank you for your reply but that unfortunately is overkill as it breaks all links. I need to selectively find the Active Control [Charts] link, review that its not critical, then break it if I deem it to be useless. I have whole bunch of other links [Data > Edit Links] in the file that are valid and must remain intact.
Hello380 Try the tool in the below link:
A colleague thought there might some hidden names and sent me over a VBA script to unhide names. After running the macro, I deleted hundreds of broken, unused names that were originally hidden. Unfortunately, it only solved a 1/3 of the spreadsheet's Active Content links so I am still looking other ideas on where they might be hidden.
Sub ShowAllNames()
Dim n As Name
For Each n In ActiveWorkbook.Names
n.Visible = True
Next n
End SubJust a final follow-up note for anyone that comes searching for a solution to find and break hidden excel links based upon my research:
0. Depending on the level of severity of the number of hidden links in your file, I wouldn't "Enable All Macros" unless you were at wits end. In my situation, when I Enabled All Macros, it revealed several linked files that I was completely unaware of. There are other settings in the trust center that may be worth playing with including ActiveX Settings for All Office Applications
https://webhelp.cloud.taxsystems.com/alphabridge/download_template/How_to_enable_Trust_Center_macro_setting.htm#:~:text=%20Use%20the%20following%20instructions%20to%20change%20this,object%20model%2
1. Follow the steps in this link (including the VBA steps)
https://www.ablebits.com/office-addins-blog/2021/01/27/find-break-external-links-excel/
2. Next run the ShowAllNames VBA script from the comment above
To paste a list of defined names to a location in the current workbook, do the following:
Select the cell for the paste target
On the ribbon use the sequence Formulas > Defined Names > Use in Formula > Paste Names (the last item), or simply press the F3 short cut
In the Paste Names dialog box, click the Paste List item. The list is now the selection
3. Running Manville's findlink excel utility is useful but it does not get 100% of the hidden links. It's the combination of all these steps that cleans up most of the hidden links. In addition, run the following searches (wildcards are accepted): .xl*, #REF, #N/A - the searches did find some interesting things in my case
https://manville.org.uk/software/findlink.htm
4. As a last step, use Juliano-Petrukio BreakLinks VBA script in the first comment. This script should be used as a last resort if you cannot find any of the remaining links as it does not show you where the remaining links are hiding.
Other useful website links:
https://wmfexcel.com/2018/04/12/hide-and-seek-external-links-in-excel/
https://marqueegroup.ca/resource/removing-phantom-links/