Excel-Alptraum externe Verknüpfungen: Ein Überblick über ihre Verstecke 4

Die meisten Excel-Anwender sind wahrscheinlich irgendwann einmal damit konfrontiert worden: Beim Öffnen einer Datei kommt eine Sicherheitswarnung und der Hinweis auf externe Bezüge. In manchen Fällen hat man diese Bezüge selbst und ganz bewusst gesetzt. Aber falls nicht, dann geht jetzt der „Spaß“ bei der Suche danach los.

Leider sind solche externen Verknüpfungen nicht immer so einfach zu finden. Wenn du deswegen auch schon einmal am Rande des Wahnsinns standest (oder sogar einen Schritt darüber hinaus warst), dann ist dieser Artikel für dich.

Wo man diese Übeltäter finden kann und wie man sie wieder los wird, erfährst du in diesem Beitrag.

Zu diesem Thema hatte ich vor längerer Zeit schon zwei Blogartikel veröffentlicht:
Die Suche nach der externen Verknüpfung
Fantastische Verknüpfungen und wo man sie in Excel findet

An den Leserreaktionen habe ich gesehen, wie groß das Interesse daran ist. Und in den vielen Kommentaren zu den Beiträgen haben die Leser teilweise weitere Verstecke beschrieben, wo sich solche Verknüpfungen und externe Bezüge noch überall finden lassen.

Der heutige Artikel soll die Quintessenz aus den beiden Blogbeiträgen und Leserkommentaren bilden. Ich werde daher bei den einzelnen Verstecken nicht mehr auf alle Details eingehen, sondern nur einen komprimierten Überblick geben.

Wie externe Verknüpfungen entstehen

Es ist leider durchaus üblich, dass in Formeln auf andere Arbeitsmappen verwiesen wird. Excel macht es einem dabei auch leicht, wenn man zwei Dateien geöffnet hat und bei der Eingabe einer Formel einfach mit der Maus eine Zelle oder einen Bereich in der anderen Datei markiert. Schon ist ein externer Bezug hergestellt:

So entsteht ein externer Bezug

Auf den ersten Blick mag das auch wie eine gute Idee erscheinen. Man hat vielleicht eine Referenzdatei, in der man bestimmte Daten zentral pflegt, wie zum Beispiel eine Preisliste. Und darauf greift man dann eben aus verschiedenen anderen Arbeitsmappen heraus zu. Zentrale Datenhaltung ist ja grundsätzlich auch ein empfehlenswertes Konzept.

Der Ärger ist aber vorprogrammiert, denn dieses Konstrukt kann einem sehr schnell um die Ohren fliegen:

• die Referenzdatei wird umbenannt, gelöscht oder in ein anderes Verzeichnis verschoben,
• die Struktur der referenzierten Tabelle ändert sich (z.B. durch eingefügte oder gelöschte Spalten),
• das Arbeitsblatt wird umbenannt,
• der Anwender, für den die Auswertung erstellt wurde, hat gar keinen Zugriff auf die Datei….

… um nur einige Beispiele zu nennen.

Darüber hinaus entstehen viele externe Verknüpfungen aus Unwissenheit und ohne, dass man es überhaupt bemerkt geschweige denn beabsichtigt. Etwa weil ein ganzes Tabellenblatt oder Teile davon von einer Datei in eine andere kopiert werden.

Daher meine ganz persönliche Meinung:

Externe Verknüpfungen sind ein Werk des Teufels!

Wer immer noch glaubt, dass es eine gute Idee ist, der möge sich noch diesen Blogartikel anschauen:
Externe Tabellenbezüge: Das solltest du wissen!
Darin beschreibe ich, wie externe Bezüge unter bestimmten Umständen dazu führen können, dass ungewollt und unbemerkt vertrauliche Daten abfließen können.

