Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zieladresse aus Hyperlink-FORMEL auslesen

Zieladresse aus Hyperlink-FORMEL auslesen
01.08.2014 18:21:45
Karsten
Hallo,
ich habe in einem Sheet eine Menge per HYPERLINK()-Formel erzeugte Zellen und möchte per VBA prüfen, ob die jeweiligen Dateien existieren. Letztendlich brauche ich also das Hyperlink-Ziel.
Beispiel:
A1 enthält "C:\Windows\" und hat den Namen "Basispfad"
B1 enthält "win.ini"
C1 enthält "=HYPERLINK(VERKETTEN(A1;B1);B1)"
D1 enthält "=HYPERLINK(Basispfad&B1;B1)"
Klick auf C1 und D1 öffnet jeweils die win.in.
Frage: Wie komme ich per VBA aus C1 oder D1 auf "C:\windows\win.ini" ?
Das Hyperlink-Objekt ist unbrauchbar, weil es nicht mit Formeln funktioniert.
Vermutlich muss ich das erste Argument aus der Formel rausparsen. Kann ich. Aber wie kann ich dann z.B. "Basispfad&B1" auswerten? Evaluate() kann das nicht.
Vielen Dank
Karsten

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
01.08.2014 18:51:20
Matthias
Hallo Karsten
Tabelle1

 ABCD
1C:\Windows\win.iniwin.iniwin.ini

Formeln der Tabelle
ZelleFormel
C1=HYPERLINK(VERKETTEN(A1;B1);B1)
D1=HYPERLINK(Basispfad&B1;B1)
Namen in Formeln
ZelleNameBezieht sich auf
D1Basispfad=Tabelle1!$A$1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Ich bin zwar kein Profi, habe es aber mal nachgestellt.
Bei mir hat es funktioniert.
In beiden Zellen wird die Win.ini geöffnet.
Oder ich habe Dich total falsch verstanden, dann schon jetzt mal Sorry dafür
Gruß Matthias

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 17:53:23
Karsten
Hallo Matthias,
das hast Du völlig falsch verstanden. Klar funktioniert der Klick. Die Frage war:
Wie komme ich per VBA aus C1 oder D1 auf "C:\windows\win.ini" ?
Ungefähr so was suche ich: Sub test() Dim rng As Excel.Range Dim strLinktarget As String Set rng = ActiveSheet.Range("C1") If InStr(1, rng.Formula, "HYPERLINK") > 0 Then ' hier fehlt der Code, der mir aus der Formel das Linkziel extrahiert strLinktarget = ? End If Debug.Print strLinktarget End Sub Am Ende soll im Direktfenster "C:\Windows\win.ini" stehen. Leider habe ich keine Idee. Und möchte mich so lange wie möglich dagegen wehren, die Formel (mit allen syntaktischen Varianten) selbst zu parsen und die Einzelteile auszuwerten. Das muss doch einfacher gehen! Oder?
Viele Grüße
Karsten

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 18:17:01
Christian
Hallo Karsten,
eine Verständnisfrage:
Du hast das Ziel in A1 und B1 hinterlegt?
Warum nicht per VBA diese Zellen auslesen und den Hyperlink prüfen?
MfG Christian

AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 18:33:22
Karsten
Hallo Christian,
das ist ein stark vereinfachtes Beispiel. Ich Echt bekomme ich irgendein Sheet, über das ich nichts weiß, und muss prüfen, ob es die Dateien in den Hyperlinks gibt. Aber dazu muss ich sie erst mal auslesen.
Viele Grüße
Karsten

AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 18:50:41
Christian
Hallo Karsten,
hast Du schon mal im Archiv gesucht?
Da sind einige Beispiele vorhanden, als VBA-Profi kannst Du die bestimmt anpassen!
MfG Christian

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 19:29:55
Karsten
Hallo Christian,
ja, ich habe gesucht. Und nichts gefunden.
Wenn Du ein Beispiel aus der Praxis willst:
=WENN($C24>"";HYPERLINK((VERKETTEN(Bildpfad;$C24&FD$10));$C24&FD$10);"")

