Hyperlinks per VBA auslesen

Dieser Beitrag zeigt, wie Sie Hyperlinks per VBA auslesen und effizient dokumentieren. Sie erhalten eine klare Schritt-für-Schritt-Anleitung, zwei sofort nutzbare Makros für Excel sowie praxisnahe Hinweise, um typische Fehler zu vermeiden. So gewinnen Sie Transparenz über alle Linkziele in einem Arbeitsblatt oder in ausgewählten Bereichen und können E-Mail-Adressen komfortabel bereinigen.

Was bedeutet Hyperlinks per VBA auslesen

Beim Auslesen von Hyperlinks per VBA werden die Linkziele aus einem Arbeitsblatt automatisiert ermittelt und beispielsweise in einer separaten Spalte aufgelistet. Dies ist hilfreich für Dokumentation, Qualitätsprüfungen und Migrationen von Arbeitsmappen. Das hier gezeigte Vorgehen liest alle Hyperlinks eines Arbeitsblatts aus und schreibt die gefundenen Adressen in eine Zieltabelle. Zusätzlich wird gezeigt, wie sich bei E-Mail-Links das Präfix mailto entfernen lässt.

Voraussetzungen und Vorbereitung in Excel

Aktivieren Sie in Excel die Ausführung von Makros und öffnen Sie den VBA-Editor mit Alt und F11. Fügen Sie über Einfügen und Modul ein neues Standardmodul hinzu. Kopieren Sie den gewünschten Makrocode in dieses Modul. Speichern Sie die Arbeitsmappe im Makroformat XLSM. Stellen Sie sicher, dass Sie auf dem richtigen Arbeitsblatt arbeiten, da die gezeigten Makros auf das erste Arbeitsblatt verweisen.

Schritt-für-Schritt: Hyperlinks per VBA auslesen und in Spalte B protokollieren

Das folgende Makro durchläuft die Hyperlinks-Sammlung des ersten Arbeitsblatts und schreibt die Zieladressen der gefundenen Hyperlinks zeilenweise in die Spalte B. Startpunkt ist die Zeile 1. Damit erhalten Sie eine vollständige Liste aller Hyperlink-Adressen des Blatts in einer kompakten Übersicht.

{code}
Public Sub Hyperlink1()
'Auslesen der Hyperlink-Adresse
Dim hlink As Hyperlink
i = 1
For Each hlink In Sheets(1).Hyperlinks
Sheets(1).Cells(i, 2).Value = hlink.Address
i = i + 1
Next hlink
End Sub

{lang}vb{highlight}{end-code}

Hinweis zur Wirkungsweise: Das Makro liest alle Hyperlinks des gesamten ersten Arbeitsblatts aus. Wenn die Linkliste nur für Zellen einer bestimmten Spalte wie Spalte A benötigt wird, sollte der Quelldatenbereich vorab eingegrenzt und nur die relevanten Zellen mit tatsächlichen Hyperlinkobjekten verwendet werden. Die Ausgabe beginnt in Zelle B1. Vorhandene Inhalte in Spalte B werden dabei überschrieben, planen Sie daher eine leere Zielsäule ein.

E-Mail-Hyperlinks auslesen und mailto entfernen

Für E-Mail-Links lässt sich die angezeigte Adresse häufig besser weiterverarbeiten, wenn das Präfix mailto entfernt wird. Das folgende Makro liest die Hyperlink-Adressen wie oben aus und entfernt anschließend den Präfixteil, damit nur die reine E-Mail-Adresse in Spalte B steht.

{code}
Public Sub Hyperlink2_Mailadresse()
Dim hlink As Hyperlink
i = 1
'Auslesen der Hyperlink-Adresse
For Each hlink In Sheets(1).Hyperlinks
mail = hlink.Address
mailneu = Right(mail, Len(mail) - 7)
Sheets(1).Cells(i, 2).Value = mailneu
i = i + 1
Next hlink
End Sub

{lang}vb{highlight}{end-code}

Die Funktion Right entfernt hier die ersten sieben Zeichen des Hyperlink-Ziels, also den Präfix mailto mit Doppelpunkt. Dieses Vorgehen setzt voraus, dass es sich wirklich um E-Mail-Hyperlinks handelt. Sind im Arbeitsblatt auch andere Links vorhanden, sollten Sie die Ergebnisse prüfen, damit nur E-Mail-Adressen in die Liste gelangen.

Praxisbeispiele für das Auslesen von Hyperlinks per VBA

Dokumentation und Audit. Erstellen Sie automatisch ein Linkverzeichnis, um die Nachvollziehbarkeit in Berichten oder Dashboards zu erhöhen. Linkprüfung. Exportieren Sie alle Adressen und prüfen Sie diese anschließend mit externen Tools, um fehlerhafte oder veraltete Ziele zu identifizieren. Migration und Bereinigung. Verschaffen Sie sich vor einer Umstrukturierung der Datei einen Überblick über alle externen Verweise, um diese gezielt zu aktualisieren.

Typische Fehlerquellen und wie Sie diese vermeiden

1. bezieht sich auf das erste Arbeitsblatt der Arbeitsmappe. Wenn Ihre Daten auf einem anderen Blatt liegen, passen Sie die Zielausgabe im Blatt entsprechend an und prüfen Sie die Ergebnisse. Überschreiben von Daten. Die Ausgabe beginnt in Spalte B ab Zeile
2. Achten Sie darauf, dass dort keine wichtigen Inhalte stehen. Inhalte in Textfeldern und Formen. Die Hyperlinks-Sammlung umfasst auch Links in Formen, Bildern oder Diagrammen. Prüfen Sie die Liste, wenn auf dem Blatt grafische Elemente verwendet werden. E-Mail-Erkennung. Das Entfernen des Präfix mailto funktioniert nur bei entsprechenden E-Mail-Links. Mischen sich Weblinks unter die E-Mail-Adressen, sollten Sie die extrahierten Werte prüfen.

Schritt-für-Schritt zum Einsatz in der Praxis

Öffnen Sie die betreffende Arbeitsmappe in Excel und aktivieren Sie Makros. Starten Sie den VBA-Editor mit Alt und F11 und fügen Sie per Einfügen und Modul ein neues Modul ein. Kopieren Sie den gewünschten Makrocode in das Modul und speichern Sie die Datei als XLSM. Wechseln Sie zurück zu Excel, rufen Sie die Makros über Entwicklertools und Makros auf und starten Sie den gewünschten Ablauf. Prüfen Sie die Ausgabe in Spalte B und speichern Sie die Ergebnisse.

Fazit

Mit den gezeigten Makros lassen sich Hyperlinks per VBA auslesen und für Dokumentation oder Qualitätsprüfungen aufbereiten. Die E-Mail-Variante entfernt zusätzlich das Präfix mailto, damit eine saubere Adressliste entsteht. Wenn Sie auf das richtige Arbeitsblatt und eine leere Zielsäule achten, gewinnen Sie schnell und zuverlässig einen vollständigen Überblick über alle Hyperlink-Adressen in Excel.