Als Ersatz für die allermeisten externen Verknüpfungen bietet sich Power Query an. Damit lassen sich Daten komfortabel importieren, irgendwo innerhalb der aktuellen Arbeitsmappe ablegen und weiterverarbeiten.

Natürlich kann auch hier die Datenquelle umbenannt, verschoben oder gelöscht werden. Aber man hat zumindest noch den letzten Datenstand importiert und kann damit problemlos weiterarbeiten.

Ausgangspunkt für die Suche

Schauen wir uns also erst einmal an, wie man mit der eingangs genannten Sicherheitswarnung umgehen kann und wo man mit der Suche am besten startet.

Sicherheitswarnung beim Öffnen der Datei

Verknüpfungen können aktualisiert werden – oder auch nicht

Der erste Weg bei der Suche sollte immer das Menü „Daten“ sein. Abhängig von der Excel-Version gibt es hier den Punkt „Workbook Links“ oder „Verknüpfungen bearbeiten“:

In Microsoft 365: Workbook Links

In anderen Excel-Versionen: Verknüpfungen bearbeiten

In Microsoft 365: Arbeitsbereich mit Links zu Arbeitsmappen

In anderen Excel-Versionen: Fenster Verknüpfungen bearbeiten

Hier lässt sich die Quelle ändern oder öffnen. Oder man kann die Verknüpfung auch direkt löschen bzw. unterbrechen. Das führt dazu, dass alle Formeln, welche auf die externe Datei verweisen, durch statische Werte ersetzt werden.

Wer vorher wissen möchte, wo genau in der Arbeitsmappe sich diese Formeln befinden, kann dazu die normale Suchfunktion in Excel verwenden. Im Feld „Suchen nach“ gibt man den Pfad oder Dateinamen zur externen Datei ein. Zusätzlich wählt man im Dropdown-Feld „Durchsuchen“ den Eintrag „Arbeitsmappe“ aus und klickt dann auf „Alle suchen“. Damit werden alle sichtbaren Arbeitsblättern gesucht und die betroffenen Formelzellen aufgelistet:

Verknüpfungen mit der Suchen-Funktion finden

Dazu noch ein Hinweis des Leser Andreas K. (danke dafür):
Man sollte vor der Suche einen etwaigen Blattschutz aufheben. In seinem Fall wurde im geschützten Blatt die Verknüpfung weder gefunden noch konnte sie gelöscht werden.

Das Fenster, in dem die externen Verknüpfungen bearbeitet werden können, ist allerdings nicht sehr übersichtlich. Falls dort sehr viele verschiedene externe Dateien angezeigt werden, lässt sich mit einem Trick auch eine Liste dieser Dateien samt ihrer Pfade direkt in einer Tabelle ausgeben. Wie das funktioniert, habe ich in diesem Artikel beschrieben:
Liste der Dateiverknüpfungen

Achtung:
Ausgeblendete Blätter werden von der Suche ausgenommen. Das gilt auch für Arbeitsblätter, die im VBA-Editor als „VeryHidden“ deklariert sind.

Aber nicht immer ist es so einfach. Manchmal werden Verknüpfungen angezeigt, die sich nicht löschen oder unterbrechen lassen. Dann müssen wir uns auf eine unter Umständen mühsame Suche einstellen. Hier folgt ein Überblick der zumindest allermeisten Verstecke.

Fiese Verstecke – ein Überblick

Die nachfolgende Auflistung soll Anregungen geben, wo man überall nach externen Bezügen suchen kann. Für weitere Details verweise ich bei Interesse an dieser Stelle gerne auf die beiden oben genannten Artikel.

Kamera-Tool / Verknüpfte Bilder

Excel bietet die sehr schöne Möglichkeit, dynamische Screenshots auf andere Tabellenbereiche einzufügen. Das ist zum Beispiel sehr praktisch, wenn man ein Dashboard erstellt dort die Ergebnisse aus einem anderen Tabellenblatt anzeigen möchte. Dabei wird der Quellbereich einfach markiert und mit Strg+C in die Zwischenablage gelegt. Im Zielblatt (z.B. auf dem Dashboard), lässt sich dann per Rechtsklick und „Inhalte einfügen…“ eine verknüpfte Grafik einfügen.

