Die Suche nach der externen Verknüpfung 14

Sie können einen in den Wahnsinn treiben: Externe Verknüpfungen in Excel. Hier zeige ich, wo sie sich verstecken können.
 

Ist dir das auch schon mal passiert: Beim Öffnen einer Excel-Datei kommt der Hinweis “Diese Arbeitsmappe enthält Verknüpfungen zu mindestens einer externen Quelle…”
Und das, obwohl in der Datei eigentlich überhaupt keine Verknüpfungen enthalten sein sollten.

Wenn du diese Situation kennst, dann weißt du wahrscheinlich auch, dass man daran fast verzweifeln kann. Wie wird man dieses Problem also los? Oder anders gefragt: Wo genau befinden sich diese kleinen Teufel eigentlich?

Eine nicht immer ganz so einfach zu lösende Aufgabe. Dieser Artikel liefert ein paar Antworten dazu.

Aller Anfang ist leicht

Ausgangspunkt der Suche ist immer die Registerkarte “Daten”. Wenn die aktuelle Arbeitsmappe externe Verknüpfungen enthält, ist hier die Schaltfläche “Verknüpfungen bearbeiten” aktiviert.

Verknüfungen bearbeiten

Verknüpfungen bearbeiten

Erst wenn es keine Verknüpfungen mehr gibt, wird dieser Punkt ausgegraut dargestellt und man ist sozusagen “sauber”. Ein Klick auf die Schaltfläche öffnet ein Fenster, in dem sämtliche Verknüpfungsquellen aufgelistet sind. Markiert man einen Eintrag in der Liste, so wird unten der Speicherort angezeigt. Leider ist in vielen Fällen der Pfad so lang, dass man nur einen Teil davon erkennen kann:

Der Pfad zur verknüpften Datei

Der Pfad zur verknüpften Datei

Nun weiß man zwar schon, auf welche Datei verwiesen wird und dass diese in meinem Beispiel irgendwo unter “D:\” gespeichert ist. Wo genau die Verknüpfung in der Arbeitsmappe verwendet wird, darüber lässt einen Excel jedoch völlig im Unklaren.

Wie findet man nun alle Formeln, die diese Verknüpfung enthalten?

Dazu muss man Folgendes wissen:
Wenn die verknüpfte Datei nicht geöffnet ist (wovon wir hier ausgehen können), dann enthalten alle Formeln mit einer Verknüpfung den vollständigen Pfad zu der betreffenden Datei:

Externer Bezug direkt in einer Formel

Externer Bezug direkt in einer Formel

Mit diesem Wissen und der Information aus dem Fenster “Verknüpfungen bearbeiten” können wir die ganz normale Suchfunktion nutzen!

Rufe also das Suchfenster auf (Menüband Start | Suchen und Auswählen bzw. STRG+F) und schreibe in das Eingabefeld “D:\”. Klicke dann auf die Schaltfläche “Optionen”, um vor der Suche weitere Einstellungen vorzunehmen:

Das Suchen-Fenster

Das Suchen-Fenster

Wähle jetzt im Feld “Durchsuchen” die Option “Arbeitsmappe”, damit die komplette Datei durchsucht wird und klicke dann auf die Schaltfläche “Alle suchen”. Je nach Umfang und Komplexität deiner Datei wirst du nach einigen Momenten eine Liste aller Zellen bekommen, die eine externe Verknüpfung enthalten:

Suche in der gesamten Arbeitsmappe

Suche in der gesamten Arbeitsmappe

Nun kannst du jeden Eintrag der Reihe nach anklicken und Excel wird die entsprechende Zelle direkt anspringen. Dort kannst du dann die notwendigen Änderungen vornehmen, wie z.B. die Verknüpfung durch den Wert ersetzen.

Wenn du damit durch bist, solltest du im Register “Daten” überprüfen, ob die Schaltfläche “Verknüpfungen bearbeiten” jetzt ausgegraut ist:

Es gibt keine externen Verknüpfungen mehr

Es gibt keine externen Verknüpfungen mehr


Denn erst dann gibt es keine externen Verknüpfungen mehr. Sollte das nicht der Fall sein, geht die Suche in die nächste Phase…

