Zieladresse aus Hyperlink-Formel auslesen in Excel
Schritt-für-Schritt-Anleitung
Um das Ziel einer Hyperlink-Formel in Excel auszulesen, kannst du den folgenden VBA-Code verwenden. Dieser Code sucht nach der Hyperlink-Formel in einer Zelle und extrahiert das Linkziel:
Private Function HyperlinkzielFinden(rng As Range) As String
If Not rng.HasFormula Then Exit Function
If IsError(rng) Then Exit Function
If rng.Value = "" Then Exit Function
Dim i As Integer, j As Integer
Dim intKlammerlevel As Integer
Dim intEndeLinkteil As Integer
Dim strFormula As String
Dim rngEval As Range
strFormula = rng.Formula
i = InStr(1, strFormula, "HYPERLINK(", vbTextCompare)
If i = 0 Then Exit Function
For j = i + 9 To Len(strFormula) - 1
Select Case Mid$(strFormula, j, 1)
Case "("
intKlammerlevel = intKlammerlevel + 1
Case ")"
intKlammerlevel = intKlammerlevel - 1
Case ","
If intKlammerlevel = 1 Then
intEndeLinkteil = j - 1
Exit For
End If
End Select
Next j
Set rngEval = rng.Parent.Cells(rng.Parent.Rows.Count, rng.Parent.Columns.Count)
rngEval.Formula = "=" & Mid(strFormula, i + 10, intEndeLinkteil - (i + 9))
HyperlinkzielFinden = rngEval.Text
rngEval.Formula = ""
End Function
Um die Funktion zu verwenden, rufe sie einfach mit der Zelle auf, in der sich die Hyperlink-Formel befindet:
Sub Test()
Dim strLinktarget As String
strLinktarget = HyperlinkzielFinden(ActiveSheet.Range("C1"))
Debug.Print strLinktarget ' Gibt den Link aus
End Sub
Häufige Fehler und Lösungen
-
Error: "Auf geschützten Arbeitsblättern funktioniert das nicht."
- Lösung: Stelle sicher, dass das Arbeitsblatt nicht geschützt ist, wenn du den Hyperlink auslesen möchtest.
-
Formel gibt einen Fehler zurück
- Lösung: Überprüfe, ob die Zelle tatsächlich eine Hyperlink-Formel enthält und ob die Formel korrekt ist.
Alternative Methoden
Wenn du Excel Hyperlink ohne VBA auslesen möchtest, gibt es auch die Möglichkeit, die Formel in eine Zelle zu schreiben und dann den Wert auszulesen. Allerdings ist dies nicht immer ideal, insbesondere wenn das Arbeitsblatt geschützt ist. Eine weitere Möglichkeit besteht darin, die Hyperlink-Formel manuell zu überprüfen und die Verknüpfungen zu testen.
Praktische Beispiele
Beispiel 1: Hyperlink-Formel in Zelle C1
=HYPERLINK(VERKETTEN(A1;B1);B1)
In diesem Beispiel wird der Hyperlink aus den Zellen A1 und B1 erstellt. Du kannst die oben beschriebene Funktion verwenden, um die Zieladresse zu extrahieren.
Beispiel 2: Hyperlink aus Zelle auslesen
Sub Beispiel()
Dim link As String
link = HyperlinkzielFinden(ActiveSheet.Range("D1"))
MsgBox "Der Link ist: " & link
End Sub
Tipps für Profis
- Verwendung von
Dir(): Wenn du bereits das Ziel des Hyperlinks hast, kannst du die Dir()-Funktion in VBA verwenden, um zu prüfen, ob die Datei existiert.
- Verkettung auflösen: Bei komplexen Formeln, die mehrere Zellen verketten, kann es hilfreich sein, die einzelnen Teile der Formel zu extrahieren und sie manuell zusammenzusetzen.
FAQ: Häufige Fragen
1. Wie kann ich einen Hyperlink aus einer Excel-Zelle ohne VBA auslesen?
Du kannst die Formel in eine andere Zelle kopieren und die Ergebnisse manuell überprüfen, aber dies ist nicht die effizienteste Methode.
2. Gibt es eine Möglichkeit, Hyperlinks aus mehreren Zellen gleichzeitig auszulesen?
Ja, du kannst eine Schleife verwenden, um durch einen Bereich von Zellen zu iterieren und die HyperlinkzielFinden-Funktion auf jede Zelle anzuwenden.
3. Was mache ich, wenn die Excel-Version keine Unterstützung für bestimmte Funktionen bietet?
Überprüfe die Dokumentation für deine spezifische Excel-Version, um sicherzustellen, dass die verwendeten Funktionen unterstützt werden. In manchen Fällen sind ältere Versionen eingeschränkt in ihren Möglichkeiten.