Breaking external Workbook links (Which cannot be broken via Edit Links)

%3CLINGO-SUB%20id%3D%22lingo-sub-2812619%22%20slang%3D%22en-US%22%3EBreaking%20external%20Workbook%20links%20(Which%20cannot%20be%20broken%20via%20Edit%20Links)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812619%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20someone%20know%20how%20to%20break%20external%20Excel%20Workbook%20Links%2C%20which%20are%20shown%20in%20Edit%20Link%20but%20do%20not%20get%20broken.%3C%2FP%3E%3CP%3ENamed%20ranges%20are%20already%20removed.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EEric%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2812619%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2812665%22%20slang%3D%22en-US%22%3ERe%3A%20Breaking%20external%20Workbook%20links%20(Which%20cannot%20be%20broken%20via%20Edit%20Links)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175516%22%20target%3D%22_blank%22%3E%40Eric_Weij%3C%2FA%3E%26nbsp%3BTry%20the%20tool%20in%20the%20link%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

Does someone know how to break external Excel Workbook Links, which are shown in Edit Link but do not get broken.

Named ranges are already removed.

Thanks

Eric

3 Replies

@Riny_van_EekelenThanks Riny, unfortunately this macro cannot be activated in our environment.

 

@Eric_Weij, I had the same (very annoying) problem a little while ago and came up with the following checks and fixes. It sounds like you've tried a few of them, but I've left everything in just in case they are helpful to others. I hope it helps.

Break the link
• Goto Data/Edit Links
• Make a note of the external Source(s)
• Try Break Link (never known to work!)

Search for links
• Press CTRL-F to open the Find facility
• Under Find What, enter some or all of the link text you noted in the Break the Link section
• Click Options
• Set Within to Workbook
• Click Find All
• Edit anything that is found

Edit Defined Names
• Goto Formula/Name Manager
• Delete names that refer to external sources or show errors
• Use the filter facility to double check
• This will tidy things up but will probably not solve the problem. Names can be Hidden (by VBA) and Name Manager does not let you see them.
• Use a 3rd party macro tool / add-in to find hidden names. If your system lets you, try FindLink.xlam from http://www.manville.org.uk/software/findlink.htm.

Check Conditional Formatting
• Use Home/Find&Select/GotoSpecial/Conditional Formatting (or press F5 and click Special) to find cells with conditional formatting.
• Manage Rules to see if a formatting rule contains an external link.

Check Data Validation
• Use Home/Find&Select/GotoSpecial/Data Validation (or press F5 and click Special) to find cells with Data Validation.
• Check cells with drop down lists to see if the list is defined externally
• Can also use Data/Data Validation/Circle Invalid Data to find problems (although these will probably not be external links)

Check Charts
• Check each data series on each chart for external data sources
• Check chart title and axis titles for external references
• Check embedded labels and text boxes
• Check for hidden data series

Check Objects
• Use Home/Find&Select/GotoSpecial/Objects (or press F5 and click Special) to find embedded objects (text boxes, shapes etc).
• Check that the text in each object is not externally linked

Check Pivot Tables
• Select a cell within your Pivot Table
• Navigate to the PivotTable Tools Analyze Tab
• Click the Change Data Source button
• Look inside the Change PivotTable Data Source dialog box and confirm your data is not linked externally

Check VBA
• Press Alt-F11 to open Visual Basic
• Check for macros that create (very hidden) Names