vba 如何在Excel工作簿中找到链接的位置？

How to find location of link in Excel workbook?

提问by sigil

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

我有一本 Excel 书，打开时会发出警告：

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]'!addressI used this code:

我想删除所有这些链接，以便不会触发警告。认为任何外部链接都将采用'[workbook path]'!address我使用此代码的形式：

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?

我使用 Ctrl+F 在工作簿中搜索此字符串的所有公式，但没有返回任何结果。如何找到并删除此链接？

采纳答案by sigil

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

断开链接不足以抑制警告。在“编辑链接”窗口中，我单击“启动提示”并将单选按钮设置为“不显示警报且不更新自动链接”。这成功地阻止了警告的出现。

回答by Mark Fitzgerald

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.

检查您的命名范围以获取指向其他工作簿的链接。

回答by Tony H

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.

如上所述删除所有链接后，我遇到了这个问题，然后我发现我的一些验证数据引用了其他工作簿。改正后问题就消失了。

回答by bilbo_strikes_back

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

回答by labrys

Could be in one of these?:

可以在其中之一吗？：

• HYPERLINKS (formula and normal)
• NAME MANAGER
• VALIDATION
• CONDITIONAL FORMATTING
• BUTTON WITH MACRO (still pointing to originating macro)

• 超链接（公式和正常）
• 姓名经理
• 验证
• 条件格式
• 带宏的按钮（仍指向原始宏）