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.
Sub RemLink() 'Excel VBA remove links from workbook.
Dim ar As Variant
Dim i As Integer
On Error Resume Next 'Trap error
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.
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"
str = r.Validation.Formula1
If InStr(1, str, "]") > 0 Then