Hyperlink ? VBA oder Power Query

Forumthread: Hyperlink ? VBA oder Power Query

26.07.2021 19:52:25

Hanna

Als Test in C:\Users\stevo\Documents\Test gespeichert
Daten_Pruefliste.xlsx
Baerbeitungsnummer Datum Hyperlink
123 20.05.2021 Google Maps geschlagen Stiftung Warentest mit überraschenden Ergebnissen - CHIP.pdf
110 31.12.2020 Gefrierschrank Test 2021 Die besten Gefriertruhen CHIP.pdf
117 16.09.2021 Waschmaschinen Test Testsieger der Stiftung Warentest CHIP.pdf
Ich habe zwei Excel Dateien die normalerweise auf einem Server liegen. Diese Datei wird von Kolleg:innen bearbeitet.
Die andere Datei ist über eine Abfrage (Power Query) mit der anderen verbunden, so das die Änderungen in dieser Datei "gespiegelt werden.
Pruefliste.xlsx. Diese Datei wird von der mehreren Nutzern angesehen und von wenigen Nutzern bearbeitet deshalb sind sind die Zellen auch schreibgeschützt
Leider werden über diese Verbindung die Hyperlinks zu den PDF Dokumenten nicht übermittelt und die Links sind in der Prüfliste.xlsx ohne Funktion
Ich habe mir mit einer zusätzlichen Spalte und er Funktion Hyperlink geholfen, aber beim Aktualisieren der Daten kommt es immer wieder zu Verschiebungen und die Links zu den PDF Dokumenten funtkioneren nicht mehr. Habe es auch mit absulutem Zellbezug versucht aber...
Habe es über VBA versucht aber die Links öffnen die PDF nicht (Falsches Format ?)
Dim strCellText As String
Dim lngIndex As Long
For lngIndex = 1 To 10
strCellText = Range("C" & CStr(lngIndex)).Value
ActiveSheet.Hyperlinks.Add Anchor:=Range("E" & CStr(lngIndex)), Address:="", SubAddress:= _
"'Test ++++'!E" & CStr(lngIndex), TextToDisplay:=strCellText
weiteres Problem die Aktualisierung der Abfrage beim öffnen der Excel Tabelle läuft nicht immer fehlerfrei (dauert zu lange)
Als Test in C:\Users\stevo\Documents\Test gespeichert
ActiveWorkbook.RefreshAll 'BackgroundQuery:=False ? im Hintergrund aktualisieren
DoEvents 'um den weiteren Ablauf abzuwarten ?
Wäre wirkich Klasse wenn Ihr mir hier weiterhelfen könntet.......LG
https://www.herber.de/bbs/user/147319.xlsm
https://www.herber.de/bbs/user/147321.xlsm

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Hyperlink ? VBA oder Power Query

28.07.2021 22:13:58

Yal

AW: Hyperlink ? VBA oder Power Query

28.07.2021 22:13:58

Yal

Hallo Hanna,
in der Pruefliste, schreibe einfach vor dem Pfad C:\Users\... den Prefix file://
dafür


Sub FilePrefix_einfügen()
Dim Z As Range
Const F = "file://"
For Each Z In Worksheets("Pruefliste").ListObjects(1).ListColumns("Hyperlink").DataBodyRange.Cells
If Not InStr(1, Z.Value, F, vbTextCompare) Then Z.Value = F & Z.Value
Next
End Sub

Die Rückgabe der Abfrage durch Power Query (also in deinem zweiten Datei, der Empfänger) wird bei der erste Übernahme den Link nicht automatisch erkennen. Aber, editiere den ersten Link (F2, Enter), so wird der Link erkannt. Bei der anschliessenden Aktualisierung der Abfrage werden alle Links automatisch erkannt.
Es liegt an diesen "intelligente" Tabellen, die versucht immer innerhalb einer Spalte dieselbe, schlüssige Formatierung zu haben.
Diese Editierung lässt sich kaum per VBA simulieren. Zumindest habe ich nicht gefunden wie. Sendkeys scheint auch nicht zu helfen.
Wenn es per Makro gehen muss (Bei viele Leute geht es manchmal besser per Hand), dann so:


Sub Link_reaktivieren()
Dim Z As Range
Set Z = ListObjects(1).ListColumns("Hyperlink").DataBodyRange.Rows(1)
ActiveSheet.Hyperlinks.Add Anchor:=Z, Address:=Z.Formula
Z.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

