Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Zieladresse aus Hyperlink-FORMEL auslesen

Betrifft: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 01.08.2014 18:21:45

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

  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Matthias L
Geschrieben am: 01.08.2014 18:51:20

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 17:53:23

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Christian
Geschrieben am: 02.08.2014 18:17:01

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 18:33:22

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Christian
Geschrieben am: 02.08.2014 18:50:41

Hallo Karsten,

hast Du schon mal im Archiv gesucht?
Da sind einige Beispiele vorhanden, als VBA-Profi kannst Du die bestimmt anpassen!

MfG Christian


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 19:29:55

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 19:31:25

Oh sorry, das seeehr einfach hab ich aus dem Beitrag von Thorsten eingemixt.


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Christian
Geschrieben am: 02.08.2014 20:17:27

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 20:27:03

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Oberschlumpf
Geschrieben am: 02.08.2014 19:00:01

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Oberschlumpf
Geschrieben am: 02.08.2014 20:06:24

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 02.08.2014 20:18:58

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Oberschlumpf
Geschrieben am: 02.08.2014 22:40:47

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Mullit
Geschrieben am: 03.08.2014 01:54:17

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ß,


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 03.08.2014 15:37:03

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


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Mullit
Geschrieben am: 04.08.2014 00:42:40

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,


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Mullit
Geschrieben am: 04.08.2014 02:05:30

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ß,


  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 06.08.2014 17:55:57

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



  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Karsten
Geschrieben am: 06.08.2014 18:10:30

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
[...]



  

Betrifft: AW: Zieladresse aus Hyperlink-FORMEL auslesen von: Mullit
Geschrieben am: 06.08.2014 21:41:33

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ß,


 

Beiträge aus den Excel-Beispielen zum Thema "Zieladresse aus Hyperlink-FORMEL auslesen"

Werte zu Zieladressen übertragen
Hyperlinks sollen mit Quell- und Zieladresse gelistet werden
Mehrfachauswahl aus ListBox auslesen
Dateien aus Listbox auslesen und öffnen
Bereich in RefEdit-Element eingeben und auslesen
Auslesen der Arbeitsmappeneigenschaften
UserName aus der Windows-Registry auslesen
Eintrag aus der Win.ini auslesen
Einzelnes Zeichen aus einer InputBox-Eingabe auslesen
Ausgewählte Arbeitsblätter auslesen
Mehrfachauswahl in ListBox auslesen
Auslesen der Mausposition
Festplatten-Seriennummer auslesen
Cursorposition in der Entwicklungsumgebung auslesen
Bedingte Formatierung auslesen
Computername auslesen
Werte aus Zellbereich in eine Matrix übernehmen und auslesen
Auslesen des ausgewählten Optionsfeldes in einer UserForm
Datum in Registry eintragen und auslesen
Mit Drehfeld durch UserForm-TextBoxes blättern und Daten auslesen
Werte aus einem nicht zusammenhängendem Bereich auslesen
Zahlenstring mit Formel auslesen und Fehlermeldung verhindern
Arbeitsmappen- und Tabellenblattnamen auslesen
Aus einer Reihe von 10-minütigen Messungen h-Mittelwerte auslesen
UserForm-Elemente auslesen
Umgebungsvariable aus der autoexec.bat auslesen
Gültigkeitsauswahl über VBA auslesen
Namen eines ausgewählten Bereiches auslesen
Bildschirmauflösung auslesen
Aus mehrspaltiger UserForm-ListBox Werte der 1. Spalte auslesen
VBE-Komponenten der aktiven Arbeitsmappe auslesen
Positionen einzelner Zeichen in langer Zeichenfolge auslesen
Rechnungsnummer in Registry eintragen, auslesen, löschen
In Array einlesen, sortieren, auslesen
Tabellenblattinhalt in seqentielle Textdatei ein- und auslesen
Plattenspeicher auslesen
Ein- und Auslesen der Seiteneinrichtung / Orientierung
Alle geöffneten Tasks auslesen
Fußzeile ein- und auslesen
Daten in benutzerdefinierten Datentyp ein- und auslesen
Daten in zweidimensionales Array ein- und in Tabelle auslesen
Auslesen der aktuellen Mausposition
Installierte Drucker auslesen
Titel einer HTML-Seite auslesen
Auslesen von Schnittstellenwerten
Textdateien auslesen und in Werte in Zellen eintragen
Grafik-Dateinamen aus Zelle auslesen und HTML-Datei erstellen
Aus einem String mit Datumsangabe das Jahr auslesen
Wert aus Optionsfelder in verschiedenen Frames auslesen
Gefilterte Daten in Array ein und aus Array auslesen
Aus GetOpenFilename-Dialog eine Mehrfachauswahl auslesen