Versteck 2: Der Namensmanager

In Excel lassen sich bekanntermaßen für Zellen oder Zellbereich eigene Namen definieren, über die diese Bereiche dann in Formeln angesprochen werden können.
Nicht ganz so bekannt ist die Möglichkeit, im Namensmanager statt einer Zelladresse eine Formel in das Bezugsfeld einzugeben und diese dann mit einem Namen zu versehen. Damit lassen sich allerhand praktische Dinge anstellen. Beispiele dafür findest du hier oder hier.

Und genau hier können sich auch externe Verknüpfungen verstecken.

Also einfach mal den Namensmanager aufrufen (Menüband Formeln | Namensmanager) und prüfen, ob einer der gelisteten Namen einen externen Bezug enthält, so wie im folgenden Bild:

Externe Bezüge im Namensmanager

Externe Bezüge im Namensmanager

Dann kannst du die notwendigen Maßnahmen ergreifen und den Bezug anpassen oder ggf. den Namen komplett löschen, wenn er nicht mehr benötigt wird.

Versteck 3: Die Datenüberprüfung

Ein weiterer Kandidat für versteckte externe Verknüpfungen ist die Datenüberprüfung (Menüband Daten | Datenüberprüfung). Hier gibt es grundsätzlich zwei mögliche “Verstecke”: “Liste” und “Benutzerdefiniert”

Datenüberprüfung: Liste und Benutzerdefiniert

Datenüberprüfung: Liste und Benutzerdefiniert

Bei beiden Optionen lässt sich ein Zellenbereich oder gar eine eigene Formel hinterlegen. Als fortgeschrittener Excel-Anwender wirst du jetzt einwenden “Ja, aber hier kann ich doch nur Bezüge innerhalb der aktuellen Arbeitsmappe setzen, nicht jedoch auf eine andere Datei!”

Das stimmt. Der Versuch, hier bei der Eingabe in eine andere Arbeitsmappe zu klicken, wird von Excel mit einem Fehler quittiert.

Aber stelle dir folgendes nicht so unwahrscheinliche Szenario vor:

Du richtest in der Datei 1 auf dem Blatt “Tabelle1” eine Datenüberprüfung ein. Als Quelle dient eine Liste, die sich in Blatt “Tabelle2” befindet

Zellbezug oder Formel als Referenz

Zellbezug oder Formel als Referenz


Soweit alles noch im grünen Bereich.

Zu irgendeinem späteren Zeitpunkt arbeitest du an einer völlig neuen Arbeitsmappe und kommst du auf die verwegene Idee, das Blatt “Tabelle1” aus der ersten Datei in diese Arbeitsmappe zu kopieren oder zu verschieben. Damit wird auch die Regel für die Datenüberprüfung übernommen, die nun allerdings auf einen externen Bereich verweist (und obendrein fehlerhaft ist):

Externer Bezug nach einer Kopie

Externer Bezug nach einer Kopie

Da steckt also der kleine Teufel und könnte jetzt beseitigt werden.

Doch wie findet man alle Zellen, die eine Datenüberprüfung enthalten?

Es geht leider nicht ganz so komfortabel, wie bei der oben gezeigten Suchfunktion. Ist aber trotzdem nicht so dramatisch. Hinter der Schaltfläche “Suchen und Auswählen” verbirgt sich unter anderem die Option “Datenüberprüfung”:

Zellen mit Datenüberprüfung finden

Zellen mit Datenüberprüfung finden

Damit werden auf dem aktuellen Arbeitsblatt sämtliche Zellen markiert, die eine Datenüberprüfung enthalten. Nun muss man “nur” noch nachsehen, ob sich dahinter eine externe Verknüpfung befindet. Kommen mehrere Arbeitsblätter zum Einsatz, muss die Suche auf jedem Blatt wiederholt werden.

Versteck 4: Bedingte Formatierung

Sollte die Schaltfläche “Verknüpfungen bearbeiten” immer noch aktiv sein, bleibt ein weiteres potentielles Versteck für externe Verknüpfungen: Die bedingten Formatierungen.

Auch hier gibt es ja die Möglichkeit, Formeln als Formatierungsregeln zu hinterlegen, die wiederum Zellbezüge enthalten können:

