Power Query und Hyperlinks

Hyperlinks nach Bearbeitung durch Power Query weiterhin anklickbar gestalten

Sorry, als ich gestern diesen Beitrag schrieb, klappte alles wie gewünscht (mit Probelauf). Heute läuft aus unerfindlichen Gründen nichts mehr richtig, die Texte können nicht in Hyperlinks umgewandelt werden. Ich arbeite daran, bis dahin ist der Beitrag erst einmal nicht sichtbar.

Danke für Ihr Verständnis.

Hyperlinks in Power Query, das sollte eigentlich kein Problem darstellen. Dachte ich. Aber ich wurde rasch eines Besseren belehrt als ich in einer als Tabelle erstellten Abfrage den Hyperlink per Klick nachverfolgen wollte. Es war nach dem Speichern ganz normaler Text. Und das ist auch klar, denn Power Query verfügt (derzeit) nicht über den Datentyp URL. Über einen kleinen Umweg lässt sich das aber durchaus regeln …

Laden Sie bitte einmal diese Musterdatei. Es sind 2 Arbeitsblätter mit prinzipiell identischen Daten. Im ersten Blatt (Tabelle1) ist in Spalte A der Name der Firma vermerkt, in Spalte B die entsprechende URL in „Klarschrift“, bei der Eingabe bereits als Hyperlink formatiert und natürlich anklickbar. In Tabelle2 sind prinzipiell die gleichen Daten enthalten, hier ist jedoch in Spalte A eine Art Gruppierung eingetragen, in Spalte B der Firmenname und in Spalte C der eigentliche Link, die URL. Der Link soll anschließend in Spalte B unsichtbar in den Nament integriert sein. Im Fachbereich spricht man für den angezeigten Namen von einem „friendly name“. Ein Klick auf den Firmennamen und im Browser wird der entsprechende Link geöffnet.

▲ nach oben …

Probehalber …

Aktivieren Sie der Musterdatei das Arbeitsblatt Tabelle1. Sie wollen die Daten in Power Query sortieren und dann in eine neue Tabelle schreiben. Also öffnen Sie den Abfrage-Editor und laden diese Daten dort hinein. Sortieren sie nach der Spalte Name und Speichern & laden. Das Ergebnis sieht erst einmal ganz normal aus; nur die URLs sind in schwarzer Schrift und ein Test wird auch sofort zeigen, dass sie nicht anklickbar sind.

Aber da gibt es doch etwas im Menü Start. In der Gruppe Formatvorlagen Symbol Zellenformatvorlagen:

Formatierung eines Links in typischem Aussehen

Formatierung eines Links in typischem Aussehen

Gesagt, getan. Jetzt sehen die Links (eigentlich Link-Texte) auch richtig gut aus. Stimmt, aber sie sehen nur so aus, sie haben immer noch nicht die Funktionalität eines Hyperlinks. Ein Klick darauf bewirkt immer noch nichts. Dennoch sollten Sie sich diese Vorgehensweise merken, Sie werden sie noch einsetzen. 😉 

Auch wenn dieser Test scheinbar nichts gebracht hat, so haben wir wenigstens die Basis einer Abfrage. Und die können Sie durchaus für weitere Arbeiten verwenden, um zum gewünschten Ziel zu gelangen.

▲ nach oben …

Name und Hyperlink (Klarschrift)

Öffnen Sie die eben erstellte Abfrage auf einem beliebigen Weg. Ich markiere dazu meist (falls erforderlich) eine Zelle in den Daten, Abfragetools | Abfrage | Bearbeiten und der Abfrage-Editor zeigt die Daten in der bereits sortierten Reihenfolge an.

Zuerst einmal zur Ursachenforschung. Warum wird in der erzeugten Tabelle nur Text angezeigt? Prüfen Sie die Spalte URL auf den aktuellen Datentyp und sie werden erkennen, dass es Text ist. Wenn Sie jetzt die Auswahl mit den Möglichkeiten erweitern werden Sie erkennen, dass es keine Form des Datentyps gibt, der einen Hyperlink als solchen darstellen kann. (Vielleicht kommt das ja noch.) Darum müssen wir einen kleinen Umweg beschreiten, um zum gewünschten Ziel zu gelangen.