In C24 steht der Prefix der Datei ("win") und in FD10 der Suffix (".ini"). Bildpfad wie im Beispiel. Es muss aber auch für beliebige andere Formeln funktionieren, die einen gültigen Pfad zusammensetzen. Der Code kommt in ein AddIn und färbt nicht vorhandene Dateien (=Zellen) rot ein. Ich kann also von gar nichts ausgehen, außer eben der Formel.
Wenn es seeehr einfach ist das auszulesen dann mach doch mal einen konkreten Vorschlag. Find ich nämlich überhaupt nicht einfach.
Viele Grüße
Karsten

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 19:31:25
Karsten
Oh sorry, das seeehr einfach hab ich aus dem Beitrag von Thorsten eingemixt.

AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 20:17:27
Christian
Hallo Karsten,
Du bist der VBA-Profi, ich schätze mein VBA-Wissen weitaus geringer ein.
Aber ich nehme Dir nicht ab, dass Du nichts gefunden hast!
https://www.herber.de/forum/archiv/712to716/713705_Umkehrfunktion_zu_HYPERLINK.html
https://www.herber.de/forum/archiv/1208to1212/1208523_HYPERLINK_Quickinfo_aus_Zelle_lesen.html
Das sind nur zwei von ca. 340 Treffern hier im Forumsarchiv, bei Google ca. 1000mal so viele Treffer..
MfG Christian

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 20:27:03
Karsten
Hallo Christian,
ich revidiere: ich habe nichts zu meinem Problem passendes gefunden.
Der erste Link verlangt Änderungen am Excel-Sheet (kann ich nicht machen), beim zweiten steht der komplette Pfad schon in einer Zelle. Das ist alles leicht und nicht mein Problem.
Lass den Thread einfach offen stehen, vielleicht hat noch jemand eine geniale Idee.
Viele Grüße
Karsten

AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 19:00:01
Oberschlumpf
Hi Karsten,
"...das ist ein stark vereinfachtes Beispiel...."
Tja, wenn es in der Original-Datei gaaaanz anders aussieht, als in deinem stark vereinf. Bsp, dann zeig uns doch am besten mal genau das, was wo wie im Original steht.
Natürlich, wenn erforderlich, Realdaten gegen Bsp-daten ersetzen - Struktur aber beibehalten
Denn anhand deines Bsps is es seeehr einfach, per VBA A1 + B1 auszulesen, zum Pfad zusammenbasteln, als Ergebnis auszugeben.
Anderer Tipp:
Um herauszufinden, ob Datei existiert, reicht ein Klick auf den Hyperlink.
Wenn Hyperlink funktioniert, ist Datei vorhanden, ist deine Frage beantwortet.
Ciao
Thorsten

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 20:06:24
Oberschlumpf
hi karsten,
ja,ich war das mit seeeehr einfach - und das war auf dein bsp mit A1+B1 bezogen!
Zeig uns doch mal ne Bsp-Datei.
Einträge in den richtigen Zellen.
Ciao
Thorsten

AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 20:18:58
Karsten
Hallo Thorsten,
ja, hab ich dann auch gemerkt ;-)
Eine Beispieldatei ändert nichts.
Folgendes ist gegeben: Ein Loop durch alle Zellen einer beliebigen, unbekannten, Datei. Darin sind HYPERLINK-Formeln enthalten, die mittels irgendeiner Verkettung einen gültigen Pfad zusammenbauen. Normalerweise sind die Dateien vorhanden, dann ist alles gut. Aber manchmal sind sie verschoben.
Ich will alle Zellen markieren, in denen es keine Dateien zu den Links gibt.
Ergo muss ich aus der HYPERLINK-Formel den Pfad ermitteln.
Wenn Du das sauber aus dem Beispiel (und vielleicht noch dem Praxisbeispiel oben) schaffst funktioniert es überall. Klar?
Viele Grüße
Karsten

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
02.08.2014 22:40:47
Oberschlumpf
Hi
...Eine Beispieldatei ändert nichts. ...
hihi, nein, nein, DU kannst NICHT entscheiden, ob für MICH ne Bsp-datei was ändert oder nich.
Aber ok, du willst nich...dann will ich auch nich.
Ich bin raus
Thorsten

