Mac Excel 2019 Hyperlinks (Pfad relativ), Klartextdarstellung und Gültigkeitsprüfung

[Frando]

Hallo Zusammen,

ich arbeiten mit Excel 2019 und einer externen Festplatte am Mac.
Leider hänge ich total und hoffe, dass Einer von Euch mir bitte helfen kann?

1.Thema - Öffnen von Datei per Hyperlink:
Ich verwende per Formel relative Hyperlinks, die sich aus Verweise, Werte aus Zellen etc, zusammen setzen. Bei den Windowsbenutzer gehen diese, jedoch nicht beim Mac.

Spaltenbezeichnungen und Inhalt:
A = Altdatei, ein x, wenn eine Altdatei vorhanden ist, dann ändert sich auch der LINK in Spalte I, da der Pfad anders ist
B = Eigene Bezeichnung, Optional
C = Quelle, Optional
D = Titel, manuelle Eingabe
E = Stil, DropDownListe
F = Thema, DropDownListe
G = Level, DropDownListe
H = Eigene Aufnahmen,
=WENN(ISTFEHLER(HYPERLINK("./"& VERWEIS(E3;AuswahlVektorEigen;ErgebnisVektorEigen) & B3 & ".mp4";"E"));"";HYPERLINK("./"& VERWEIS(E3;AuswahlVektorEigen;ErgebnisVektorEigen) & B3 & ".mp4“;“E“))
I = Orginalvideos,
=HYPERLINK(WENN(A3="";"01_New/";"")&VERWEIS(E3;AuswahlVektorTanzstil;ErgebnisVektorTanzstil)&VERWEIS(F3;AuswahlVektorThema;ErgebnisVektorThema)&WENN(NICHT(ISTLEER(G3));"/"&G3;"")&W3&D3&".mp4“;“O“)

Beginn bei ZEILE 3
Es geht um die Spalten H und I.
Beim Anklicken kommt entweder:

„Potenzielles Microsoft Office-Sicherheitsrisiko
Links können Ihren Computer und Daten beschädigen. Um Ihren Computer zu schützen, klicken Sie nur auf Links aus vertrauenswürdigen Quellen. Dieser Ort ist möglicherweise nicht sicher:
/Users/ICH/Downloads/2Make4Dance/Dance/02_Bachata/01_Bachata_eigene Videos/Guarija Advanced Footwork.mp4
Möchten Sie fortfahren? NEIN JA“​

und nach „ja“

„Warnung
Die angegebene Datei konnte nicht geöffnet werden.“​

oder die Warnung kommt gleich. Der Pfad ist jedoch korrekt.

2.Thema - Prüfung der Hyperlinks auf Gültigkeit:
Ich wollte per VBA prüfen, ob die Hyperlinks auf vorhandene Dateien gehen, oder ins Leere laufen.
Wenn keine Daten (da Name falsch o.ä.) soll mit die Zelle rot gefüllt werden.

VBA Code:

Sub Hyperlinks_testing()
Dim H As Hyperlink
'Installiere Fehlerbehandlung
On Error GoTo LinkTot
'Durchlaufe alle Hyperlinks
For Each H In ActiveSheet.Hyperlinks
'Öffne ihn
H.Follow True
Next
'Fertig
Exit Sub

LinkTot:
'Zelle rot markieren
H.Range.Interior.ColorIndex = 3
'Fortfahren mit nächster Zeile
Resume Next
End Sub​

3.Thema - Hyperlinks als Klartext:
Gern möchte ich die Hyperlinks aus Spalte H und I als reinen Text in Spalte Y und Z darstellen.

4.Thema - aktive Zellenmarkierung bis zum Ende der Spalte:
Markiere ab aktive Zelle bis zum Ende in der Spalte

VBA Code:

Sub MarkiereSpaltebisEnde()
x = Cells(Rows.Count, 1).End(xlUp).Row
End Sub​

5.Thema - Dateien aus Ordner auslesen und auflisten:
In einer neuen Registerkarte (AuslesenDateienausOrdner) steht in A1 der Ordnerpfad, den ich manuell eintrage.
In den Ordner befinden sich Unterordner und darin Videodateien in .mp4. Nun möchte ich gern die Dateinamen in der neuen Registerkarte auflisten.

VBA Code:

Sub Verzeichnis_Auflisten()
Dim Pfad As String
Dim sep As String
sep = Application.PathSeparator
'Pfad = ThisWorkbook.Path & sep 'oder
Pfad = Range("A1") & sep
'WICHTIG: am Ende Path Separator
' False= Dateien, True=Ordner; A1= Zielzelle, False= Zeilenweise, True=Spaltenweise
Call VerzeichnisListen(Pfad, False, "A2", False) '
End Sub​

Private Sub VerzeichnisListen(Pfad As String, Ordner As Boolean, Zielzelle As String, Optional Spaltenweise As Boolean)
'True für Ordner, False für Dateien listen
Dim strInhalt() As String 'Array
Dim strObjekt As String
Dim sep As String 'PathSeparator
Dim intCount As Integer
ChDir (Pfad) 'Pfad wechseln; bleibt sonst im letzten Verzeichnis
intCount = 0
On Error GoTo Pfad_Exit
Select Case Ordner
Case Is = True: strObjekt = Dir("", vbDirectory) 'findet Dateien + Ordner
Case Is = False: strObjekt = Dir(Pfad) 'findet nur Dateien
End Select
On Error GoTo 0​

If strObjekt = "" Then 'keine Dateien vorhanden
MsgBox "Ordner leer!"
Exit Sub
End If
Do
If Ordner = True Then
If Not strObjekt Like "*.*" Then 'nur Ordner ohne "." durchlassen
intCount = intCount + 1
ReDim Preserve strInhalt(intCount)
strInhalt(intCount) = strObjekt
End If
Else 'Dateien
' If strObjekt Like "*.mp4" Then 'nur bestimmte Dateitypen
intCount = intCount + 1
ReDim Preserve strInhalt(intCount)
strInhalt(intCount) = strObjekt
' end if
End If
'nächste Datei
strObjekt = Dir()
Loop Until strObjekt = ""
'ausleeren
If intCount > 0 Then
For intCount = LBound(strInhalt) To UBound(strInhalt)
If Spaltenweise = True Then
Range(Zielzelle).Offset(0, intCount + 1) = strInhalt(intCount) 'spaltenweise
Else
Range(Zielzelle).Offset(intCount + 1, 0) = strInhalt(intCount) 'zeilenweise, standardmäßig
End If
Next intCount
End If
Exit Sub
Pfad_Exit:
MsgBox "Pfad nicht gefunden!"
End Sub​

 

[McTailor]

Bei mir funktioniert
/Users/ICH/Downloads/2Make4Dance/Dance/02_Bachata/01_Bachata_eigene Videos/Guarija Advanced Footwork.mp4

wenn ich es auf file://localhost/Users etc. beginnen lasse.

Es kommt dieselbe Warnmeldung wie bei Dir, aber nach dem Wegklicken wird der Film abgespielt.

 

[Frando]

Hallo McTailor,

Vielen Dank, der Pfad funzt.

Wenn ich nun den Pfad „„ in Zelle W2 schreibe und eine Verkettung veranlasse, dass aus Zelle Z3 der restliche Pfad hinzugefügt wird, dann schreibe ich in einer anderen Zelle folgende Formel: =(W$2 & Z3)

Dargestellt wird:
file://localhost/Users/devil/Downloads/2Make4Dance/Dance/01_New/05_Movements/Movements/Arms Kiz.mp4
Jedoch ist es nicht anklickbar, da die Formel in der Formel angezeigt wird.

 

[McTailor]

Um anklickbar zu werden muss die Zeichenkette wohl noch in die Funktion HYPERLINK() gekapselt werden.

Also =HYPERLINK(W$2 & Z3)

 

[Frando]

Hat funktioniert, Danke.