Eigene Formel in bedingter Formatierung

Eigene Formel in bedingter Formatierung

Wie schon oben bei der Datenüberprüfung beschrieben sind darin nur Bezüge innerhalb der eigenen Arbeitsmappe zulässig. Aber wenn das Arbeitsblatt mit der bedingten Formatierung eben später in eine andere Arbeitsmappe kopiert oder verschoben wird, dann hat man wieder den Salat:

Externer Bezug in der Formatierungsregel

Externer Bezug in der Formatierungsregel

Ein externer Bezug, der obendrein auch noch ungültig ist.

Nachdem auch dieses Problem ausgemerzt wurde, sollten keine externen Verknüpfungen mehr vorhanden sein. Oder kennst du noch ein anderes Versteck? Wenn ja, dann lass es uns unten in den Kommentaren wissen.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

14 Gedanken zu “Die Suche nach der externen Verknüpfung

  • Annette

    Vielen Dank für die Tips.
    Noch als Ergänzung: Wenn es nur darum geht die unerwünschten Verknüpfungen in Werte umzuwandeln, kann man ja auch einfach “Verknüpfung löschen” wählen.

    Wo ich mich mal zu Tode gesucht hatte, war ein “Kamera”-Bild aus einer anderen Exceltabelle.
    Da ist ja auch ein Bezug hinterlegt.

    Gruss
    Annette

    • Martin Weiß Autor des Beitrags

      Hallo Annette,

      das Kamera-Bild ist wirklich noch ein heißer Kandidat für eine externe Verknüpfung. Daran hatte ich überhaupt noch nicht gedacht, vielen Dank für den guten Tipp!
      Verknüpfungen einfach löschen geht natürlich auch, aber das sollte man wirklich nur dann tun, wenn sie tatsächlich nirgendwo benötigt werden. Ansonsten tauchen nämlich plötzlich Bezugsfehler in irgendwelchen Formeln auf.

      Schöne Grüße,
      Martin

  • Hans-georg Müller

    Martin, danke, das ist endlich die Lösung, wo diese verflu… Verknüpfungen alles herkommen können. Klasse, wie immer!
    Excellente Grüße
    Georg

  • Ronny Welke

    Hallo, vollkommen richtig, diese Meldung kann einen wirklich in den Wahnsinn treiben. Ich habe mir immer damit geholfen, dass ich sie ausgeschalten haben. Diese Möglichkeit hat man in dem Fenster “Verknüpfungen bearbeiten”. Dort verbirgt sich hinter dem Button “Eingabeaufforderung beim Start …” die Option “Kein Warnung anzeigen und Verknüpfung nicht aktualisieren”. Damit hat man es immer noch selber in der Hand, Verknüpfungen zu aktualisieren. Ich meine, dass man auch in Grafiken Verknüpfungen auf andere Dateien haben kann, die nicht im Fenster “Verknüpfungen bearbeiten” angezeigt werden. Auch das wäre so ein Versteck!

    Schöne Grüße
    Ronny

    • Martin Weiß Autor des Beitrags

      Hallo Ronny,

      auch ein guter Tipp, die Warnungen abzuschalten und auf die Aktualisierung einfach zu verzichten, danke dafür.
      Und die Verknüpfungen über eingefügte Grafiken sind auch ein sehr gutes Versteck. Annette hat fast das gleiche auch über das Kamera-Tool entdeckt.

      Schöne Grüße,
      Martin

  • Andi

    Hallo,
    in diesem Zusammenhang kann man noch die “Verbindungen” erwähnen, zwei Icons über “Verknüpfungen bearbeiten”. Ist zwar nicht ganz so nervig und kompliziert wie die genannten Beispiele, aber doch praktisch zu wissen, wo es so eine Übersicht gibt.
    lg Andi

    • Martin Weiß Autor des Beitrags

      Hallo Andi,

      du hast Recht, die Verbindungen sind ein ähnlich gelagertes Thema. Hier verbergen sich z.B. Datenbankabfragen oder ähnliches. Danke für den Hinweis!

      Schöne Grüße,
      Martin

  • Christian Fette

    Bei Verwendung des Namensmanager sei darauf hingewiesen, das es auch versteckte Namen gibt. Die kann ich mit dem Namensmanager nicht bearbeiten.

    Hier ein Makro zum Löschen von versteckten Namen.
    (Quelle: https://support.microsoft.com/en-us/help/119826/macro-to-remove-hidden-names-in-active-workbook)

    ‘ Module to remove all hidden names on active workbook
    Sub Remove_Hidden_Names()
    ‘ Dimension variables.
    Dim xName As Variant
    Dim Result As Variant
    Dim Vis As Variant
    ‘ Loop once for each name in the workbook.
    For Each xName In ActiveWorkbook.Names
    ‘If a name is not visible (it is hidden)…
    If xName.Visible = True Then
    Vis = “Visible”
    Else
    Vis = “Hidden”
    End If
    ‘ …ask whether or not to delete the name.
    Result = MsgBox(prompt:=”Delete ” & Vis & ” Name ” & _
    Chr(10) & xName.Name & “?” & Chr(10) & _
    “Which refers to: ” & Chr(10) & xName.RefersTo, _
    Buttons:=vbYesNo)
    ‘ If the result is true, then delete the name.
    If Result = vbYes Then xName.Delete
    ‘ Loop to the next name.
    Next xName
    End Sub

  • Ditmar Schwarze

    Hallo Martin,
    ich bin ein etwas reiferer (62) PC-User, der mittlerweile auch den Zweck und den Spaß von Excel zu schätzen weiß. Deine verständlich dargelegten Tipps sind hierbei sehr hilfreich und daher lobenswert. Das wollte ich nur mal so loswerden.
    Schöne Grüße
    Ditmar

    • Martin Weiß Autor des Beitrags

      Hallo Ditmar,

      vielen Dank für das Lob und das nette Feedback. So etwas motiviert immer sehr!

      Schöne Grüße und weiterhin viel Spaß mit Excel,
      Martin

  • Martin Weiß Autor des Beitrags

    Hier für alle Interessierten noch eine weitere Quelle für externe Verknüpfungen. Mein Leser Uwe hat mich darauf aufmerksam gemacht und netterweise gleich noch ein Stück VBA-Code mitgeliefert, der bei der Suche hilft. Vielen Dank dafür!

    “Heute bin ich über eine weitere Quelle externer Verknüpfungen gestolpert: Makro-Buttons.
    Hier ist ein Makro, das per MsgBox das Tabellenblatt des Buttons, die Button-Beschriftung und die externe Datei ausgibt. Buttons ohne Makro bzw. mit lokalem Makrobezug werden ignoriert. Vielleicht kannst du es ja noch zum Artikel hinzufügen, denn es könnte für den einen oder anderen nützlich sein. Möglicherweise funktioniert es auch für sonstige Objekte wie Grafiken, hab ich aber keine Ahnung und nicht getestet.
    Und vielen Dank übrigens für deine Artikel und Tips.
    Uwe”

    _____________________________

    Sub Button_mit_externer_Verknüpfung_suchen()
    Dim Datei As String, Blatt As String, Button As String, Makro As String, Verknüpfung As String

    Datei = ActiveWorkbook.Name
    For Each s In ActiveWorkbook.Sheets
    s.Select
    Blatt = s.Name

    On Error Resume Next ‘ Kommentarfelder führen zu Fehler
    For Each b In ActiveSheet.Shapes
    b.Select
    Button = b.AlternativeText ‘ Beschriftung
    Makro = b.OnAction ‘ Makro-Zuordnung
    If Makro <> “” Then ‘ Button HAT Makro-Zuordnung
    Verknüpfung = Left(Makro, InStr(Makro, “!”) – 1)
    End If

    If Left(Makro, Len(Datei)) = Datei Or Makro = “” Then
    Else
    MsgBox “Im Tabellenblatt ” & Blatt & Chr(13) & _
    “” & Chr(13) & _
    “beim Makro-Button ” & Button & Chr(13) & _
    “” & Chr(13) & _
    “ist eine Verknüpfung zur Datei ” & Chr(13) & _
    Verknüpfung
    End If
    Next
    Next
    MsgBox “Feddisch”
    End Sub
    _____________________________