Break Workbook Links VBA

Breaking the links in an Excel workbook can be a cumbersome task.  The error message which appears when you open a workbook can be annoying especially if you do not wish your file to have any links.  Finding the links can often be difficult too.  A trick I usually use is to press Ctrl F (shortcut for Find) and look for the square bracket [.  This will appear in all linked cells.  Here is an example ='C:\Users\HYMC\Desktop\[XL File.xls]SaveFile'!$B$10

The above will find any links within a sheet. Change the Within: dropdown to Workbook to find the links in cells of the whole workbook. The following VBA procedure will do the same thing with the advantage of replacing all of the links at once with the hard coded text.

Option Explicit

Sub RemLink() 'Excel VBA remove links from workbook.
Dim ar As Variant
Dim i As Integer


On Error Resume Next 'Trap error

For i=1 To UBound(ar) 'Excel VBA loop throuh links
ActiveWorkbook.BreakLink ar(i), xlLinkTypeExcelLinks
Next i

On Error GoTo 0

End Sub

It becomes a little more difficult if the links are in the named ranges or the links are in charts. However the above is a good starting point to remove the links from a workbook.