Forum Discussion

Hello380's avatar
Hello380
Copper Contributor
Sep 29, 2021

How to find and remove active content links in excel

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 Con...
excel
Juliano-Petrukio's avatar
Juliano-Petrukio
Bronze Contributor
Sep 29, 2021

Hello380 

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's avatar
    Hello380
    Copper Contributor
    Sep 29, 2021

    Juliano-Petrukio 

     

    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's avatar
        Hello380
        Copper Contributor
        Sep 30, 2021

        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 Sub

Resources