Knowledge Base Articles By Category
This code quickly replaces all external links in an Excel workbook with their values. It is something that can be very handy when you need to send a heavily linked workbook outside of your immediate network, to a user who doesn't have access to the directory containing the linked files, of if you want to solidify values so that they don't change next time you open the file.
Note: If you also want to change the colour of the background cell (so that you can see where the link was,) as well as places the original link path in a comment, please see this article.
Where to place the code:
This code goes in a standard module.
Code required:
Code:
Sub BreakLinks() 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Break all links in the active workbook Dim vLinks As Variant Dim lLink As Long ' Define variable as an Excel link type. vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If vLinks = vbNullString Then Exit Sub ' Break all links in the active workbook. For lLink = LBound(vLinks) To UBound(vLinks) ActiveWorkbook.BreakLink _ Name:=vLinks(lLink), _ Type:=xlLinkTypeExcelLinks Next lLink End Sub
Recent Forum Posts
Rate this article