Jemand stellte im MS Office Forum die Frage, warum Excel 2007 in bestimmten Konstellationen mit externen Daten verknüpfte Formeln immer wieder entfernt und durch Werte ersetzt hatte. Der User bemerkte auch, dass, wenn die verknüpfte Datei in einen anderen Ordner geschoben wurde, alles normal lief. Es stellte sich schließlich heraus, dass Sonderzeichen im Pfad zu verknüpften Datei zu diesem Verhalten geführt hatten. Microsoft empfiehlt grundsätzlich keine Sonderzeichen in Pfad- und Dateinamensangaben zu verknüpften Dateien zu verwenden. Trotzdem bin ich durch den Beitrag des Users im Forum neugierig geworden und habe ich es mal in Excel 2003, 2007 und 2010 getestet. Dabei bin ich zu interessanten Ergebnissen gekommen.
Zunächst habe ich mir für das Testen eine Ordnerstruktur angelegt, wie oben im Screenshot zu sehen. Nehmen wir mal an, wir hätten ein paar Ordner, die jeweils in den Pfadnamen eckige Klammern als Sonderzeichen beinhalten. Und dass sich in diesen Ordnern Excel Dateien mit einer Monatsauswertung für diverse Produkte befinden.
Eine solche einfache Monatsauswertung, die sich im Unterordner „2010 – [Reports]“ befindet, ist im oberen Screenshot abgebildet. In Spalte C sind die Bezeichnungen der Produkte enthalten, in Spalte D die Anzahl und in Spalte E der Preis pro Stück. Die Spalten habe ich ausgeblendet, denn diese sind für den Artikel nicht so relevant.
Verhalten von Excel 2003 – XLS Dateiformat
Um nun die Verknüpfungen zur Monatsauswertung herzustellen, habe ich mir in Excel 2003 die Datei „2010.xls“ zur Jahresauswertung angelegt und über Formeln Daten der noch geöffneten Monatsauswertung verknüpft.
Beispielsweise extrahiert die Formel „=[October.xls]Monat“$C$3“ den in der Monatsauswertung hinterlegten Monat. „October.xls“ ist hier der Dateiname der Monatsauswertung, „Monat“ der referenzierte Tabellenname und $C$3 die Zelle. Wird abschließend die Datei zur Monatsauswertung geschlossen, ergänzt Excel die Formel automatisch um den Pfad „F:\Links\2010 – [Reports]“ zu dieser Datei.
Als weitere Formeln zu Testzwecken habe ich die Formel SVERWEIS() und SUMMENPRODUKT() verwendet. Erstere liefert mir die Anzahl für das Produkt A aus der Monatsauswertung und zweitere die Summe der Preise, jeweils multipliziert mit der Anzahl.
Wenn beide Dateien in Excel 2003 gespeichert und geschlossen werden und anschließend nur die Jahresauswertung „2010.xls“ wieder geöffnet wird, zeigt Excel eine Nachfrage an, ob die Verknüpfungen aktualisiert werden sollen. Soweit alles bester Ordnung.
Doppelklicke ich jedoch beispielsweise die Formel zum Monat an, erscheint beim Verlassen der Zelle die Fehlermeldung „Eine Formel in diesem Arbeitsblatt enthält einen oder mehrere ungültige Bezüge. Überprüfen Sie, ob der Pfad, der Name der Arbeitsmappe und der Name oder des Bereiches oder des Zellbezuges korrekt ist.“ Wie bereits am Anfang des Artikels erwähnt, sind Sonderzeichen innerhalb der Datei- und Pfadnamen von verknüpften Dateien nicht zulässig. Excel 2003 toleriert jedoch diese Sonderzeichen, solange keine Änderungen in den Formeln stattfinden.
Das lässt sich auch relativ einfach überprüfen, indem in Excel 2003 über das Hauptmenü „Bearbeiten“ der Eintrag „Verknüpfungen“ aufgerufen wird. Wenn dann versucht wird, über die Schaltfläche „Quelle ändern“ beispielsweise die Quelle auf dieselbe Quelldatei zu setzen, erscheint ebenfalls die zuvor zitierte Fehlermeldung.
Verhalten von Excel 2010 und Excel 2007 – XLS Dateiformat
Schauen wir uns nun mal an, was passiert, wenn die Datei zur Jahresauswertung „2010.xls“ in Excel 2010 oder in Excel 2007 geöffnet wird. Zunächst erscheint wieder die Abfrage zur Aktualisierung. Und auch hier, solange nicht die verknüpften Formeln editiert werden, kann die Datei ganz normal bearbeitet werden. Sobald Änderungen gespeichert werden sollen, erhält man jedoch einen Hinweis von der Kompatibilitätsprüfung, vorausgesetzt, diese wurde nicht abgeschaltet. Diese Meldung ist zwar meines Wissens normal, trifft aber hier nicht zu und ist nicht besonders sinnvoll, denn es sind keine Formeln mit Verknüpfungen vorhanden, die länger als 255 Zeichen sind.
Wenn versucht wird, die Quelle zur verknüpften Datei zu ändern (im Ribbon bzw. Menüband zu finden unter „Daten“ und „Verknüpfungen bearbeiten“), erhält man dieselbe Fehlermeldung, wie in Excel 2003.
Verhalten von Excel 2010 und Excel 2007 – XLSX Dateiformat
Beide Dateien habe ich anschließend nochmals im Excel 2007 und Excel 2010 nativen XLSX Dateiformat angelegt und, zur besseren Unterscheidung, die Formatierung leicht geändert. Zum Anlegen der Verknüpfungen bin ich auf dieselbe Weise vorgegangen wie in Excel 2003.
Auch hier tauchten erst mal keine Probleme beim Speichern der Jahresauswertung auf, unabhängig davon ob die Monatsdatei offen war oder nicht. Und wie bei Excel 2003 erscheint die Fehlermeldung beim Verlassen der Zelle, wenn in die Zellen mit den verknüpften Formeln doppelt geklickt wird.
Jetzt wird’s jedoch interessant: denn wenn die Datei jedoch geschlossen und erneut geöffnet wird, erscheint sofort eine Reparaturnachfrage.
Das Beantworten der Nachfrage mit „Nein“ verwirft das Öffnen der Datei, ohne dass etwas Weiteres passiert. Die Auswahl von „Ja“ bewirkt, dass die Verknüpfungen einfach entfernt und durch die Werte ersetzt werden.
Das bedeutet nun, dass man im ungünstigsten Fall nicht mehr an die Formeln herankommt. Und es bedeutet auch, dass in dem Fall, wo eine Mappe aus einem Ordner mit Sonderzeichen im Pfadnamen bereits in Excel geöffnet ist und Verknüpfungen zu dieser Mappe erstellt werden, nach dem Schließen und Öffnen der Mappe mit den Verknüpfungen, letztere korrupt ist.
Übrigens, wenn ein Datei im älteren Format XLS mit Sonderzeichen in den Verknüpfungen in das modernere Format XLSX konvertiert (zum Beispiel über „Speichern unter“) wird, scheint alles normal. Spätestens beim erneuten Öffnen erscheint jedoch auch wieder die Reparaturnachfrage.
Insofern sollte der Hinweis von Microsoft, gerade bei neueren Excel Versionen, grundsätzlich keine Sonderzeichen in Pfad- und Dateinamen bei verknüpften Dateien zu verwenden, meiner Meinung nach, auch wirklich ernst genommen werden, auch wenn es zu funktionieren scheint. Es sollte auch darauf geachtet werden, keine Verknüpfungen zu bereits in Excel geöffneten Dateien zu erstellen, die aus einem Ordner mit Sonderzeichen stammen.
Ändern der Verknüpfungen in Excel 2003
Wenn es sich um Dateien im XLS-Format handelt, ist die einfachste Möglichkeit, zunächst den Ordner mit den verknüpften Dateien umzubenennen oder, je nach Fall, zu duplizieren und im Ordernamen die Sonderzeichen zu entfernen. Anschließend wären die betroffene Datei zu öffnen und im Dialog zur Bearbeitung der Verknüpfungen die neuen Quellen anzugeben. Das führt in der Regel zu einem zufriedenstellenden Ergebnis.
Manuelle Reparatur der Verknüpfungen für XLSX Dateien
Bei den Dateien im XLSX-Format gestaltet sich eine Reparatur etwas schwieriger, da ja die Nachfrage der Datei vor dem Anzeigen der Datei ausgeführt wird und keine Eingriffsmöglichkeiten bestehen.
Meine Experimente, die Datei einfach mit dem Konverter in Excel 2003 zu öffnen oder die Datei über „Daten“ und Auswahl des Eintrags „Vorhandene Verbindungen“ in eine neue Mappe zu „importieren“ waren nicht wirklich befriedigend und funktionierten gar nicht oder lieferten nur die Werte zu den Verknüpfungen in der Mappe.
Jetzt sind XLSX Dateien aber nichts anderes sind als gepackte XML Dateien und können auch von Fremdtools ausgelesen werden. Um mir die Struktur meiner fehlerhaften Excel Datei genauer anzuschauen, habe ich mir das Tool von Ted Pattison heruntergeladen. Der Package Viewer ist übrigens auch als Quellcode in der Downloaddatei verfügbar. Nach dem Entpacken des Tools in einen beliebigen Ordner und dem Aufrufen der Anwendung kann über „File – Open Package“ die Struktur einer Excel Datei komfortabel untersucht werden.
Die externe Referenz wird in der angezeigten Baumstruktur im Node „/xl/externalLinks/externalLink1.xml“ als Beziehung referenziert. In einem nächsten Schritt habe ich die XLSX Datei „2010.xlsx“ in „2010.zip“ umbenannt mit einem Entpacker in den Unterordner „2010“ entpackt, wie folgend im Screenshot zu sehen.
Im Ordner „externalLinks“ befindet sich der Unterordner „_rels“ und in diesem ist die Datei „externalLink1.xml.rels“ zu finden, die die zuvor angesprochenen Beziehungen definiert. Falls übrigens Verknüpfungen zu mehreren Dateien vorhanden sind, werden diese in durchnummerierten Dateien abgelegt. Öffnen wir einer dieser Dateien in einem normalen Texteditor, ist der Pfad mit den Sonderzeichen schnell gefunden.
Hinweis: %20 steht für ein Leerzeichen und %5b bzw. %5d für die eckigen Klammern. Den Wert „2010%20-%20%5bReports%5d/October.xlsx“ habe ich dann durch „2010-Reports/October.xlsx“ ersetzt und die Datei abgespeichert. Danach braucht der Inhalt im Ordner „2010“ wieder als neues Zip-Archiv gepackt werden. Und schließlich ist das neue Zip Archiv wieder in XLSX umzubenennen. Wenn dann diese Datei in Excel 2010 bzw. Excel 2007 wieder geöffnet wird, siehe da, ist die Fehlermeldung verschwunden und alle Formeln sind erhalten geblieben.
Abschließend möchte ich noch anmerken, dass die oben beschriebene Methode bei mir prima funktioniert hatte, auch mit mehreren Verknüpfungen. Trotzdem übernehme ich keine Garantie, dass die Methode für jeglichen Fall geeignet ist. Und, ein Backup der Dateien, ist vor dem Experimentieren natürlich Pflicht.
Nachfolgend ein paar Links mit weiterführender Literatur zum Thema „Office XML Dateiformat“ und dem Tool von Ted Pattison.