AW: Zieladresse aus Hyperlink-FORMEL auslesen
03.08.2014 01:54:17
Mullit
Hallo,
in Bezug auf Deinen 1.Post:
Vermutlich muss ich das erste Argument aus der Formel rausparsen. Kann ich.
Aber wie kann ich dann z.B. "Basispfad&B1" auswerten?

Wenn das rausparsen schon hinhaut und Du nur noch "Basispfad&B1" auf Existenz überprüfen mußt,
könnte z.B. die Dir-Funktion das sein, was Du suchst....
Gruß,

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
03.08.2014 15:37:03
Karsten
Hallo Mullit,
das erste Argument rausparsen würde ich nur als letzten Ausweg machen. Ich hatte gehofft dass jemandem hier was eleganteres einfällt.
Aber selbst dann: wie mache ich aus "Basispfad&B1" oder "Verketten(A1;B1)" oder "VERKETTEN(Bildpfad;$C24&FD$10));$C24&FD$10)" (oder einer beliebigen anderen Kombination) einen Pfad? Es gibt keine Excel-Funktion, die ich aus VBA aufrufen kann und die das auflöst, oder? Ich hatte auf Evaluate() gehofft, das kann aber nicht mal "A1&B1".
Ich könnte es als Formel in eine Zelle schreiben und dann den Wert auslesen. Aber dann funktioniert es wieder nicht mit geschützten Sheets. Zu dumm...
Dir() kenne ich. Sobald ich einen Pfad habe ist alles ok.
Viele Grüße
Karsten

Anzeige
AW: Zieladresse aus Hyperlink-FORMEL auslesen
04.08.2014 00:42:40
Mullit
Hallo Karsten,
tja, dann müsstest Du Dich doch ums Rausparsen kümmern;
bisher fällt mir da zunächst auch nichts anderes ein ....
Da gäbe es z.B. drei Möglichkeiten:
Option Explicit

'Hyperlink/Verketten-Formel: 

Public Sub prcHyperVerketten()
 With ActiveSheet.Cells(1, 3)
     MsgBox Dir(Evaluate(Mid$(String:=.Formula, Start:=InStr(1, .Formula, "(", vbTextCompare) + 1, _
         Length:=InStr(1, .Formula, ")", vbTextCompare) - InStr(1, .Formula, "(", vbTextCompare))))
 End With
End Sub
 
'Hyperlink/Basispfad-Formel: 
 
 Public Sub prcHyperBasispfad()
    MsgBox Dir(ThisWorkbook.Names("Basispfad").RefersToRange & ActiveSheet.Cells(1, 4))
 End Sub
  
'Hyperlink/UnbekannterName-Formel: 

Public Sub prcHyperUnknownName()
  With ActiveSheet.Cells(1, 4)
      MsgBox Dir(ThisWorkbook.Names(Mid$(String:=.Formula, Start:=InStr(1, .Formula, "(", _
         vbTextCompare) + 1, Length:=InStr(1, .Formula, "&", vbTextCompare) - _
         InStr(1, .Formula, "(", vbTextCompare) - 1)).RefersToRange & ActiveSheet.Cells(1, 4))
  End With
End Sub


VBA/HTML - CodeConverter für Office-Foren
AddIn für Excel/Word 2000-2010 - komplett in VBA geschrieben von Lukas Mosimann
Projektbetreuung durch mumpel



Code erstellt und getestet in Office 12

Die anderen Kombis kannst Du ja selbst mal zusammenbauen...
Gruß Mullit,

AW: Zieladresse aus Hyperlink-FORMEL auslesen
04.08.2014 02:05:30
Mullit
Hallo,
in 2) und 3) muß es natürlich richtig heißen:
'...
& ActiveSheet.Cells(1, 2)
'...
Oder Du ermittelst die Zelle ebenfalls aus dem Formel-String....
Gruß,

