Die Suche nach der externen Verknüpfung 40

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.

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

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.

40 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
    _____________________________

      • Martin Weiß Autor des Beitrags

        Hallo Ringe,

        tut mir leid, dass Du hier nicht fündig geworden bist. Offensichtlich gibt es noch mehr Verstecke…
        Wenn Du die Verknüpfung doch noch findest, würde ich mich freuen, wenn Du hier noch einen kurzen Tipp dazu gibst.

        Schöne Grüße,
        Martin

        • Ringo

          Hallo Martin,
          ich konnte die Verknüpfung leider nicht finden. Ich habe die Datei neu aufgesetzt und da waren die Verknüpfungen verschwunden.

          Gruss Ringo

          • Martin Weiß Autor des Beitrags

            Hallo Ringo,

            das ist ärgerlich (soweit war ich auch schon hin und wieder). Trotzdem danke für die kurze Rückmeldung.

            Schöne Grüße,
            Martin

  • Andreas K.

    Hallo,

    GANZ, GANZ wichtig:

    Blattschutz aufheben!

    Ich hatte anscheinend eine Verknüpfung in einer Datenquelle eines Diagramms.
    Als das Blatt geschützt war, konnte die Verknüpfung weder gefunden noch gelöscht werden.

  • Carsten

    Ich habe die bislang aufgelisteten Suchen verwendet – allerdings habe ich meine Verknüpfung so nicht gefunden – ich habe das „Versteck“ dann aber doch „zeitnah“ über Ausschlußverfahren gefunden – es war eine Pivottabelle – die dort versteckten externen Links habe ich mit den genannten Verfahren (ohne Makros) nicht gefunden (ode ich habe ienen Schritt ausgelasssen…:-)

    • Martin Weiß Autor des Beitrags

      Hallo Carsten,

      Verknüpfung in einer Pivot-Tabelle hört sich nach Datenverbindung an. Auch ein sehr schönes Versteck.
      Ich muss wohl noch einen zweiten Artikel schreiben…

      Schöne Grüße,
      Martin

  • Matthias Janbeck

    Hallo,

    vielen Dank für die Zusammenstellung, Das ha mir nach dem Kopieren eines Tabellenblattes geholfen die Verknüpfungsmeldung wieder abzuschalten.
    SUPER
    Freundliche Grüße
    Matthias Janbeck

  • Richard Seufert

    Hallo,
    weitere Möglichkeit sind die Datenbereiche von Grafiken. Ursache ist ebenfalls meist ein „ausgelagertes“ Tabellenblatt.
    Als ich mal eine Verknüpfung weder mit den Tipps oben noch mit VBA ausfindig machen konnte, habe ich den Bezug über „Quelle ändern“ auf die geöffnete Datei selbst geändert – siehe da, der externe Bezug war weg. Man weiß zwar dann immer noch nicht, wo die Ursache lag, aber bislang hatte ich keine Nebenwirkungen damit.
    VG
    Richard

    • Martin Weiß Autor des Beitrags

      Hallo Richard,

      danke für diese Ergänzung! Der von Dir beschriebene Weg ist tatsächlich oft Erfolg versprechend, wenn die Quelldaten ohnehin in die aktuelle Arbeitsmappe kopiert wurden. Nur spätestens, wenn irgendwo die mysteriösen #BEZUG-Fehler auftauchen, dann weiß man, dass doch noch etwas fehlt…

      Schöne Grüße,
      Martin

  • Dario

    Danke für den Artikel. Als alternatives Vorgehen schlage ich folgendes vor:

    1. Ausgangspunkt gemäss diesem Artikel -> Verknüpfung nachschauen unter der Registerkarte „Daten“ und Funktion „Verknüpfungen bearbeiten“

    2. Excel Datei mit der Software „7-Zip“ extrahieren

    3. Das Verzeichnis mit den extrahierten Excel Daten im Windows Explorer öffnen

    4. Die Suche des Windows Explorer nutzen um nach dem unter Schritt 1 nachgeschlagenen Pfad zu suchen

    So habe zumindest ich die Verknüpfung ziemlich schnell gefunden 🙂

  • Hans Joachim

    Diese Tipps sind Gold wert. Vielen Dank für die Hinweise. Ich konnte ein lange störendes Problem lösen. „Option 2“ war die Lösung. Excel hatte beim Importieren von Tabellenblätternn aus anderen Tabellen die Verknüpfungen von Namen zur Originaltabelle mit übernommen. Jetzt ist Ruhe eingekehrt.
    Vielen Dank

    • Martin Weiß Autor des Beitrags

      Hallo Hans Joachim,

      freut mich, wenn die Tipps helfen konnten. Die Verknüpfungen bringen mich gelegentlich immer noch zur Verzweiflung…

      Schöne Grüße,
      Martin

  • Heinz Siebenmorgen

    Hallo, guten Tag,

    Verknüpfungen ließen sich teilweise in einer sehr großen Mappe (30 Blatt, bis Spalte CA) nicht aktualisieren.
    Aus einer bestehenden Mappe hatte ich Teile übertragen, Verknüpfungen „mitgeschleppt“.

    Die Tipps hier auf der Seite führten mich schrittweise auf die Spur.
    Um in den 30 Excel – Blättern fündig zu werden, habe ich Blatt für Blatt gelöscht, gespeichert, neu gestartet.
    Als Blatt 23 gelöscht war, trat der Fehler nicht mehr auf. Also schon mal den „Standort“ des Fehlers gefunden.
    Dann die Tipps auf Excel – Seite 23 „durchgearbeitet“ und eine „leere“ Bedingte Formatierung gefunden.
    Diese gelöscht und das Problem war final beseitigt!
    Die Eingrenzung half ungemein.

    Jetzt nur noch die Originaldatei bereinigen und die Arbeit am Mammut-Projekt kann weitergehen.

    Besten Dank für die Tipps.

    Heinz aus dem Rheinland.

    • Martin Weiß Autor des Beitrags

      Hallo Heinz,

      danke für das Feedback. Freut mich sehr, wenn die Tipps weiterhelfen konnten.

      Schöne Grüße,
      Martin

  • Nimrod

    Hallo,
    evtl. schon genannt aber ich habe beim kopieren von Tabellenblättern aus fremden Dateien, dann oft eine Verknüpfung (externen Bezug)auf die ursprüngliche Datei, wenn das Blatt Active-X Kombinationsfelder hat (Combobox).

    Wenn man all die hier in diesem Artikel genannten Möglichkeiten durch hat und trotzdem noch Verknüpfungen vorhanden sind, bleibt einem zur Fehlersuch nur noch die Möglichkeit, erst einzelne Tabellenblätter zu löschen und anschließend einzelne Zeilen oder Spalten zu löschen um festzustellen, wo der Fehler genau lokalisiert ist (natürlich ohne zu speichern).
    So kann ich schon immer den Bereich eingrenzen und mir fällt dann oft schon die Zelle mit bedingter Formatierung oder das Active-X Steuerelement ins Auge, welche es dann sein könnte oder ist.

    Großes Lob über die hier gemachte Zusammenfassung!!

    • Martin Weiß Autor des Beitrags

      Hallo Nimrod,

      vielen Dank für das Lob und den Hinweis auf die Active-X-Elemente. Im Grunde kann man sagen, dass an jeder Art von Objekten theoretisch auch eine externe Verknüpfung hängen kann.
      Ich werde wohl irgendwann mal einen aktualisierten Artikel mit all Euren Ergänzungen herausbringen müssen 🙂

      Schöne Grüße,
      Martin