Clever Geek Handbook

How to find the location of a link in an Excel workbook?

I have an Excel workbook that, when opened, gives a warning:

This workbook contains links to other data sources.

I want to remove all these links so that the alert does not fire. I think any external link will look like '[workbook path]'!address

I used this code:

Sub ListLinks()
Dim wb As Workbook
Dim link As Variant
Set wb = ThisWorkbook
For Each link In wb.LinkSources(xlExcelLinks)
    Debug.Print link
Next link

End Sub

This returns the path to the file:

\\somePath\xyz\aWorkbook.xlsm

I searched all the formulas in the book for this line using Ctrl + F, but no results were returned. How do I find and remove this link?

+3


source to share


4 answers


Breaking links isn't enough to suppress the warning. In the Edit Links window, I clicked Start on Load and set the radio button to "Do not display warning and do not update automatic links." This successfully prevented the warning from appearing.



+2


source


Your book can be linked through a named range pointing to another book. Finding formulas for a linked workbook cannot find anything because the link is hidden in the name.



Check your named ranges for links to other books.

+3


source


The next cycle should work.

Dim intCounter As Integer
Dim varLink As Variant

'Define variable as an Excel link type.
varLink = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'Are there any links?
If IsArray(varLink) = False Then
    Exit Sub
End If

'Break the links in the active workbook.
For intCounter = 1 To UBound(varLink)
    ActiveWorkbook.BreakLink _
    Name:=varLink(intCounter), _
    Type:=xlLinkTypeExcelLinks
Next intCounter
+1


source


I got this problem after deleting all the links as described above, after which I found that some of my validation data links to other books. After the fix, the problem went away.

+1


source






More articles:


All Articles