Viel Erfolg damit
VG
Yal

Infobox / Tutorial

Hyperlinks in Excel: VBA oder Power Query?

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um Hyperlinks in einer Excel-Tabelle mithilfe von Power Query oder VBA zu erstellen, kannst Du folgende Schritte befolgen:

Daten vorbereiten: Stelle sicher, dass Deine Daten in einer Tabelle vorliegen und die Hyperlinks in einer Spalte definiert sind.
Power Query verwenden:
- Öffne Power Query über die Registerkarte "Daten" und wähle "Daten abrufen".
- Wähle die Quelle Deiner Daten aus (z.B. Excel-Datei).
- Füge einen Schritt hinzu, um die Hyperlink-Spalte zu formatieren. Du kannst die hyperlink in power query-Funktion verwenden, um die Links zu erstellen.

VBA verwenden:

Öffne den VBA-Editor (ALT + F11).
Füge ein neues Modul hinzu und verwende den folgenden Code, um Hyperlinks zu erstellen:

Sub HyperlinkErstellen()
   Dim Z As Range
   For Each Z In Worksheets("DeinBlatt").Range("C1:C10") ' Passe den Bereich an
       ActiveSheet.Hyperlinks.Add Anchor:=Z, Address:=Z.Value
   Next Z
End Sub

Testen: Überprüfe, ob die Hyperlinks in der Tabelle korrekt funktionieren.

Häufige Fehler und Lösungen

Hyperlinks nicht erkannt: Wenn die Hyperlinks nach der Aktualisierung nicht funktionieren, stelle sicher, dass Du vor dem Pfad den Prefix file:// hinzufügst. Dies kann durch den folgenden VBA-Code automatisiert werden:

Sub FilePrefix_einfügen()
   Dim Z As Range
   Const F = "file://"
   For Each Z In Worksheets("Pruefliste").ListObjects(1).ListColumns("Hyperlink").DataBodyRange.Cells
       If Not InStr(1, Z.Value, F, vbTextCompare) Then Z.Value = F & Z.Value
   Next
End Sub

Aktualisierung dauert zu lange: Verwende den Befehl ActiveWorkbook.RefreshAll mit BackgroundQuery:=False, um die Aktualisierung im Vordergrund durchzuführen und die Belastung zu reduzieren.

Alternative Methoden

Wenn VBA nicht die gewünschte Lösung bietet, kannst Du auch Power Query verwenden. Um einen power query hyperlink zu erstellen, kannst Du die folgende Formel in Power Query verwenden:

= Table.AddColumn(DeineTabelle, "Hyperlink", each "file://" & [DeineLinkSpalte])

Dies konvertiert die Links in ein Format, das Excel korrekt erkennt und öffnet.

Praktische Beispiele

Excel-Liste für Gefrierschrank: Angenommen, Du hast eine Liste von Gefrierschränken. Du kannst die Hyperlinks zu Testberichten wie "Gefrierschrank Test CHIP" direkt in der Tabelle einfügen, indem Du die oben genannten Methoden anwendest.
Power BI Datenschnitt: Wenn Du Hyperlinks in Power BI nutzen möchtest, kannst Du ähnliche Ansätze verfolgen, um die Links in interaktiven Berichten zu integrieren.

Tipps für Profis

Automatisierung: Nutze VBA, um wiederkehrende Aufgaben zu automatisieren, insbesondere wenn Du regelmäßig Hyperlinks hinzufügst oder aktualisierst.
Links formatieren: Achte darauf, dass die Links einheitlich formatiert sind, um Probleme bei der Aktualisierung zu vermeiden.
Verwendung von Power Query: Experimentiere mit den verschiedenen Funktionen in Power Query, um Hyperlinks direkt zu generieren, ohne manuell eingreifen zu müssen.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass meine Hyperlinks immer funktionieren?
Achte darauf, dass der Prefix file:// vor dem Pfad hinzugefügt wird, und teste die Links regelmäßig nach Aktualisierungen.

2. Was ist der Unterschied zwischen VBA und Power Query für Hyperlinks?
VBA eignet sich besser für Automatisierungen und individuelle Anpassungen, während Power Query ideal ist, um Daten aus verschiedenen Quellen zu transformieren und zu laden, einschließlich Hyperlinks.

Beliebteste Forumthreads (12 Monate)