Hyper­links in Pow­er Query, das sollte eigentlich kein Prob­lem darstellen. Dachte ich. Aber ich wurde rasch eines Besseren belehrt als ich in ein­er als Tabelle erstell­ten Abfrage den Hyper­Link per Klick nachver­fol­gen wollte. Es war nach dem Spe­ich­ern ganz nor­maler Text. Und das ist auch klar, denn Pow­er Query ver­fügt (derzeit) nicht über den Daten­typ URL. Über einen kleinen Umweg lässt sich das aber dur­chaus regeln …

Laden Sie bitte ein­mal diese Mus­ter­datei. Es sind 2 Arbeits­blät­ter mit prinzip­iell iden­tis­chen Dat­en. Im ersten Blatt (Tabelle1) ist in Spalte A der Name der Fir­ma ver­merkt, in Spalte B die entsprechende URL in „Klarschrift”, bei der Eingabe bere­its als Hyper­Link for­matiert und natür­lich anklick­bar. In Tabelle2 sind prinzip­iell die gle­ichen Dat­en enthal­ten, hier ist jedoch in Spalte A eine Art Grup­pierung einge­tra­gen, in Spalte B der Fir­men­name und in Spalte C der eigentliche Link, die URL. Der Link soll anschließend in Spalte B unsicht­bar in den Nament inte­gri­ert sein. Im Fach­bere­ich spricht man für den angezeigten Namen von einem „friend­ly name”. Ein Klick auf den Fir­men­na­men und im Brows­er wird der entsprechende Link geöffnet.

▲ nach oben …

Probehalber …

Aktivieren Sie der Mus­ter­datei das Arbeits­blatt Tabelle1. Sie wollen die Dat­en in Pow­er Query sortieren und dann in eine neue Tabelle schreiben. Also öff­nen Sie den Abfrage-Edi­tor und laden diese Dat­en dort hinein. Sortieren sie nach der Spalte Name und Spe­ich­ern & laden. Das Ergeb­nis sieht erst ein­mal ganz nor­mal aus; nur die URLs sind in schwarz­er Schrift und ein Test wird auch sofort zeigen, dass sie nicht anklick­bar sind.

Aber da gibt es doch etwas im Menü Start. In der Gruppe For­matvor­la­gen Sym­bol Zel­len­for­matvor­la­gen:

For­matierung eines Links in typ­is­chem Ausse­hen

Gesagt, getan. Jet­zt sehen die Links (eigentlich Link-Texte) auch richtig gut aus. Stimmt, aber sie sehen nur so aus, sie haben immer noch nicht die Funk­tion­al­ität eines Hyper­links. Ein Klick darauf bewirkt immer noch nichts. Den­noch soll­ten Sie sich diese Vorge­hensweise merken, Sie wer­den sie noch ein­set­zen. 😉 

Auch wenn dieser Test schein­bar nichts gebracht hat, so haben wir wenig­stens die Basis ein­er Abfrage. Und die kön­nen Sie dur­chaus für weit­ere Arbeit­en ver­wen­den, um zum gewün­scht­en Ziel zu gelan­gen.

▲ nach oben …

Name und Hyperlink (Klarschrift)

Öff­nen Sie die eben erstellte Abfrage auf einem beliebi­gen Weg. Ich markiere dazu meist (falls erforder­lich) eine Zelle in den Dat­en, Abfrage­tools | Abfrage | Bear­beit­en und der Abfrage-Edi­tor zeigt die Dat­en in der bere­its sortierten Rei­hen­folge an.

Zuerst ein­mal zur Ursachen­forschung. Warum wird in der erzeugten Tabelle nur Text angezeigt? Prüfen Sie die Spalte URL auf den aktuellen Daten­typ und sie wer­den erken­nen, dass es Text ist. Wenn Sie jet­zt die Auswahl mit den Möglichkeit­en erweit­ern wer­den Sie erken­nen, dass es keine Form des Daten­typs gibt, der einen Hyper­Link als solchen darstellen kann. (Vielle­icht kommt das ja noch.) Darum müssen wir einen kleinen Umweg beschre­it­en, um zum gewün­scht­en Ziel zu gelan­gen.

