Whenever we implement formula in excel cells which contains reference to external workbook, excel create a link to refer external workbook. So, it requires all external linked excel workbooks to calculate formula whenever someone open the excel workbook with formula.
Sometimes these external workbooks may be moved or removed from the specified folder. As a result, excel ask for the linked excel workbook which is missing from linked path. this will impact on formula calculation and result into #NA values. Due to large number of workbooks that have links in them and they are getting very large.
Here requires a way for Excel to convert the links to the data grabbed from those links.Use below macro which will help you to break large number of links in your workbook,
Sub Break_All_Links() ' Author: Dreams24 ' Written for VBA Tricks and tips blog 'https://www.vbatricksntips.com Dim bLinks As Variant bLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) ' Break all links in the active workbook If Not IsEmpty(bLinks) Then For lLink = LBound(bLinks) To UBound(bLinks) ActiveWorkbook.BreakLink _ Name:=bLinks(lLink), _ Type:=xlLinkTypeExcelLinks Next lLink End If MsgBox "All Links are breaked.", vbInformation End Sub