CODEWITHSUNDEEP
vbaexcel
sigil
sigil

Reputation: 9546

How to find location of link in Excel workbook?

I have an Excel book that, when opened, gives the warning:

This workbook contains links to other data sources.

I want to remove all of these links so that the warning will not be triggered. Thinking that any external link will be of the form '[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 returned a file path:

\\somePath\xyz\aWorkbook.xlsm

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

Upvotes: 3

Views: 10221

Answers (5)

labrys
labrys

Reputation: 149

Could be in one of these?:

Upvotes: 3

Tony H
Tony H

Reputation: 11

I had this problem after removing all the links as described above, I then found that some of my validation data was refering to other workbooks. After correcting this the problem disappeared.

Upvotes: 1

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

Your workbook could be linking via a named range pointing to another workbook. A search of formulas for the linked workbook may find nothing because the link is hidden in the name.

Check your named ranges for links to other workbooks.

Upvotes: 3

sigil
sigil

Reputation: 9546

Breaking the links is not enough to suppress the warning. On the Edit Links window, I clicked Startup Prompt and set the radio button to "Don't display the alert and don't update automatic links". This successfully prevented the warning from appearing.

Upvotes: 2

bilbo_strikes_back
bilbo_strikes_back

Reputation: 591

The following loop 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

Upvotes: 1

Related Questions