vba - 在 Excel 工作簿中找不到链接

Question

我编写了一个宏来打开多个受密码保护的工作簿。这些工作簿彼此之间都有链接，因此为了方便起见，我设置了 UpdateLinks:=0，这样在其他书籍更新之前，我就不会收到所有链接更新的密码提示。打开。

打开所有工作簿后，我尝试使用

更新链接

Workbooks("Workbook1").UpdateLink Type:=1
Workbooks("Workbook2").UpdateLink Type:=1
Workbooks("Workbook3").UpdateLink Type:=1
Workbooks("Workbook4").UpdateLink Type:=1

这更方便，因为工作簿现在已打开，因此不需要密码提示。

这在两个工作簿上运行良好，但另外两个工作簿提示我找到不存在的链接的来源。也就是说，工作簿中不存在实际的链接。

我花了几个小时试图弄清楚它是从哪里获取此链接的，但它根本不存在于任何地方..

为了更清楚地说明这一点，在工作簿 2 中，我有三个链接 A、B 和 C。这些链接在“数据”>“编辑链接”菜单中可见。但是，当我运行宏时，它要求我找到链接 E 的源...

我尝试了以下方法来查看是否存在由于某种原因不可见的链接

Workbooks("Workbook2").Activate

aLinks = ActiveWorkbook.LinkSources(1)
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        MsgBox "Link " & i & ":" & Chr(13) & aLinks(i) 
    Next i
End If

这只是显示了我在编辑链接中可以看到的三个。

我在工作簿中搜索了它试图让我找到文件的链接名称，但什么也没找到。

有人以前见过这个或者有什么想法吗？这让我难住了，让本来应该是简单的工作变得非常令人沮丧。

Answer 1

可以通过多种方式(有意或无意)创建工作簿之间的链接:

1. Within formulae 
2. Inside range names
3. Inside chart ranges

Excel 用户通常熟悉 (1)，并搜索引用链接的文本，但这不会检测图表和范围名称中的链接。

比尔·曼维尔的 findlink是查找和/或删除这些链接的出色解决方案。

下载插件，选择带有链接的文件，从 Excel 运行插件(Bill 页面上的说明)，然后

• 在下拉框中选择您要查找的引用
• 我选择找到并列出链接的选项

各种链接类型的示例

示例输出

几年前，我尝试编写自己的链接查找器，下面的代码以防万一它有用

代码

Option Explicit

' This code searches all sheets (worksheets and chart sheets) in the ActiveWorkbook for links
' and compiles a filtered CSV file to report on any:
' #1 Formula links (and validates them against linksources)
' #2 Range Name links
' #3 PivotTable links
' #4a Chart Series links (in both Chart Sheets and Charts on regular Worksheets)
' #4b Chart Title links (in both Chart Sheets and Charts on regular Worksheets)

' Download Bill Manville's FindLink at http://www.bmsltd.co.uk/MVP/Default.htm
' for a tool to manage (ie delete) links

' Notes
' 1) The Chart title method relies on activating the Chart.
'         ---> Protected sheets are skipped
'         ---> This method does not work in xl2007
' 2) I have deliberately left out error handling as I want to resolve any issues