Verknüpfte Grafiken

Diagramme

Ähnlich verhält es sich mit Diagrammen / Charts. Diese verweisen ja immer auf einen Zellenbereich, in dem die zugrunde liegenden Daten liegen. Wird nun das Diagramm in eine andere Arbeitsmappe kopiert, entsteht sofort eine externe Verknüpfung.

Noch ein Hinweis eines Lesers:
Gerne werden mal Diagramme (oder andere grafischen Objekte) beim Verschieben versehentlich kopiert und danach direkt übereinander gelegt. Das heißt, über dem Diagramm liegt nochmal ein identisches Diagramm – und selbstverständlich haben beide ihre eigenen Quellbezüge…

Formularsteuerelemente

In den Entwicklertools gibt es die sogenannten Formularsteuerelemente. Damit lassen sich beispielsweise Ankreuzkästchen, Optionsfelder und andere Schaltflächen einfügen. In aller Regel werden diese Steuerelemente dann mit einer VBA-Routine („Makro“) verbunden, um bestimmte Funktionen auszuführen. Kopiert man jetzt ein Arbeitsblatt mit solchen Steuerelementen in eine andere Datei (oder auch nur einzelne Steuerelemente), dann entsteht unbemerkt auch eine Verknüpfung zum VBA-Modul in der Ursprungsdatei.

Über einen Rechtsklick auf das Steuerelement und „Makro zuweisen…“ kann man sich diese Verknüpfung anzeigen lassen – und bei Bedarf korrigieren.
(Der Leser Martin Bieberstein gab den generellen Hinweis auf externe Makros und Subroutinen, danke dafür!)

Formen und Schaltflächen