Die Logik des Weges zum Ziel ist eigentlich recht sim­pel: Sie schreiben den Link so, dass er in eine ganz nor­male Hyper­Link-Formel in die Zelle geschrieben wird. Dann ist es auch einen Hyper­Link, der dort ste­hen wird. Also gehen Sie auf Spalte hinzufü­gen | Benutzerdefinierte Spalte und im Dia­log tra­gen Sie diese Dat­en ein:

Bei Neuer Spal­tenname tra­gen Sie Link ein, und als Formel ver­wen­den Sie:

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

Bei den " ist es nicht immer so ganz klar erkennbar wie sich die darstellen. Darum hier eine kleine Verdeut­lichung: Ganz zu Anfang ein (1) dop­peltes Anführungsze­ichen ("), gefol­gt von einem ein­fachen ' (Shift#). Nach der öff­nen­den Klam­mer als auch vor der schließen­den Klam­mer sind jew­eils 3 dop­pelte Anführungsze­ichen. Und zum Schluss noch ein­mal ein ein­fach­es Gänse­füßchen. So wird die Formel in Excel durch das führende ein­fache ' als Text in die anschließend zu erzeu­gende Tabelle geschrieben. Im Edi­tor stellt sie das nun so dar:

Die Formel im Dialogfen­ster

Die Spalte URL ist jet­zt über­flüs­sig, darum Recht­sklick in die Über­schrift und Ent­fer­nen. Anschließend Start | Schließen & laden. Auch wenn sie der Inhalt der Spalte B irri­tiert:

Auch wenn es nicht so scheint, es ist kor­rekt …

Das ist richtig so. Dort ste­ht in jed­er Zeile die Formel für den Hyper­Link welche durch das führende Aus­las­sungsze­ichen als Text for­matiert ist. Und damit ist der Teil „Pow­er Query” auch been­det. Die fol­gen­den Schritte beziehen sich nur auf Excel.

▲ nach oben …

Zu Fuß

Markieren Sie die kom­plette Spalte B der Intel­li­gen­ten Tabelle. StrgH um in den Erset­zen-Dia­log zu gelan­gen. Hier suchen Sie nach '= und erset­zen diese Zeichen­folge nur mit =, wobei sie natür­lich auf Alle erset­zen Klick­en. OK, das sieht ja schon ganz vernün­ftig aus. Und auch wenn es nicht so scheint: Es sind jet­zt bere­its echte Hyper­links. Ein Klick auf einen Zellinhalt und sofort wird sich im Brows­er die entsprechende Seite öff­nen. Der Form hal­ber kön­nen, nein soll­ten Sie noch die weit­er oben ange­sproch­ene For­matierung für die Dat­en anwen­den, also über die Zel­len­for­matvor­la­gen die Auswahl Link tre­f­fen. Jet­zt haben die Ein­träge auch das gewohnte Ausse­hen für Hyper­links. 🙂 

▲ nach oben …

Mit anschubsen 😉

Diesen Vor­gang der Umwand­lung kön­nen Sie natür­lich auch per VBA/Makro erledi­gen lassen. Ins­beson­dere dann, wenn sie Pow­er Query einge­set­zt haben, weil sich die Aus­gangsliste ständig aktu­al­isiert, ist natür­lich auch die durch die Abfrage erstellte Tabelle immer wieder auf den neuesten Stand zu brin­gen. 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 Mod­ul des Arbeits­blattes mit der gener­ierten Tabelle (hier: Tabelle3) oder in ein all­ge­meines Mod­ul ein und starten Sie es beispiel­sweise über AltF8. Alter­na­tiv kön­nen Sie natür­lich auch in das Blatt eine Schalt­fläche ein­fü­gen und diese mit dem Mod­ul verbinden. Das mag benutzer­fre­undlich­er sein.

Wie auch immer sie vorge­hen, einen kleinen vielle­icht unge­woll­ten Seit­en­ef­fekt wer­den sie unter Umstän­den fest­stellen: Durch die Aktu­al­isierung geht die Kennze­ich­nung, dass das ein Link bere­its auf diesem Weg geöffnet wor­den ist, lei­der ver­loren; die far­bliche Kennze­ich­nung ist dann wieder durchgängig das typ­is­che blau.