Die Logik des Weges zum Ziel ist eigentlich recht simpel: Sie schreiben den Link so, dass er in eine ganz normale Hyperlink-Formel in die Zelle geschrieben wird. Dann ist es auch einen Hyperlink, der dort stehen wird. Also gehen Sie auf Spalte hinzufügen | Benutzerdefinierte Spalte und im Dialog tragen Sie diese Daten ein:

Bei Neuer Spaltenname tragen Sie Link ein, und als Formel verwenden Sie:

"'=HYPERLINK(""" & [URL] & """)"

Bei den " ist es nicht immer so ganz klar erkennbar wie sich die darstellen. Darum hier eine kleine Verdeutlichung: Ganz zu Anfang ein (1) doppeltes Anführungszeichen ("), gefolgt von einem einfachen ' (Shift#). Nach der öffnenden Klammer als auch vor der schließenden Klammer sind jeweils 3 doppelte Anführungszeichen. Und zum Schluss noch einmal ein einfaches Gänsefüßchen. So wird die Formel in Excel durch das führende einfache ' als Text in die anschließend zu erzeugende Tabelle geschrieben. Im Editor stellt sie das nun so dar:

Die Formel im Dialogfenster

Die Formel im Dialogfenster

Die Spalte URL ist jetzt überflüssig, darum Rechtsklick in die Überschrift und Entfernen. Anschließend Start | Schließen & laden. Auch wenn sie der Inhalt der Spalte B irritiert:

Auch wenn es nicht so scheint, es ist korrekt …

Auch wenn es nicht so scheint, es ist korrekt …

Das ist richtig so. Dort steht in jeder Zeile die Formel für den Hyperlink welche durch das führende Auslassungszeichen als Text formatiert ist. Und damit ist der Teil „Power Query“ auch beendet. Die folgenden Schritte beziehen sich nur auf Excel.

▲ nach oben …

Zu Fuß

Markieren Sie die komplette Spalte B der Intelligenten Tabelle. StrgH um in den Ersetzen-Dialog zu gelangen. Hier suchen Sie nach '= und ersetzen diese Zeichenfolge nur mit =, wobei sie natürlich auf Alle ersetzen klicken. OK, das sieht ja schon ganz vernünftig aus. Und auch wenn es nicht so scheint: Es sind jetzt bereits echte Hyperlinks. Ein Klick auf einen Zellinhalt und sofort wird sich im Browser die entsprechende Seite öffnen. Der Form halber können, nein sollten Sie noch die weiter oben angesprochene Formatierung für die Daten anwenden, also über die Zellenformatvorlagen die Auswahl Link treffen. Jetzt haben die Einträge auch das gewohnte Aussehen für Hyperlinks. 🙂 

▲ nach oben …

Mit anschubsen 😉

Diesen Vorgang der Umwandlung können Sie natürlich auch per VBA/Makro erledigen lassen. Insbesondere dann, wenn sie Power Query eingesetzt haben, weil sich die Ausgangsliste ständig aktualisiert, ist natürlich auch die durch die Abfrage erstellte Tabelle immer wieder auf den neuesten Stand zu bringen. Fügen Sie diesen Code …

Sub TextZuLink()
   With Range("Tabelle1_2[Link]")  'Anpassen!
      .Replace What:="'=", Replacement:="=", _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
   End With
End Sub

… in das Modul des Arbeitsblattes mit der generierten Tabelle (hier: Tabelle3) oder in ein allgemeines Modul ein und starten Sie es beispielsweise über AltF8. Alternativ können Sie natürlich auch in das Blatt eine Schaltfläche einfügen und diese mit dem Modul verbinden. Das mag benutzerfreundlicher sein.

Wie auch immer sie vorgehen, einen kleinen vielleicht ungewollten Seiteneffekt werden sie unter Umständen feststellen: Durch die Aktualisierung geht die Kennzeichnung, dass das ein Link bereits auf diesem Weg geöffnet worden ist, leider verloren; die farbliche Kennzeichnung ist dann wieder durchgängig das typische blau.

Sollten die Daten nach einer Aktualisierung wieder in schwarzer Schrift und nicht als Hyperlink-Formatierung dargestellt werden, dann müssen Sie den Weg über Tabellentools | Entwurf | Eigenschaften gehen und dafür Sorge tragen, dass das Häkchen bei Zellformatierung beibehalten gesetzt ist:

So bleibt die Formatierung auch nach einer Aktualisierung der Daten erhalten

So bleibt die Formatierung auch nach einer Aktualisierung der Daten erhalten

▲ nach oben …

Gruppe und Name (Hyperlink in Name integriert)

Prinzipiell ist das 2. Arbeitsblatt gleichermaßen aufgebaut. Das Vorgehen ist auch in großen Teilen identisch, denn auch hier wird der Hyperlink als Text mit voran gestelltem ' in die zu erzeugende Tabelle eingefügt. Zusätzlich muss der in Spalte B dargestellte Name des Unternehmens in den Link integriert werden, da ja der Name angezeigt werden und der eigentliche Link versteckt werden soll. Im Endeffekt sollen natürlich auch nur 2 Spalten generiert werden, wobei in der 2. Spalte der Name aus der vorherigen Spalte B gezeigt und der Hyperlink darin integriert ist.

Wie in der 1. Version werden sie die Spalte URL duplizieren, in den Text einer Excel-Formel umwandeln und dann mit einem führenden ' versehen, aber in einzelne Sequenzen aufgesplittet. Beginnen Sie so nach dem fest integrierten =:

"'=HYPERLINK("

Wenn diese Sequenz probehalber mit einem OK abschließen werden sie erkennen, dass der Text '=HYPERLINK( in der Spalte steht. Als nächstes muss innerhalb einfacher Gänsefüßchen der Text des eigentlichen Hyperlinks stehen, hier also beispielsweise https://www.microsoft.com/de-de. Damit aber diese doppelten Anführungen nicht als Steuerzeichen sondern als zu schreibendes Zeichen interpretiert werden, geben Sie vor und nach dem zu schreiben Link "" ein. Im Endeffekt sind es dann 3 Anführungszeichen. Da der Text des zu schreibenden Links ja bereits in der Spalte URL steht, übernehmen Sie diesem natürlich aus genau dieser Spalte:

"'=HYPERLINK(""" & [URL]