Neben klassischen Formularsteuerfeldern lassen sich auch ganz normale Formen (Menü „Einfügen | Formen“ als Schaltflächen verwenden und mit Makros verbinden. Die Problematik ist dabei identisch zu dem, was ich bei den Formularsteuerelementen geschrieben habe: beim Kopieren wird auf Makros und Subroutinen in der Quelldatei verwiesen.

Gleiches gilt auch für selbst erstellte AddIns mit ausgelagerten benutzerdefinierten Funktionen (Danke für diesen Hinweis an Tino Becker).

Eingebettete Objekte

Im Rahmen von Dokumentationen werden gerne externe Objekte, wie Word- oder PDF-Dokumente in Excel eingefügt. Diese werden dann als kleines Symbol angezeigt und können bei Bedarf mit einem Doppelklick geöffnet werden können. Je nachdem, wie das Objekt eingefügt wurde, kann dabei auch ein externer Bezug entstehen.

Pivot-Tabellen

Wer liebt sie nicht – Pivot-Tabellen 🙂
Da auch sie zwangsläufig immer auf einen Tabellenbereich mit den Quelldaten verweisen, führt das Kopieren einer Pivot-Tabelle oder eines Arbeitsblatts mit einer Pivot-Tabelle zu einer externen Verknüpfung. Dummerweise wird diese aber nicht in dem Fenster „Verknüpfungen bearbeiten“ angezeigt. Man bemerkt sie erst, wenn man die Pivot-Tabelle aktualisieren möchte und eine entsprechende Fehlermeldung auf eine fehlende Datenquelle erscheint.

Benannte Bereiche

Wird auch sehr gerne genommen:
Man vergibt einen Namen für einen Zellbereich, um diesen Namen später in Formeln zu verwenden. Stichwort „Namensmanager“. Kopiert man nun ein Arbeitsblatt, in dem sich eine Formel mit einem solchen Namen befindet, in eine neue Datei, dann entsteht – du ahnst es bereits – eine externe Verknüpfung.

Datenüberprüfung

Sehr häufig tritt dieser Fall auf: Man hat Datenprüfung eingerichtet, um dem Anwender über ein Dropdownfeld die Eingabe zu erleichtern. Die über die Datenprüfung angebotenen Werte stammen natürlich aus einer Liste irgendwo in der Arbeitsmappe. Kopiert man das Blatt mit den Dropdownfeldern in eine andere Arbeitsmappe, erzeugt man automatisch eine externe Verknüpfung.

Bedingte Formatierungen

Ganz fies und schwer zu finden sind externe Bezüge, die über bedingte Formatierungen entstanden sind. In diesen Regeln lässt sich so gut wie alles per Formel festlegen und es wird praktisch immer mit Zellbezügen gearbeitet. Das ist in vielen Fällen auch kein Problem, aber manchmal können auch dabei externe Bezüge entstehen.

Wenn also ganz hartnäckig eine Meldung zu externen Verknüpfungen angezeigt wird, sollte man vielleicht auch die bedingten Formatierungsregeln prüfen.

Formatierte Tabellen

Dieser Fall ist wirklich hundsgemein und zum Glück sehr exotisch – aber er kann eben trotzdem eintreten. Der Hinweis dazu kam von einem Leser (Danke an Markus!).

Er hatte eine formatierte („intelligente“) Tabelle erstellt und dort Formeln verwendet, die wohl auf Bereiche außerhalb dieser Tabelle verwiesen haben. Nun sollte diese formatierte Tabelle später als leere Vorlage in anderen Arbeitsmappen dienen und war dorthin kopiert worden.

Wenn man nun eine solche intelligente Tabelle komplett leert, bleiben im Hintergrund aber etwaige zuvor enthaltene Formeln erhalten. Man merkt das schnell, sobald man eine neue Zeile einfügt, um Daten einzugeben und plötzlich in den relevanten Spalten die Formeln auftauchen – und auf die ursprünglichen Quelldaten verweisen, die im Fall von Markus in der anderen Datei lagen!

Ausgeblendete Blätter („VeryHidden“)

Die Verstecke sind aber nicht immer so kompliziert zu finden. Manchmal befinden sie sich einfach in ganz normalen Formeln, die nur in ausgeblendeten Arbeitsblättern liegen. Wie oben erwähnt, werden ausgeblendete Blätter bei der Suche ausgeschlossen. Man muss sie also zuvor explizit wieder einblenden, damit sie berücksichtigt werden.

Etwas weniger bekannt und von VBA-Programmierern gerne genutzt ist die Möglichkeit, ein Arbeitsblatt „sicher“ auszublenden. Ein spezielles Attribut „VeryHidden“ verhindert, dass ein solches Arbeitsblatt in der Liste der ausgeblendeten Blätter überhaupt angezeigt wird. Um es sichtbar zu machen, muss der VBA-Editor mit Alt + F11 geöffnet werden.

Dann wird links oben im Verzeichnisbaum das Arbeitsblatt markiert und unten in den Eigenschaften das Attribut „Visible“ von „2 – xlSheetVeryHidden“ auf „-1 xlSheetVisible“ geändert:

Im VBA-Editor: Sehr versteckte Blätter einblenden

Sonderfall: Power Query

Wenn du schon länger diesen Blog liest, dann weißt du vermutlich, dass ich Power Query liebe! Allerdings kann es auch hier passieren, dass eine Art von externen Verknüpfungen entsteht. Hat man nämlich eine Power Query-Abfrage in ein Tabellenblatt geladen und kopiert dieses Blatt in eine andere Excel-Mappe, so kopiert man automatisch im Hintergrund diese Abfrage mit. Und auch alle anderen Abfragen, die irgendwie davon abhängig sind.

Das muss nicht zwingend ein Problem sein, aber es führt auf jeden Fall zu unerwarteten Sicherheitswarnungen („Externe Datenverbindungen wurden deaktiviert“) beim nächsten Öffnen der Datei. Und wenn man nun auf die Idee kommt, die geladenen Daten per Rechtsklick zu aktualisieren, können unter Umständen weitere Fehlermeldungen auftauchen.

Hier haben wir es meistens nicht mit klassischen externen Verknüpfungen zu tun. Aber man sollte durchaus ein Auge darauf haben, wenn man Blätter kopiert, hinter denen sich Power Query-Abfragen verbinden.

Wenn alles nichts hilft

Eines meiner Prinzipien hier auf dem Blog ist, dass ich keine Tipps zu VBA gebe. Da sich aber ein paar Leser die Mühe gemacht haben, in den Kommentaren zu den anderen Artikeln ein paar hilfreiche VBA-Codeschnippsel zu diesem Thema zur Verfügung zu stellen, möchte ich dir diese auch nicht vorenthalten.

Die Anwendung erfolgt aber immer auf eigene Gefahr und ich bitte um Verständnis, dass ich keinerlei Support anbieten oder Fragen dazu beantworten werde.

VBA-Codeschnippsel

Der Leser Klaus Kurras hat in den alten Kommentaren auf einen kurzen Code zum Entfernen von externen Verknüpfungen aufmerksam gemacht:
Externe Verknüpfungen entfernen (externer Bezug)

Der Leser Christian Fette hatte in einem Kommentar darauf aufmerksam gemacht, dass es auch versteckte Namen gibt, die man im Namensmanager nicht bearbeiten kann und die daher auch nicht so leicht zu finden sind. Das wird mit VBA möglich. Und auf den Microsoft-Support-Seiten wird ein VBA-Code angeboten, um solche ausgeblendeten Namen zu entfernen:
Ausgeblendete Namen entfernen (externer Bezug)

Der Leser Uwe hat in den alten Kommentaren folgenden Code zur Verfügung gestellt und noch diese Bemerkungen ergänzt:
„Muss man öfter hintereinander laufen lassen, denn es stoppt beim ersten gefundenen Ergebnis, damit man es gleich reparieren kann. Allerdings weiß ich nicht, welche Diagramm-Elemente noch Verknüpfungen enthalten können. Die müsste man dann auch ansprechen und durchsuchen.“
Sub Diagramm_Titel_mit_externer_Verknüpfung_suchen()
Dim Blatt As Worksheet, c As ChartObject
For Each Blatt In ActiveWorkbook.Worksheets
Blatt.Activate
For Each c In ActiveSheet.ChartObjects
c.Activate
If InStr(ActiveChart.ChartTitle.Formula, "[") > 0 Then
MsgBox "Im Blatt " & Blatt.Name & ":" & vbCr & _
"Titel: " & ActiveChart.ChartTitle.Caption & vbCr & _
"enthält Formel: " & vbCr & ActiveChart.ChartTitle.Formula
Exit Sub
End If
Next c
Next Blatt
End Sub


Und noch ein weiterer Schnippsel von Uwe, welches Schaltflächen mit hinterlegten Makros finden soll. Per MsgBox wird das Tabellenblatt des Buttons, die Button-Beschriftung und die externe Datei ausgegeben. Buttons ohne Makro bzw. mit lokalem Makrobezug werden ignoriert.
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

 
An dieser Stelle möchte ich mich auch bei allen nicht genannten Lesern bedanken, die in den Kommentaren zu den beiden anderen Verknüpfungsartkeln so zahlreich ihre Hinweise, Ideen und Erfahrungen eingebracht haben. Vielen Dank an euch alle!

Kennst du noch weitere Verstecke, in denen sich externe Verknüpfungen befinden können? Dann lass es uns gerne in den Kommentaren wissen.

Das könnte dich auch interessieren:

DIE Anlaufstelle rund um Excel & Co.

Blitzschnell mit der Tastatur

Neuberechnung clever nutzen

Dateneingabe für Faule