Soll­ten die Dat­en nach ein­er Aktu­al­isierung wieder in schwarz­er Schrift und nicht als Hyper­Link-For­matierung dargestellt wer­den, dann müssen Sie den Weg über Tabel­len­tools | Entwurf | Eigen­schaften gehen und dafür Sorge tra­gen, dass das Häkchen bei Zell­for­matierung beibehal­ten geset­zt ist:

So bleibt die For­matierung auch nach ein­er Aktu­al­isierung der Dat­en erhal­ten

▲ nach oben …

Gruppe und Name (Hyperlink in Name integriert)

Prinzip­iell ist das 2. Arbeits­blatt gle­icher­maßen aufge­baut. Das Vorge­hen ist auch in großen Teilen iden­tisch, denn auch hier wird der Hyper­Link als Text mit voran gestell­tem ' in die zu erzeu­gende Tabelle einge­fügt. Zusät­zlich muss der in Spalte B dargestellte Name des Unternehmens in den Link inte­gri­ert wer­den, da ja der Name angezeigt wer­den und der eigentliche Link ver­steckt wer­den soll. Im End­ef­fekt sollen natür­lich auch nur 2 Spal­ten gener­iert wer­den, wobei in der 2. Spalte der Name aus der vorheri­gen Spalte B gezeigt und der Hyper­Link darin inte­gri­ert ist.

Wie in der 1. Ver­sion wer­den sie die Spalte URL duplizieren, in den Text ein­er Excel-Formel umwan­deln und dann mit einem führen­den ' verse­hen, aber in einzelne Sequen­zen aufge­s­plit­tet. Begin­nen Sie so nach dem fest inte­gri­erten =:

"'=HYPERLINK("

Wenn diese Sequenz probe­hal­ber mit einem OK abschließen wer­den sie erken­nen, dass der Text '=HYPERLINK( in der Spalte ste­ht. Als näch­stes muss inner­halb ein­fach­er Gänse­füßchen der Text des eigentlichen Hyper­links ste­hen, hier also beispiel­sweise https://www.microsoft.com/de-de. Damit aber diese dop­pel­ten Anführun­gen nicht als Steuerze­ichen son­dern als zu schreiben­des Zeichen inter­pretiert wer­den, geben Sie vor und nach dem zu schreiben Link "" ein. Im End­ef­fekt sind es dann 3 Anführungsze­ichen. Da der Text des zu schreiben­den Links ja bere­its in der Spalte URL ste­ht, übernehmen Sie diesem natür­lich aus genau dieser Spalte:

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

Jet­zt muss noch ein Kom­ma, der Inhalt der Spalte Name einge­fügt wer­den und auch die schließende Klam­mer. Da sie in diesem Dia­log auch mehrere Zeilen schreiben kön­nen, ohne ein Verbindungsze­ichen ver­wen­den zu müssen, kön­nte das bei Ihnen so ausse­hen:

Kom­plette Formel, jet­zt mit dem „friend­ly name”

Hier noch ein­mal als Text:

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

Das war schon fast. Die bei­den Spal­ten Name und URL im Abfrage-Edi­tor löschen und anschließend kön­nen Sie die Spalte Benutzerdefiniert in URL um. Wie gehabt auch hier das führende Apos­troph per Hand oder mit­tels ein­er VBA-Rou­tine ent­fer­nen und beim 1. spe­ich­ern schlussendlich die For­matierung als Hyper­Link vornehmen.

Hin­weis: Große Teile der Prob­lem­lö­sung habe ich hier bei Imke Feld­mann gefun­den und mit meinen Worten in diesen Beitrag einge­bracht. (Danke Imke 😎 )

Auch wenn es auf der Hand liegt, sei mir der Hin­weis ges­tat­tet: Selb­stver­stän­lich kann auf diese Weise jede beliebige Excel-Formel in eine Spalte einge­tra­gen und dann aktiviert wer­den. Sie muss nur kom­plett in der Sprachver­sion des instal­lierten Excel geschrieben wer­den, da sie ja in der Tabelle prinzip­iell direkt eingegeben wird.

▲ nach oben …