Zellen in Excel mit externer Verknüpfung finden

Suchen nach Links (externen Bezügen) in einer Arbeitsmappe

Wenn beim Öffnen die Meldung kommt "Diese Datei enthält Verknüpfungen zu anderen Datenquellen" git es innerhalb von Excel einen/mehrere Verweise auf externe Quellen.
Speziell wenn eine Tabellen-Blatt kopiert wurde und in dieser Verknüfungen hinterlegt waren.

Formeln / Verknüfungen in Zellen
Der erste Schritt ist immer per STRG-R nach den Zeichen "[" bzw. "'[" zu suchen. Dann kann der Verweis auf die externe Datei z.B. [externe Datei.xlsx] mit '' (entfernen) ersetzt werden. Meistens löst dieses löschen das Problem.

Formeln / Verknüfungen in der Datenüberprüfung
Wenn jetzt beim nächsten öffnen immer noch der Hinweis kommt liegt das vermutlich an einer Datenüberprüfung. Wenn z.B. eine Auswahlliste für Felder erstellt wurden. Um einen schnellen Überbkick zu bekommen kann via "Daten > Bereich Datentools > Datenüberprüfung > Ungültige Daten einkreisen" einen Hinweis liefern.

Sollte das alles nicht Helfen muss per VBA danach gesucht werden.
Einfach den VBA-Editor öffen ( ALT-F11) und folgenden Code dort in ein neues Modul einfügen (Alt-E Alt-M).
Vorab den Direktbereich anzeigen lassen (STRG-D). Da dort das Ergebnis ausgegeben wird.
Cursor in die Sub setzen und per F5 die Sub starten. Wird jetzt eine externe Verknüpfung gefunden wird die betroffenen Tabelle samt Felder ausgedruckt.

 

Sub find_external_links()
  Dim oWB As Workbook, oWSs As Worksheets, oWS As Worksheet, oCell As Object, oO As Object
  Dim i%, j%, r%, c%, rMax%, cMax%, sValidationExternalLink$, sExternalLink$, sFormatConditions$, sNames$, s$
  
  
  ' Zelle finden in denen ein Link auf eine externe Excel-Datei besteht.
  Const cfExternalLink = True
  
  ' Externe Links in einer Datenüberprüfung finden
  Const cfValidationExternalLink = True
  ' Wenn etwas gefunden wird den Befehl "Ungültige Daten einkreisen" ausführen.
  ' Es werden aber nicht immer alle fehlerhaften Felder markiert. Die Formel =#BEZUG! wird nicht markiert.
  ' Markierung wieder löschen: Register Daten > Bereich Datentools > Datenüberprüfung > Gültigkeitskreise löschen
  Const cfHiglightFields = True
  
  
  ' Bedinge Formatierungen einer Zelle auf externen Link testen
  Const cfFormatConditions = True
  
  ' Namen (Feldbezeichnung) im Names-Manager
  Const cfNames = True
  
  Application.ScreenUpdating = False
  Application.Visible = False
  
  
  On Error Resume Next
  Set oWB = ActiveWorkbook
  For Each oWS In oWB.Worksheets
    With oWS
      .Select
      ' Die Zelle markieren welche den äußersten Rand des Zellbereichs markiert mit veränderten Zellen
      ActiveCell.SpecialCells(xlLastCell).Select
      
      rMax = ActiveCell.Row
      cMax = ActiveCell.Column
       
      For r = 1 To rMax
        For c = 1 To cMax
          Set oCell = oWS.Cells(r, c)
          With oCell
            
            If cfExternalLink Then
              s = .Formula
              If s <> vbNullString Then
                If InStr(1, s, "[", vbTextCompare) Then
                  sExternalLink = sExternalLink & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
                End If
              End If
            End If
            
            If cfValidationExternalLink Then
              s = vbNullString
              s = .Validation.Formula1
              If s <> vbNullString Then
                If InStr(1, s, "[", vbTextCompare) Then
                  sValidationExternalLink = sValidationExternalLink & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
                End If
              End If
              s = vbNullString
              s = .Validation.Formula2
              If s <> vbNullString Then
                If cfValidationExternalLink Then
                  If InStr(1, s, "[", vbTextCompare) Then
                    sValidationExternalLink = sValidationExternalLink & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
                  End If
                End If
              End If
            End If
            
            If cfFormatConditions Then
              j = .FormatConditions.Count
              For i = 1 To j
                Set oO = .FormatConditions.Item(i)
                s = vbNullString
                s = oO.Formula1
                If InStr(1, s, "[", vbTextCompare) Then
                  sFormatConditions = sFormatConditions & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
                  Exit For
                End If
                s = vbNullString
                s = oO.Formula2
                If InStr(1, s, "[", vbTextCompare) Then
                  sFormatConditions = sFormatConditions & Replace(.Address(True, False), "$", vbNullString, , 1, vbTextCompare) & "|"
                  Exit For
                End If
              Next
            End If
            
          End With
        Next
        
      Next
      
      If sExternalLink <> vbNullString Then
        Debug.Print vbCrLf & "Externe Links in folgenden Feldern der Tabelle '" & oWS.Name & "' gefunden."
        Debug.Print Left(sExternalLink, Len(sExternalLink) - 1)
        sExternalLink = vbNullString
      End If
      
      If sValidationExternalLink <> vbNullString Then
        Debug.Print vbCrLf & "Externe Links in der Datenüberprüfung folgender Zellen der Tabelle '" & oWS.Name & "' gefunden. " & IIf(cfHiglightFields, "Zellen wurden rot umkreist!", vbNullString)
        Debug.Print Left(sValidationExternalLink, Len(sValidationExternalLink) - 1)
        sValidationExternalLink = vbNullString
        If cfHiglightFields Then ActiveSheet.CircleInvalid
      End If
      
      If sFormatConditions <> vbNullString Then
        Debug.Print vbCrLf & "Externe Links in der bedingten Formatierung folgender Zellen der Tabelle '" & oWS.Name & "' gefunden. " & IIf(cfHiglightFields, "Zellen wurden rot umkreist!", vbNullString)
        Debug.Print Left(sFormatConditions, Len(sFormatConditions) - 1)
        sFormatConditions = vbNullString
      End If
            
    End With
  Next
    
  If cfNames Then
    With oWB
      j = .Names.Count
      For i = 1 To j
        Set oO = .Names.Item(i)
        s = vbNullString
        s = oO.Value
        If InStr(1, s, "[", vbTextCompare) Then
          sNames = sNames & oO.Name & "|"
        End If
      Next
    End With
      
    If sNames <> vbNullString Then
      Debug.Print vbCrLf & "Externe Links in benannte Zellen / -Zellbreiche gefunden (Names-Manager)"
       Debug.Print Left(sNames, Len(sNames) - 1)
    End If
  End If
  
  
  Set oCell = Nothing
  Set oWB = Nothing
  Application.Visible = True
  Application.ScreenUpdating = True
End Sub

 

Hauptmenü

Anmeldeformular