Sub ListLinks()
    Dim objFSO As Object, objFSOfile As Object
    Dim wb As Workbook, sh
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rArea As Range
    Dim chr As ChartObject, chr1 As Chart
    Dim lSource, PivCh, chrSrs
    Dim FSOFileHeader As String, tmpStr As String, chrTitle As String, FirstAddress As String, ReportFile As String, ShProt As String
    Dim nameCnt As Long
    Dim FndRngLink As Boolean, FndChrLink As Boolean, FndNameLink As Boolean, FndPivLink As Boolean

    Application.ScreenUpdating = False
    'location of report file
    ReportFile = "c:\LinkReport.csv"
    FSOFileHeader = "Type,Object Level,Location,Linked Workbook,Full Linked File Path,Reference"

    Set objFSO = CreateObject("scripting.filesystemobject")
    On Error Resume Next
    'if report file is open then ask user to close it
    Set objFSOfile = objFSO.createtextfile(ReportFile)
    If Err.Number <> 0 Then
        MsgBox "Pls close " & vbNewLine & ReportFile & vbNewLine & "then re-run code"
        Exit Sub
    End If
    On Error GoTo 0

    'write report file headers
    With objFSOfile
        .writeline ActiveWorkbook.Path & "," & ActiveWorkbook.Name
        .writeline FSOFileHeader
    End With

    For Each sh In ActiveWorkbook.Sheets

        Select Case sh.Type
        Case xlWorksheet
            'look at formula cells in each worksheet
            Set rng1 = Nothing
            Set rng2 = Nothing
            Set rng3 = Nothing

            On Error Resume Next
            Set rng1 = sh.Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            Application.StatusBar = "Searching formulas in sheet " & sh.Name
            If Not rng1 Is Nothing Then
                'look for *.xls
                With rng1
                    Set rng2 = .Find("*.xls", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)
                    If Not rng2 Is Nothing Then
                        FirstAddress = rng2.Address
                        'validate that the *.xls is part of a linksource
                        For Each lSource In ActiveWorkbook.LinkSources
                            'look in open and closed workbooks
                            If InStr(Replace(rng2.Formula, "[", vbNullString), lSource) > 0 Or InStr(rng2.Formula, Right$(rng2.Formula, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                FndRngLink = True
                                'write to the report file
                                Set rng3 = rng2
                                Exit For
                            End If
                        Next
                        'repeat till code loops back to first formula cell containing "*.xls"
                        Do
                            Set rng2 = .FindNext(rng2)
                            If rng2.Address <> FirstAddress Then
                                For Each lSource In ActiveWorkbook.LinkSources
                                    If InStr(Replace(rng2.Formula, "[", vbNullString), lSource) > 0 Or InStr(rng2.Formula, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                        Set rng3 = Union(rng3, rng2)
                                        Exit For
                                    End If
                                Next
                            End If
                        Loop Until rng2.Address = FirstAddress
                    End If
                End With
            End If

            If Not rng3 Is Nothing Then
                For Each rArea In rng3.Areas
                    objFSOfile.writeline "Formula," & "Range" & "," & sh.Name & "!" & Replace(rArea.Address(0, 0), ",", ";") & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & rng3.Cells(1).Formula
                Next
            End If

            ' Charts
            For Each chr In sh.ChartObjects
                Application.StatusBar = "Searching charts in sheet " & sh.Name
                For Each chrSrs In chr.Chart.SeriesCollection
                    If InStr(chrSrs.Formula, ".xls") <> 0 Then
                        For Each lSource In ActiveWorkbook.LinkSources
                            'look in open and closed workbooks
                            If InStr(Replace(chrSrs.Formula, "[", vbNullString), lSource) > 0 Or InStr(chrSrs.Formula, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                FndChrLink = True
                                'write to the report file
                                objFSOfile.writeline "Chart Series," & chr.Name & "," & sh.Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & Replace(chrSrs.Formula, ",", ";")
                                Exit For
                            End If
                        Next
                    End If
                Next chrSrs

                If chr.Chart.HasTitle Then
                    If sh.ProtectContents = True Then
                        ShProt = ShProt & sh.Name & " - " & chr.Name & vbNewLine
                    Else
                        chr.Activate
                        chrTitle = CStr(ExecuteExcel4Macro("GET.FORMULA(""Title"")"))
                        If InStr(chrTitle, ".xls") <> 0 Then
                            For Each lSource In ActiveWorkbook.LinkSources
                                'look in open and closed workbooks
                                If InStr(Replace(chrTitle, "[", vbNullString), lSource) > 0 Or InStr(chrTitle, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                    FndChrLink = True
                                    'write to the report file
                                    objFSOfile.writeline "Chart Title," & chr.Name & "," & sh.Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & chrTitle
                                    Exit For
                                End If
                            Next
                        End If
                    End If
                End If

            Next chr

            'Pivot Tables
            For Each PivCh In sh.PivotTables
                If InStr(PivCh.SourceData, ".xls") > 0 Then
                    For Each lSource In ActiveWorkbook.LinkSources
                        If InStr(Replace(PivCh.SourceData, "[", vbNullString), lSource) > 0 Or InStr(PivCh.SourceData, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                            objFSOfile.writeline "Pivot Table," & PivCh.Name & "," & sh.Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & PivCh.SourceData
                            FndPivLink = True
                            Exit For
                        End If
                    Next
                End If
            Next
        Case 3
            Set chr1 = Nothing
            On Error Resume Next
            Set chr1 = sh
            On Error GoTo 0
            If Not chr1 Is Nothing Then
                Application.StatusBar = "Searching charts in sheet " & sh.Name
                For Each chrSrs In chr1.SeriesCollection
                    If InStr(chrSrs.Formula, ".xls") <> 0 Then
                        For Each lSource In ActiveWorkbook.LinkSources
                            'look in open and closed workbooks
                            If InStr(Replace(chrSrs.Formula, "[", vbNullString), lSource) > 0 Or InStr(chrSrs.Formula, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                FndChrLink = True
                                'write to the report file
                                objFSOfile.writeline "Chart Series,Chart Sheet," & sh.Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & Replace(chrSrs.Formula, ",", ";")
                                Exit For
                            End If
                        Next
                    End If
                Next

                If chr1.HasTitle Then
                    chr1.Activate
                    chrTitle = CStr(ExecuteExcel4Macro("GET.FORMULA(""Title"")"))
                    If InStr(chrTitle, ".xls") <> 0 Then
                        For Each lSource In ActiveWorkbook.LinkSources
                            'look in open and closed workbooks
                            If InStr(Replace(chrTitle, "[", vbNullString), lSource) > 0 Or InStr(chrTitle, Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                                FndChrLink = True
                                'write to the report file
                                objFSOfile.writeline "Chart Title,Chart Sheet," & sh.Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & Replace(chrTitle, ",", ";")
                                Exit For
                            End If
                        Next
                    End If
                End If
            End If
        Case Else
        End Select
        'End If
    Next sh

    'Named ranges
    If ActiveWorkbook.Names.Count = 0 Then
    Else
        Application.StatusBar = "Searching range names"
        For nameCnt = 1 To ActiveWorkbook.Names.Count
            If InStr(ActiveWorkbook.Names(nameCnt), ".xls") <> 0 Then
                For Each lSource In ActiveWorkbook.LinkSources
                    If InStr(Replace(ActiveWorkbook.Names(nameCnt), "[", vbNullString), lSource) > 0 Or InStr(ActiveWorkbook.Names(nameCnt), Right$(lSource, Len(lSource) - InStrRev(lSource, "\"))) > 0 Then
                        FndNameLink = True
                        'write to the report file
                        objFSOfile.writeline "Range Name," & "Workbook level," & ActiveWorkbook.Names(nameCnt).Name & "," & Right$(lSource, Len(lSource) - InStrRev(lSource, "\")) & "," & lSource & ",'" & ActiveWorkbook.Names(nameCnt).RefersTo
                        Exit For
                    End If
                Next
                'Name link does not exist in "known" links
                If FndNameLink = False Then
                    FndNameLink = True
                    objFSOfile.writeline "Range Name," & "Workbook level," & ActiveWorkbook.Names(nameCnt).Name & "," & ActiveWorkbook.Names(nameCnt) & ",'" & Replace(ActiveWorkbook.Names(nameCnt).RefersTo, ",", ";")
                End If
            End If
        Next nameCnt
    End If

    'Close the report file
    objFSOfile.Close
    Set objFSO = Nothing

    'If at least one cell link was found then open report file
    If (FndChrLink = FndNameLink = FndRngLink = FndPivLink) And FndRngLink = False Then
        MsgBox "No formula links found", vbCritical
    Else
        Set wb = Workbooks.Open(ReportFile)
        With wb.Sheets(1)
            .Rows("1:2").Font.Bold = True
            .Columns("A:F").AutoFit
            .[A2].AutoFilter
        End With
    End If
    With Application
        .StatusBar = vbNullString
        .DisplayAlerts = True
    End With
    If ShProt <> vbNullString Then MsgBox "The following sheets were protected " & vbNewLine & "so these Chart titles could not be searched" & vbNewLine & ShProt, vbCritical
End Sub