Konfigurationsdatei in Excel (Config)

In einem älteren Beitrag habe ich einst aufgeführt, dass ein Blatt "Parameter" zum professionellen Präsentieren von Excel-Tabellen und -Reports hilfreich ist (siehe diesen Blogeintrag). Wichtige Angaben oder Inhalte von Drop-Down-Listen werden als Einträge auf einem (ausgeblendeten) Tabellenblatt hinterlegt. Eine Konfigurationsdatei (Config) ist dem sehr ähnlich, die hier vorgestellten Ansätze könntest Du durchaus auch in einem "Parameter"-Sheet hinterlegen.

Eine Konfigurationsdatei dient dazu, an einem zentralen Ort bestimmte Einstellungen vorzunehmen, es ist aus Wartungssicht deshalb deutlich komfortabler, als Anpassungen in diversen Formeln, Tabellenblättern oder VBA-Codezeilen vorzunehmen. Insbesondere bei grösseren Dateien oder Skripten ist es je länger je unübersichtlicher, eine Config-Datei ist dort empfohlen. Man bindet also möglichst viele übergreifenden Angaben, die allenfalls ändern könnten (manuell geändert werden müssen) in eine Konfig-Datei ein. Ein weitergehendes Beispiel könnten Dateipfade sein - respektive dynamische Pfade und Ordnerverzeichnisse. Stelle Dir vor, Du teilst Deine Datei mit einigen Arbeitskollegen. Je nach dem, wenn Ihr das File lokal öffnet, befindet sich die Datei jedoch in einem anderen Verzeichnis. Wenn man neben dem aktuellen File auch eine zweite Datei im Stammverzeichnis ansprechen möchte (z.B. eine Input- oder Vormonats-Datei), dann führen starre Pfade zu Fehlern. Im Beitrag "Dateipfad mit einer Formel darstellen" sind die Grundlagen aufgeführt, wie ein dynamischer Pfad hinterlegt werden kann.

Hier führe ich Dir eine Beispieldatei auf, die ein Blatt "Config" enthält:

Config- Konfigurations-Datei.xlsx

Es werden Variablen definiert, die allerdings im Rahmen dieses Blogs nicht weiter verwendet werden. Du könntest diese entsprechend einem Diktionär (Variable des Typs "Dictionary") einlesen - insbesondere im VBA-Kontext. Du kannst aber auch selber simplere Definitionen mit Zellbezügen anstellen. Relevant für den Excel-Teil an und für sich ist der Inhalt in der Spalte B: Dort sind unterschiedliche Varianten der Datei oder des Stammverzeichnisses (Root Path) direkt ausgelesen. Je nach dem, wo die Datei abgespeichert ist, sind andere Werte (und dort relevante Gegebenheiten) eingetragen.

Dateipfad mit einer Formel darstellen

Den Pfad einer Datei darzustellen, kennen die meisten womöglich mittels der Kopf- und Fusszeilen-Funktion. Ich zeige Dir in diesem Beitrag auf, wie Du dies auch mittels einer Formel machen kannst, damit Du den Pfad der aktuellen Datei in einer beliebigen Zelle in Excel ausgeben kannst.

Kopf- und Fusszeile

Bekannter ist die Vorgehensweise, den aktuellen Dateipfad in die Kopf- oder Fusszeile zu integrieren. Dazu gibt es eine vorgefertigte Auswahlmöglichkeit in den Kopf- und Fusszeilentools, die folgendes Resultat liefert:

dateipfadkopfzeile

Dies bedeutet jedoch, dass Du den Pfad immer an einer bestimmten Stelle (eben zu oberst oder zu unterst im Dokument) angezeigt haben wirst. Im nächsten Abschnitt findest Du, mithilfe welcher Formel Du diese Information in jeder beliebigen Zelle hinterlegen kannst!

Die Formel =ZELLE

Wie die Überschrift bereits andeutet, bietet die Formel "=ZELLE" die entsprechende Möglichkeit. Wendet man die Formel an, kommt eine Auswahlmöglichkeit:

dateipfadzelle

Wähle hier "dateiname" und schliesse die Formel ab. Vollständig in einer beliebigen Zelle steht also:

=ZELLE("dateiname")

Und das Resultat ist der Pfad (inklusive Dateiname/Tabellenblatt). Beachte: Es wird nur etwas angezeigt, wenn Deine Excel-Datei auch einen Speicherort hat. Öffnest Du einfach die Applikation und versuchst dies aus (Mappe1), wird es nicht funktionieren. Speichere die Datei ab, aktualisiere die Formel und Du erhältst den Output (bspw.: "C:\Users\Roman\Desktop\[MeineDatei.xlsx]Tabelle1". Möchtest Du nun NUR den Dateipfad angezeigt bekommen, braucht es einige Ergänzungen. Die fertige Formel lautet:

=LINKS(ZELLE("dateiname");SUCHEN("[";ZELLE("dateiname"))-2)

Dies ergibt: "C:\Users\Roman\Desktop". Wir suchen also einerseits nach dem Zeichen "[" - dieses steht für den Dateinamen. Vom gesamten Konstrukt nehmen wir alle Zeichen von links bis zu dieser eckigen Klammer (abzüglich 2: Eben die Klammer und den letzten Backslash "\").

P.S.: Hängst Du hinter "dateiname" noch einen Bezug in eine andere Datei/Zelle, dann wird Dir davon der Pfad/Dateiname angezeigt!

Mittels Formel den Tabellenblatt-Name ausgeben

Mit einer Zusammensetzung von diversen Formeln kann der Name des aktuellen Tabellenblatts ausgegeben werden. Die komplette Formel lautet:

=TEIL(ZELLE("dateiname");SUCHEN("]";ZELLE("dateiname"))+1;100)

Das Ergebnis dieser Formel ist bei meiner Datei: "MeinTabellenblatt". [Beachte, dass dies nur bei bereits abgespeicherten Dateien funktioniert.]

Die drei verschiedenen verwendeten Formeln bewirken folgendes; beginnen wir mit "=ZELLE":

In dieser Funktion wird zuerst nach dem "Infotyp" gefragt - wir wollen, dass der Dateiname der aktiven Zelle ausgegeben wird. Das Ergebnis: C:\....\...\[MeineDatei.xlsx]MeinTabellenblatt. Nicht zwingend ist eine "Referenz" nach dem Infotyp als zweites Argument innerhalb der Formel. Man könnte da auf eine andere Zelle verweisen; auch auf ein anderes Tabellenblatt - und könnte so schlussendlich mit leichtem Modifizieren der obigen Formel den Namen eines anderen Tabellenblattes ausgeben lassen.

Die Formel "=SUCHEN" haben wir im Beitrag "Textfragmente aus einer Zelle ziehen" genauer betrachtet: Es wird damit die Position des Suchtextes (in unserem Beispiel die eckige Klammer "]" nach dem Dateinamen) ausgemacht, ab welcher (+1) die "=TEIL"-Funktion einen entsprechenden Text ausgibt. Die 100 zum Schluss der obigen Formel ist lediglich eingebaut, dass bestimmt genügend Zeichen ausgegeben werden (also ein Tabellenname mit bis zu 100 Zeichen). Diese hohe Zahl ist unkritisch, da keine ungewünschten Leerzeichen an den Text angefügt werden.