Jetzt muss noch ein Komma, der Inhalt der Spalte Name eingefügt werden und auch die schließende Klammer. Da sie in diesem Dialog auch mehrere Zeilen schreiben können, ohne ein Verbindungszeichen verwenden zu müssen, könnte das bei Ihnen so aussehen:

Komplette Formel, jetzt mit dem "friendly name"

Komplette Formel, jetzt mit dem „friendly name“

Hier noch einmal als Text:

"'=HYPERLINK(""" & [URL]
& """,""" & [Name] &
""")"

Das war schon fast. Die beiden Spalten Name und URL im Abfrage-Editor löschen und anschließend können Sie die Spalte Benutzerdefiniert in URL um. Wie gehabt auch hier das führende Apostroph per Hand oder mittels einer VBA-Routine entfernen und beim 1. speichern schlussendlich die Formatierung als Hyperlink vornehmen.

Hinweis: Große Teile der Problemlösung habe ich hier bei Imke Feldmann gefunden und mit meinen Worten in diesen Beitrag eingebracht. (Danke Imke 😎 )

Auch wenn es auf der Hand liegt, sei mir der Hinweis gestattet: Selbstverstänlich kann auf diese Weise jede beliebige Excel-Formel in eine Spalte eingetragen und dann aktiviert werden. Sie muss nur komplett in der Sprachversion des installierten Excel geschrieben werden, da sie ja in der Tabelle prinzipiell direkt eingegeben wird.

▲ nach oben …

 
Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datentyp anpassen, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Schreibe einen Kommentar