Relative und absolute Hyperlinks in Excel

Ein Arbeitskollege kam neulich mit einem Problem zu mir. Er hatte eine Excel-Datei mit mehreren Hyperlinks auf verschiedene Exceldateien. Die Hyperlinks wurden mit der Funktion HYPERLINKS dynamisch aus verschiedenen Strings erstellt. Dadurch waren die Hyperlinks aber absolut und nicht mehr relativ (Für mit Einfügen->Hyperlinks definierte Hyperlinks gilt dies nicht!). Ein Verschieben der Ordnerstruktur (oder auch ein Umbennen) führt zu der Fehlermeldung:

Die angegebene Datei konnte nicht geöffnet werden.

Was tun?

Ausgehend von der Funktion =ZELLE("Dateiname",E2), mit der der gesamte Dateiname inklusive Pfad ermittelt werden kann, habe ich verschiedene Lösung zusammengestellt. Im wesentlichen geht es immer darum die eckige Klammer [ im Dateinamen zu finden und so den Pfad zu bestimmen.

relative und absolute Links

  E
1 Pfad der Datei + Dateiname
2 D:\eigene Dateien\Programmierung\Excel\relative und absolute Links\[Zusammenfassung.xls]relative und absolute Links
3 Auslesen des Dateipfads + Name mit der Funktion ZELLE
4  
5 Pfad der Datei, einfache Funktion
6 D:\eigene Dateien\Programmierung\Excel\relative und absolute Links\
7 Auslesen des Dateipfads + Name mit der Funktion ZELLE + bei erster eckiger Klammer [ abschneiden
8  
9 Pfad der Datei, bessere Funktion mit Arrayformel
10 D:\eigene Dateien\Programmierung\Excel\relative und absolute Links\
11 Auslesen des Dateipfads + Name mit der Funktion ZELLE + bei letzter eckiger Klammer [ abschneiden
12  
13 Pfad der Datei, bessere Funktion mit Arrayformel und als Name definiert
14 D:\eigene Dateien\Programmierung\Excel\relative und absolute Links\
15 Definiere Pfad als = "Auslesen des Dateipfads + Name mit der Funktion ZELLE + bei letzter eckiger Klammer [ abschneiden"
16 (Namen können mit Einfügen -> Namen -> Definieren hinzugefügt werden)

Formeln der Tabelle
Zelle Formel
E2 =ZELLE("Dateiname",E2)
E6 =LINKS(ZELLE("Dateiname",E6),FINDEN("[",ZELLE("Dateiname",E6))-1)
E10 {=LINKS(ZELLE("Dateiname",E10),MAX(WENN(RECHTS(LINKS(ZELLE("Dateiname",E2),ZEILE(A:A)),1)="[",ZEILE(A:A),0))-1)}
E14 =Pfad
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Wenn nun der Pfad der aktuellen Datei bekannt ist kann mit der Funktion HYPERLINKS ein relativer Pfad angegeben werden. Besonders schön ist diese Lösung wenn zuvor ein Name definiert wurde (s.o.).

relative und absolute Links

  E F G H I J K L M
19 Quelldateien   relativer Link, fest   absoluter Link, Funktion mit Text   relativer Link, bessere Funtion mit Arrayformel   relativer Link, bessere Funtion mit Arrayformel und Name definieren
20 Quelle 1.xls   Quelle 1.xls   Quelle 1.xls   Quelle 1.xls   Quelle 1.xls
21 Quelle 2.xls   Quelle 2.xls   Quelle 2.xls   Quelle 2.xls   Quelle 2.xls
22 Quelle 3.xls   Quelle 3.xls   Quelle 3.xls   Quelle 3.xls   Quelle 3.xls
23                  
24     Ein fest angegebener Link ist relativ!   Ein mit der Funktion HYPERLINK angegebener Link ist "absolut" wenn ein konstanter String angegeben wird.   Ein mit der Funktion HYPERLINK angegebener Link ist "relativ" wenn ein dynamischer String angegeben wird.   Ein mit der Funktion HYPERLINK angegebener Link ist auch "relativ" wenn ein dynamischer String über einen definierten Namen angegeben wird.

Formeln der Tabelle
Zelle Formel
E20 =A20&B20&C20
I20 =HYPERLINK("C:\Dokumente und Einstellungen\Tobias\Desktop\relative Links mit Excel\" & E20,E20)
K20 {=HYPERLINK(LINKS(ZELLE("Dateiname",E10),MAX(WENN(RECHTS(LINKS(ZELLE("Dateiname",E2),ZEILE(A:A)),1)="[",ZEILE(A:A),0))-1) & G20,G20)}
M20 =HYPERLINK(Pfad & I20,I20)
E21 =A21&B21&C21
I21 =HYPERLINK("C:\Dokumente und Einstellungen\Tobias\Desktop\relative Links mit Excel\" & E21,E21)
K21 {=HYPERLINK(LINKS(ZELLE("Dateiname",E14),MAX(WENN(RECHTS(LINKS(ZELLE("Dateiname",E4),ZEILE(A:A)),1)="[",ZEILE(A:A),0))-1) & G21,G21)}
M21 =HYPERLINK(Pfad & I21,I21)
E22 =A22&B22&C22
I22 =HYPERLINK("C:\Dokumente und Einstellungen\Tobias\Desktop\relative Links mit Excel\" & E22,E22)
K22 {=HYPERLINK(LINKS(ZELLE("Dateiname",A19),MAX(WENN(RECHTS(LINKS(ZELLE("Dateiname",E5),ZEILE(A:A)),1)="[",ZEILE(A:A),0))-1) & G22,G22)}
M22 =HYPERLINK(Pfad & I22,I22)
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Die Beispielarbeitsmappe mit allen Formeln gibt es natürlich auch hier zum Download.

AnhangGröße
relative und absolute Links.zip8.19 KB
Your rating: Keine Average: 4.4 (8 votes)