How to find and remove active content links in excel

Question

When I open my excel file (using Office 365) I get this security message:

SECURITY WARNING: Some active content has been disabled. Click for more details. [Enable Content]

When I click [Enable Content], excel proceeds to try and access files 1) linked files available on my network but have been moved and 2) MS 365 login dialog box opens and requests: Sign In. Tell us the account you want to use to open [Charts] - random name of file to illustrate my problem.

So my problem is #2 above: how do I find where [Charts] and remove the [Charts] link is because its not in Data > Edit Links, Data > Queries & Connections, Formulas > Name Manager, Control G > Object search, Control F search for [Charts] across the workbook; and its not an external link in a chart, pivot table, or macro. With regards to the File > Options > Trust Centre > Active X Settings, I can change the Active X settings to "disable all control without notification" but that does not solve my problem of finding and removing the [Charts} link. Also the Data > Edit Links > Startup Prompt does not apply because [Charts] does not appear as a link. My file has some minor macros but I have gone through every line of the code and it does not have links or references to [Charts].

I have followed most of the steps on this page. I stopped at Dynamic Arrays

https://www.ablebits.com/office-addins-blog/2021/01/27/find-break-external-links-excel/

This all started because I was copying data or importing tabs from other excel spreadsheets into my spreadsheet. So I am now trying to clean up my file to get rid of these hidden Active Content links

So back to my original question: how do I find and remove Active Content links given all of the steps I have take above?

EDIT: I have existing links in the file that must remain intact. The links identified in Data > Edit Links are all valid.

juliano-petrukio · Answer

Hello380&nbsp;Sub BreakLinks()
    Dim wb As Workbook
    Set wb = Application.ActiveWorkbook
    If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
        For Each link In wb.LinkSources(xlExcelLinks)
            wb.BreakLink link, xlLinkTypeExcelLinks
        Next link
    End If
End Sub

hello380 · Answer

Juliano-Petrukio&nbsp;&nbsp;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 &gt; Edit Links] in the file that are valid and must remain intact.

riny_van_eekelen · Answer

Hello380&nbsp;Try the tool in the below link:https://manville.org.uk/software/findlink.htm&nbsp;

hello380 · Answer

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 NameFor Each n In ActiveWorkbook.Namesn.Visible = TrueNext nEnd Sub

hello380 · Answer

Just 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 Applicationshttps://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%21. 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 aboveTo paste a list of defined names to a location in the current workbook, do the following:Select the cell for the paste targetOn the ribbon use the sequence Formulas &gt; Defined Names &gt; Use in Formula &gt; Paste Names (the last item), or simply press the F3 short cutIn the Paste Names dialog box, click the Paste List item. The list is now the selection3. 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 casehttps://manville.org.uk/software/findlink.htm4. 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.&nbsp;&nbsp;Other useful website links:https://wmfexcel.com/2018/04/12/hide-and-seek-external-links-in-excel/https://marqueegroup.ca/resource/removing-phantom-links/

Forum Discussion

How to find and remove active content links in excel

5 Replies

Resources