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

ar=ActiveWorkbook.LinkSources(1)

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

Break Data Validation Links

It becomes a little more difficult if the links are in the named ranges or the links are in charts or even harder in conditional formating or in data validation lists. However, the above is a good starting point to remove the links from a workbook.<br> <br> We may wish to break external links which are hidden in data validation lists. This is a tricky problem to face as it is not at first apparent that this could conceivably happen. However if you can’t find links either in cells, in chart, in named ranges then the pesky link may be in a hard to find data validation list. Here is the code to deal with that problem. It will make a list of all of the links on a new sheet then you simply have to go and eradicate the data validation links.

Sub ValidExtLinks() 'Excel VBA remove validation links from workbook.
Dim r As Range
Dim str As String
Dim sh As Worksheet
Dim ws As Worksheet

Set ws = ActiveSheet
Set sh = Sheets.Add
sh.Name = "ExternalLK"

For Each r In ws.UsedRange.Cells
On Error Resume Next
str = ""
str = r.Validation.Formula1
On Error GoTo 0

If InStr(1, str, "]") > 0 Then
sh.[A1048576].End(xlUp)(2) = r.Address & " " & r.Validation.Formula1
End If
Next r

End Sub

It is best to make a habit of steering away from Excel links altogether. Make your workbooks stand alone with set processes in place for importing summaries of data into your workbook. I have managed to get through my entire career without linking my spreadsheets to other workbooks. If I have to look for data in another workbook I break that link immediately so I don’t forget later.