Vor längerer Zeit hatte ich einen Artikel veröffentlicht, der sich mit externen Verknüpfungen in Excel-Dateien befasst. Wie du ja sicherlich weißt, lassen sich in einer Formel Bezüge zu einer anderen Arbeitsmappe erstellen. Was in manchen Fällen ganz praktisch erscheinen mag, treibt einen jedoch manchmal in den Wahnsinn. Nämlich dann, wenn man beim Öffnen einer Datei mit der Meldung beglückt wird „Diese Arbeitsmappe enthält Verknüpfungen zu mindestens einer externen Quelle…“, obwohl man zumindest wissentlich niemals eine solche Verknüpfung gesetzt hat.
Solche externen Links können nämlich nicht nur über normale Formeln entstehen, sondern über unzählige andere Wege, die leider nicht immer so einfach zu finden sind. Wenn du also schon einmal einen Kollegen mit einem irren Kichern und einem Hauch von Wahnsinn in den Augen gesehen hast, könnte es sein, dass er bei der Suche nach einer solchen Verknüpfung einfach übergeschnappt ist…
Damit es dir nicht auch so ergeht, zeige ich dir heute ein paar weitere Verstecke.
Die Vorgeschichte
Im ursprünglichen Artikel Die Suche nach der externen Verknüpfung hatte ich schon gezeigt, dass der erste Weg über das Menü „Daten | Verknüpfungen bearbeiten“ führt.
Verknüpfungen bearbeiten
Dort werden alle externen Links mit ihren Pfadangaben aufgelistet, so dass man in einem ersten Schritt über die normale Suchen-Funktion alle betreffenden „normalen“ Formelzellen finden und bearbeiten kann.
Verknüpfungen mit der Suchfunktion finden
Darüber hinaus habe ich einige weitere Plätze aufgezeigt, an denen versteckte externe Verknüpfungen verborgen sein können.
- Namensmanager
- Datenüberprüfung
- Bedingte Formatierung
Diese Punkte wurden im ersten Artikel bereits ausführlich beschrieben wurden, daher gehe ich hier nicht mehr näher darauf ein. Da anschließend aber viele Leser noch weitere Verstecke gemeldet haben, wurde es endlich Zeit für eine Erweiterung dieser Sammlung.
Generell sollte man nicht vergessen, dass sich Verknüpfungen auch in ausgeblendeten Tabellenblättern befinden können. Daher empfehle ich, vor allen Maßnahmen, sämtliche Blätter einzublenden und auch einen etwaigen Blattschutz zu entfernen.
Das Kamera-Tool
Ein ungemein praktisches Hilfsmittel, mit dessen Hilfe sich dynamische Screenshots auf bestimmte Zellbereich einfügen lassen. Wer das Kamera-Tool noch nicht kennt: Guckst du hier.
Wird jedoch ein solches Bild oder ein Arbeitsblatt, das ein solches Bild enthält, in eine andere Arbeismappe kopiert oder verschoben, wird damit automatisch eine externe Verknüpfung auf die Ursprungsdatei erzeugt:
Verknüpftes Bild per Kamera-Tool
Diagramme
Diagramm sind ja immer dynamisch mit einem Quelldatenbereich verbunden. Das hat den Vorteil, dass sich das Diagramm automatisch ändert, sobald sich die Quelldaten ändern. Aber auch hier gilt:
Wenn man das Diagramm oder das Arbeitsblatt mit dem Diagramm in eine neue Arbeitsmappe kopiert, bleiben die Bezüge zu ursprünglichen Quelle erhalten – eine neue externe Verknüpfung ist entstanden. Diese findet man nur, wenn man das Diagramm markiert und in „Diagrammtools | Entwurf“ die Schaltfläche „Daten auswählen“ aktiviert:
Diagramme mit externer Datenquelle
Dort wird dann im Eingabefeld die Quelldatei angezeigt…
Anzeige nur bei geöffneter Datenquelle
… allerdings nur dann, wenn diese Quelldatei auch geöffnet ist! Ansonsten bleibt dieses Feld nämlich leer. Aber keine Sorge, die Quelle lässt sich trotzdem finden. Man muss nur auf die „Bearbeiten“-Schaltfläche klicken, mit der man die Datenreihen bearbeiten kann und schon erhält man die gewünschte Information:
Datenreihe bearbeiten
Datenreihe bearbeiten
Formen und Schaltflächen
Wer seine Excel-Dateien gerne bedienerfreundlich gestaltet, verwendet vielleicht Formen oder Schaltflächen. Diese können entweder mit einem Hyperlink versehen sein, um z.B. an eine bestimmte Stelle zu springen. Oder sie sind mit einem Makro/VBA-Schnippsel verknüpft, welches beim Klick eine vordefinierte Funktion ausführt.
Wie du sicherlich schon ahnst: Auch hier können sich Verknüpfungen verbergen. Ein Rechtsklick auf die Schaltfläche zeigt die Quelle an: Entweder über die Option „Link bearbeiten“ oder den Punkt „Makro zuweisen…“, je nachdem, was im konkreten Fall eben zutrifft.
Links und Makros hinter Formen
Möglich ist auch eine Kombination aus beidem, dass also sowohl ein Link gesetzt wurde als auch eine Verknüpfung mit einem Makro existiert.
Formularsteuerelemente / ActiveX-Steuerelemente
In eine ähnliche Richtung gehen die Steuerelemente, die man über die Entwicklertools einfügen kann. Auch hier ist in sehr vielen Fällen Makro- bzw. VBA-Code mit diesen Elementen verknüpft. Wenn sich das dazugehörige VBA-Modul in einer anderen Datei befindet, hat man wieder eine „wunderbare“ externe Verknüpfung.
Ein Rechtsklick auf das Steuerelement hilft weiter:
Bei normalen Formularsteuerelementen wählt man „Makro zuweisen…“ um an die Verknüpfung heranzukommen, bei ActiveX-Steuerelementen gibt es stattdessen den Eintrag „Code anzeigen“.
Formularsteuerelemente und ActiveX-Steuerelemente
Wobei hier der VBA-Code ohnehin direkt mit in die neue Arbeitsmappe übernommen wird. Aber das ist ein anderes Thema, in die Tiefen von VBA steige ich hier nicht ein.
Eingebettete Objekte
Durchaus beliebt ist die Möglichkeit, externe Dateien als Objekte in eine Excel-Mappe einzubetten, um später per Doppelklick die entsprechende Datei (Word, PDF, Bild etc) zu öffnen. Dabei gibt es die Möglichkeit, die komplette Datei als Symbol einzubinden (was die Dateigröße der Arbeitsmappe natürlich erhöht) oder man erstellt lediglich ein Symbol und eine Verknüpfung auf die Originaldatei. Das spart Speicherplatz – und führt, wie der Name schon sagt, zu einer Verknüpfung.
Objekt als Verknüpfung einfügen
Markiert man ein solchermaßen eingefügtes Objekt, wird oben in der Bearbeitungszeile der Pfad zur Quelldatei angezeigt:
Pfad zur verknüpften Datei
Etwas allgemein formuliert kann man sagen:
An jeder Art von eingefügtem Objekt (Bilder, Formen, 3D-Modelle, Piktogramme…) können per Rechtsklick entweder Links erstellt oder mit Makros verknüpft werden. Wird ein solches Objekt später in eine andere Arbeitsmappe kopiert, verweisen die Verknüpfungen unter Umständen auf die Ursprungsdatei.
Besonderheit: Pivot-Tabellen
Die oben gezeigten Fälle führen in aller Regel zu einem Eintrag in der Übersicht „Verknüpfungen bearbeiten“ bzw. zu einem entsprechenden Hinweis auf externe Verknüpfungen, wenn die Arbeitsmappe geöffnet wird.
Anders verhält es sich jedoch bei Pivot-Tabellen. Wurde eine solche oder ein Arbeitsblatt mit einer Pivot-Tabelle in eine andere Excel-Arbeitsmappe kopiert, führt dies in aller Regel erst einmal weder zu einer Fehlermeldung oder zu einem Warnhinweis beim Öffnen der Datei. Erst wenn die Pivot-Tabelle aktualisiert werden soll, kommt ein Hinweis auf die fehlende Datenquelle (Anmerkung: Dies kann je nach PivotTable-Einstellungen auch schon beim Öffnen der Datei passieren).
Wenn die Pivot-Tabelle nicht gerade über eine echte Datenverbindung mit einer Datenquelle verbundenen ist (z.B. per Power Query), dann taucht eine solche Verknüpfung weder in der besagten Übersicht „Verknüpfungen bearbeiten“ noch in der Übersicht der Abfragen und Datenverbindungen auf. Hier führt der Weg über die PivotTable-Tools. Im Register „Analysieren“ findet sich die Schaltfläche „Datenquelle ändern“, in der Pfad und Datei der ursprünglichen Datenquelle angezeigt werden:
Datenquelle in Pivot-Tabelle ändern
Nun hat man die Möglichkeit, diesen Pfad/diese Datei zu ändern. Oder man muss im schlimmsten Fall auf die Pivot-Tabelle oder zumindest deren Aktualisierung ganz verzichten, wenn man keinen Zugriff auf die Datenquelle hat.
Das war’s für heute mit den externen Verknüpfungen. Solltest du zu den bedauernswerten Opfern gehören, dann hoffe ich, du konntest hier ein paar Tipps finden. Und wenn du weitere Verstecke solcher Verknüpfungen kennst, lass es uns in den Kommentaren wissen!
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.
… und wieder einmal ein toller Tipp! Ich quäle mich seit Wochen mit diesem Problem herum. Nun gibt es eine Irre weniger! 😀
Freut mich zu hören!
Liebe Grüße,
Martin
Ich habe den Artikel zwar nur schnell überflogen, aber kann es sein, dass du den Namensmanager vergessen hast?
Außerdem finden sich manchmal auch Verknüpfungen bei „Daten => Datenüberprüfung“ 🙂
Hallo Marcel,
ja, du hast den Artikel nur überflogen 🙂
Auf den Namensmanager und die Datenüberprüfung bin ich ganz am Anfang in der Vorgeschichte eingegangen (mit einem Verweis auf den ersten Artikel).
Schöne Grüße,
Martin
Schon wieder was gelernt! Ich habe mich schon häufiger gewundert, wo denn die Verknüpfung sein soll, wenn doch „Daten – Verknüpfungen bearbeiten“ ausgegraut ist. Ich wusste gar nicht, welche Möglichkeiten es noch gibt!
Vielen Dank für diesen wieder einmal sehr hilfreichen Artikel!
Sehr gern geschehen!
Schöne Grüße,
Martin
Klasse Sammlung! 🙂
Auch durch Bezüge in bedingten Formatierungsformeln können Verknüpfungen resistenzen entwickeln.
Gruß,
T.
Hallo TDi,
danke für den Hinweis, über die bedingten Formatierungen hatte ich auch schon im ersten Artikel (siehe Vorgeschichte) geschrieben.
Schöne Grüße,
Martin
Die Beiträge zu Hyperlink sind schon alle etwas älteren Datums, gibt es schon etwas Neues. Unter Windows10 will es mir nicht gelingen in EXCEL oder in WORD ein Hyperlink einzufügen. Jeder Versuch endet mit Programmabbruch.
Hallo Eva,
auch wenn die Frage nicht direkt zum Artikel oben passt: Es hat sich grundsätzlich nichts an den Hyperlinks in Excel geändert. Sie funktionieren eigentlich nach wie vor und konnte das von dir beschriebene Problem bisher selbst noch nie feststellen. Daher kann ich jetzt leider auch keinen Tipp geben.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für diesen wieder super erklärten klaren Artikel !
Diese Verknüpfungen sind ja wirklich ein weites Feld. Und ich habe mit Deinen Erläuterungen schon viele Probleme lösen können.
Aktuell habe ich jedoch den umgekehrten Fall bezogen auf ein Diagramm.
(Ich versuche ein Dashboard zu bauen mit einer Vielzahl von Bezugstabellen (innerhalb und außerhalb des Dashboards)).
Und nun habe ich ein Diagramm, das anscheinend nicht (mehr) verknüpft ist.
Du schreibst (im Abschnitt Diagramme), dass der Bezug auf geschlossene Tabellen in der Zeile „Entwurf – Daten auswählen – Diagrammbereich“ dann leer ist und also nicht angezeigt wird [was habe ich schon ergebnislos nach eine Grund dafür gegooglelt !]
Und dass man die Quelle aber trotzdem ermitteln kann mit: „Bearbeiten“.
Bei mir steht dann aber
Reihenname: =“Hamburg“
Reihenwerte: ={8.2.4.1.5.2.3.5}
Also echte Werte und keine Bezüge.
Die Tabelle aus der ich das Diagramm kreiert habe, exisitert. Aber es existiert anscheinend kein Bezug mehr darauf. Aber muss eine Diagramm nicht auf eine Tabelle mit Daten bezogen sein? Oder kann sie einfach alleine existieren?
(„Diagramme sind ja immer dynamisch mit einem Quelldatenbereich verbunden.“)
Wäre super, wenn Du hier Licht ins Dunkle bringst :-))
Gruß
Sabina
Hallo Sabina,
die Diagramme sind tatsächlich etwas speziell. Hier kommt es darauf an, wie das Diagramm in die andere Datei gekommen ist und um was für eine Art von Diagramm es sich handelt.
Ich habe festgestellt, dass bei kopierten Pivot-Charts das von dir beschriebene Verhalten auftritt. Also z.B. „Hamburg“ und {8.2.4.1.5.2.3.5} als feste Datenwerte angezeigt werden. Das Diagramm braucht in diesem Fall also tatsächlich keine Datentabelle. Und in diesem Fall gibt es dann auch keine Verknüpfung zur Ursprungsdatei, da die Werte „hart“ übernommen wurden.
Das kopierte Diagramm sieht danach wie ein normales Diagramm aus, aber eine Verknüpfung zu den Original-Tabellen bekommst du damit nicht mehr hin (zumindest mir ist das nicht gelungen). Das heißt, du musst das Diagramm neu erstellen.
Schöne Grüße,
Martin
Hallo Martin,
Du hast Recht, das Diagram war ursprünglich auf Basis einer Pivot-Tabelle entstanden (und auch kopiert).
Dann muss man bei so was also aufpassen, dass der Bezug bei kopierten Pivot-Diagrammen verloren gehen kann (und somit keine Aktualisierung mehr statt findet, obwohl es so dynamisch aussieht ;-})
Vielen Dank für die Klarstellung !
VG Sabina
Ich habe auch erst lange gesucht, bis ich in Diagramm-Quelldaten fündig geworden bin wie Kommentator ‚Urs‘ im ersten Artikel und Andere. Damit habe ich vier von fünf Verweisen erfolgreich entfernen können, aber immer noch einen nicht auffindbar gehabt.
Und das war am Ende Pebcac, und die Lösung ein Zufallsfund: ich muss wohl mal ein Diagramm bei gedrückter Strg-Taste mit der Maus minimal bewegt und somit kopiert haben, ohne es zu merken oder zu wollen. Damit war ein „unsichtbares“ Diagramm versteckt hinter einem anderen im Vordergrund, und ausgerechnet dies hatte Verknüpfungen zu externen Quellen! Merke: wenn man immer noch unauffindbare Verknüpfungen hat, einfach mal grafische Elemente (Diagramme, Fotos,…) etwas zur Seite ziehen und gucken ob noch was dahinter liegt 😉
Hallo aynoS,
sich überlagernde Objekte, ein sehr gemeines Versteck! Und ein sehr guter Hinweis, vielen Dank dafür!
Schöne Grüße,
Martin
Hallo Martin
Toller Artikel, hatte gerade heute das Problem mit einer solchen Verknüpfung.
Schlussendlich war der Verweis in den bedingten Formatierungen „versteckt“.
Ich hatte eine Arbeitsmappe mit einigen Tabellenblättern, wo fast alle in diesem Artikel genannten Möglichkeiten vorkommen konnten (benannte Bereiche, Datenprüfungen, bedingte Formatierungen, mehrere Pivot-Tabellen, mehrere Diagramme, Formen und Schaltflächen).
Dabei hat sich folgendes Vorgehen bewährt:
Zuerst das richtige Tabellenblatt finden, d.h. alle Zellen und Objekte löschen und prüfen ob sich dann die Verknüpfung entfernen lässt. Falls ja, Tabellenblatt gefunden, falls nein, Vorgang mit nächstem Tabellenblatt wiederholen.
Nach der Eingrenzung auf das Tabellenblatt haben sich dann die verbleibenden Möglichkeiten wesentlich reduziert.
Das war für mich einfacher als alle Möglichkeiten auf allen Tabellenblätter zu durchsuchen.
Schöne Grüsse
Dani
Hallo Dani,
vielen Dank für das Feedback und Deine Tipps zur praktischen Umsetzung.
Schöne Grüße,
Martin
eine weitere Möglichkeit, sich solche versteckten Verknüpfungen „einzufangen“, sind in ein AddIn ausgelagerte benutzerdefinierte Funktionen. Diese Erfahrung durfte ich gerade heute machen, bin aber zum Glück schnell auf die Lösung gekommen.
da hilft dann nur:
entweder die benutzerdefinierten Funktionen in die betroffene Arbeitsmappe zu integrieren (dann gibt es eben keine externe Verknüpfung mehr)
oder aber die Eingabeaufforderung für externe Verknüpfungen auszuschalten und voreinzustellen, was beim Öffnen wegen der Verknüpfungen passieren soll.
Dazu wie folgt vorgehen:
1. Über das Menüband in der Menügruppe „Abfragen und Verbindungen“ die Schaltfläche „Verknüpfungen bearbeiten“ aufrufen.
2. Im Folgedialog „Eingabeaufforderung beim Start“ aufrufen
3. Gewünschte Aktion auswählen
Standardmäßig ist die Option „Benutzer entscheidet, ob eine Warnung angezeigt wird“ voreingestellt.
Zur Verfügung stehen noch:
„Keine Warnung anzeigen und Verknüpfung nicht aktualisieren“
„Keine Warnung anzeigen und Verknüpfung aktualisieren“
Hallo Tino,
vielen Dank für diese Ergänzung. Es ist immer wieder interessant zu sehen, welche wilden Verstecke es für Verknüpfungen gibt.
Schöne Grüße,
Martin
Es muss noch weitere Orte geben, an denen Verknüpfungen gespeichert werden.
Aus Verzweiflung habe ich alle Blätter gelöscht und trotzdem ist die Verknüpfung noch in der Tabelle.
Ein guter Trick ist ja auch das File als *.Zip umzubenennen. in der xml Struktur findet man auch tatsächlich die Verknüpfung. Nur leider nicht wo man sie dann löschen kann….
Hallo Frank,
ja, der direkte Zugriff auf die XML-Struktur wäre eine finale Option. Und wenn man wirklich weiß, dass die Verknüpfung nirgendwo gebraucht wird und man ganz sicher ist, was man tut, kann man sie auch direkt in der XML-Datei löschen. Ein Backup der Datei ist aber vorher dringend zu empfehlen!
Schöne Grüße,
Martin
Ich hätte gedacht, dass man über ein Attribut in der XML Struktur rausbekommt., wofür die Verknüpfung verwendet wird und demnach dann im Excel löschen kann. Die Üblichen verdächtigen Orte wie Namensmanager und BedingteFormatierung ist bereits vom Müll befreit.
Viele Grüße Frank
Hallo. Gibt es denn auch irgendeine Möglichkeit, die Dateien zu finden, in denen die aktuelle Datei weiterverwendet wird. Also der umgekehrte Weg. Mir passiert es oft, dass ich in einer Datei arbeite, dort Zeilen einfüge oder Spalten und später in anderen Dateien feststelle, dass sie mit dieser verknüoft waren und nun dort alles „durcheinander“ bis hin zu „unbrauchbar“ (#BEZUG) geworden ist.
LG Ute
Hallo Ute,
ja, das wäre zwar schön, ist aber leider nicht machbar. Denn die Datei kann nicht „wissen“, ob irgendwo eine andere Datei existiert, die auf sie zugreift.
Schöne Grüße,
Martin