AW: Zieladresse aus Hyperlink-FORMEL auslesen
06.08.2014 17:55:57
Karsten
Falls noch mal jemand so krude Probleme hat anbei das finale Ergebnis:

' Procedure : HyperlinkzielFinden
' Date      : 06.08.2014
' Author    : Karsten Pries (pries@gmx.de)
' Purpose   : extrahiert aus einer Hyperlink-Formel das HL-Ziel.
'             Übergeben wird eine einzelne Zelle
Private Function HyperlinkzielFinden(rng As Range) As String
If UserMode Then On Error GoTo Error_HyperlinkzielFinden
Dim i As Integer, j As Integer
Dim intKlammerlevel As Integer
Dim intEndeLinkteil As Integer
Dim strFormula As String
Dim rngEval As Range
If Not rng.HasFormula Then Exit Function
If IsError(rng) Then Exit Function
If rng.Value = "" Then Exit Function ' dann wird beim Hyperlink kein Text angezeigt,
' die wollen wir nicht prüfen
If rng.Parent.ProtectContents Then
MsgBox "Auf geschützten Arbeitsblättern funktioniert das nicht.", vbExclamation, _
"HyperlinkzielFinden"
Exit Function
End If
strFormula = rng.Formula
i = InStr(1, strFormula, "HYPERLINK(", vbTextCompare)
If i = 0 Then Exit Function
For j = i + 9 To Len(strFormula) - 1
' durchgehen und passende schließende Klammer finden
Select Case Mid$(strFormula, j, 1)
Case "("
intKlammerlevel = intKlammerlevel + 1
Case ")"
intKlammerlevel = intKlammerlevel - 1
Case ","
' das erste gefundene Komma trennt die zwei HYPERLINK-Argumente.
If intKlammerlevel = 1 Then
intEndeLinkteil = j - 1
' Der Rest interessiert uns nicht
Exit For
End If
End Select
Next j
' die gefundene Formel in der Zelle gaaaanz rechts unten auswerten.
' In VBA geht das nicht ohne extreme Verrenkungen
Set rngEval = rng.Parent.Cells(.Rows.Count, .Columns.Count)
If rngEval.Value  "" Then
MsgBox "Letzte Zelle auf dem Blatt ist nicht leer, kann nichts auswerten.", _
vbExclamation, "HyperlinkzielFinden"
Else
rngEval.Formula = "=" & Mid(strFormula, i + 10, intEndeLinkteil - (i + 9))
If Not IsError(rngEval) Then
HyperlinkzielFinden = rngEval.Text
End If
rngEval.Formula = ""
End If
Exit_HyperlinkzielFinden:
Exit Function
Error_HyperlinkzielFinden:
MsgBox Err.Number & ": " & Err.description & vbCrLf & _
" in Funktion HyperlinkzielFinden.", vbCritical, "mdlAllgemein"
Resume Exit_HyperlinkzielFinden
End Function

AW: Zieladresse aus Hyperlink-FORMEL auslesen
06.08.2014 18:10:30
Karsten
Da habe ich glatt beim formatieren fürs Web noch einen Fehler eingebaut. Unten fehlt die Referenz auf das Arbeitsblatt. So ist es richtig:

' die gefundene Formel in der Zelle gaaaanz rechts unten auswerten.
' In VBA geht das nicht ohne extreme Verrenkungen
With rng.Parent
Set rngEval = .Cells(.Rows.Count, .Columns.Count)
End With
If rngEval.Value  "" Then

AW: Zieladresse aus Hyperlink-FORMEL auslesen
06.08.2014 21:41:33
Mullit
Hallo,
was schreibst Du Dir da für Hieroglyphen zusammen?
Falls noch mal jemand so krude Probleme hat...

...siehe 3 Posts weiter oben...
MsgBox "Auf geschützten Arbeitsblättern funktioniert das nicht."

klar geht das; s.o.
....VBA-Profi....
Gruß,

182 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige