Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Hyperlinks in Formeln umwandeln

Hyperlinks in Formeln umwandeln
12.08.2013 11:30:25
guebla
Hallo Leute!
Da ich die Verzeichnisstruktur bzw. einen Verzeichnisnamen ändern musste, sind in mehreren Dateien alle Hyperlinks defekt. Ich benötige eine VBA-Routine die mir automatisch die Hyperlinks in eine Formel =HYPERLINK(Hyperlink_Adresse; [Anzeigename]) umschreibt. Die Zelle mit den alten Hyperlinks soll dabei überschrieben werden. Über die Suche- und Ersetzefunktion kann ich dann leicht die falschen Einträge korrigieren.
Recht herzlichen dank für Eure Bemühungen.

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hyperlinks in Formeln umwandeln
13.08.2013 08:15:03
Luschi
Hallo guebla,
so richtig habe ich nicht verstanden, was mit Vba passieren soll, damit per Suchen und Ersetzen der Hyperlink geändert werden soll.
Das kann man doch in einem Ritt per Vba machen. Beschreibe doch bitte es etwas genauer.
Gruß von Luschi
aus klein-Paris

AW: Hyperlinks in Formeln umwandeln
14.08.2013 08:47:55
guebla
Hallo Luschi,
ich habe eine Exceldatei in der mehrere hundert Verlinkungen abgelegt sind. Nun habe ich aber einerseits den Verzeichnisbaum, als auch wenige Dateinamen ändern müssen, auf die die Verlinkungen der Exceldatei zeigen. D. h. von diesen Links in der Exceldatei funktionieren die meisten nicht mehr. Es ist aber auch mühselig jeden einzelnen Link per Hand über Pulldown-Menü zu ändern. Daher kam mir die Idee, via VBA-Routine, alle Links der Datei auszulesen und die Hyperlinks automatisch in eine Formel =HYPERLINK(Hyperlink_Adresse; [Anzeigename]) umzuwandeln. Natürlich werden dann weiterhin die falschen Hyperlinkadressen in der Formel =HYPERLINK(Hyperlink_Adresse; [Anzeigename]) stehen. Diese falschen Einträge kann ich dann per Suche- und Ersetzefunktion per Hand ändern, da es sich meistenteils um eine Verzeichnisnamensänderung handelt, aber das mehrere 100 mal.

Anzeige
AW: Hyperlinks in Formeln umwandeln
14.08.2013 12:22:24
Luschi
Hallo guebla,
da die Hyperlink-Formeln nicht in der Hyperlink-Auflistung der Arbeitsblätter aufgelistet sind, muß man per Find-Befehl die Zellen suchen, die eine solche Formel enthalten. In der extra anzulegenden Tabelle 'iniTab' werden 3 Angaben gelistet: Tabellenname, Zelladresse und Hyperlink-Formel.
Wenn Du die korrigierten Hyperlinks in der 4. Spalte erfaßt, kann man per Vba die Formeln auch wieder zurückschreiben.
Um Dir die Arbeit ein 2. mal zu ersparen, falls wieder Verzeichniswechsel anstehen, schreibe den Pfad in der 'iniTab' in eine Zelle und verknüpfe die neue Hyperlinkadresse mit dieser Zelle.
Dann mußt Du beim nächsten mal nur noch diesen Zellinhalt an den neuen Pfad anpassen. Denn _ gerade, wenn es sich um ein Netzlaufwerkpfad handelt ist man vor der Arbeitswut des Admins nicht geschützt.

Sub hypsSuchen()
Dim wb As Workbook, ws As Worksheet, _
rg1 As Range, rg2 As Range
Dim adr As String
Set wb = ThisWorkbook
'iniTtab ist eine separate/extra angelegte Tabelle
Set rg1 = ThisWorkbook.Worksheets("iniTab").Range("A4")
rg1.Value = "Tabelle"
rg1.Offset(0, 1).Value = "Zelle"
rg1.Offset(0, 2).Value = "Formel"
rg1.Resize(1, 3).Font.Bold = True
Set rg1 = rg1.Offset(1, 0)
For Each ws In wb.Worksheets
If ws.Name  "iniTab" Then
'alle Zeilen & Spalten einblenden, da Find versteckte Zellen nicht findet!!!
ws.Cells.EntireRow.Hidden = False
ws.Cells.EntireColumn.Hidden = False
With ws.Cells
Set rg2 = .Find("=HYPERLINK(", , xlFormulas, xlPart, xlByColumns, xlNext, False,  _
False, False)
If Not rg2 Is Nothing Then
adr = rg2.Address
Do
rg1.Value = rg2.Parent.Name
rg1.Offset(0, 1).Value = rg2.Address(0, 0)
rg1.Offset(0, 2).Value = "'" & rg2.FormulaLocal
Set rg2 = .FindNext(rg2)
Set rg1 = rg1.Offset(1, 0)
Loop While Not rg2 Is Nothing And rg2.Address  adr
End If
End With
End If
Next ws
Set rg1 = Nothing
Set rg2 = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Gruß von Luschi
aus klein-Paris

Anzeige
AW: Hyperlinks in Formeln umwandeln
14.08.2013 13:59:08
guebla
Hallo Luschi,
danke für die Routine, aber ich glaube die macht nicht das, was sie eigentlich machen soll.
Die Routine soll die Hyperlinks finden, die ganz normal über das Pulldownmenü rechte Maustaste auf eine Zelle Hyperlink angelegt worden sind. Deine Routine, ich habe es anhand einer Datei probiert, findet keinen Hyperlink. Zusätzlich habe ich mal probiert einen Hyperlink über die Excel-Formel =HYPERLINK(....) in eine Zelle zu schreiben. Rufe ich dann Deine Routine auf, stürzt das sie ab.
Das Register iniTab hatte ich per Hand angelegt und Deine Routine schreibt auch in iniTab die Überschrift Tabelle/ Zelle/ Formel ein.
Oder mach ich hier irgentetwas falsch?
Gruß
guebla

Anzeige
AW: Hyperlinks in Formeln umwandeln
17.08.2013 10:33:39
fcs
Hallo Guebla,
hier ein Makro das die Hyperlinks in entsprechende Formel-Hyperlinks umwandelt.
Allerdings muss man beachten, dass Excel dabei mit relativen Pfad-Angaben arbeitet und teilweise URL- oder UNC-Schreibweise verwendet(Pfadtrennzeichen = "/" nicht "\").
Es ist deshalb notwendig, die Exceldatei zunächst via Speichern-untern auf einem Laufwerk zu Speichern, dessen Laufwerk in den Links nicht vorkommt (z.B. USB-Stick) bevor das Makro ausgeführt wird.
Nachdem die Formel-Hyperlinks erzeugt sind kann die Datei wieder in einem beliebigen Verzeichnis gespeichert werden.
Teste das Ganze mal in einer Kopie der Datei.
Das Makro speicherst du am besten in deiner persönlichen Makro-Arbeitsmappe oder in der Datei mit den Hyperlinks.
Gruß
Franz
Sub Hyperlinks_to_Formeln()
'Hyperlinks im aktiven Blatt in Formeln umwandeln
'ACHTUNG: Excel verwendet bei der Umwandlung relative Pfadangaben und URL/UNC-Schreibweise
'Übereinstimmende Teile des Pfades von Datei und Link werden durch "../../" ersetzt.
'Datei mit Hyperlinks ggf. vor Makroausführung auf einem anderen Laufwerk _
speichern (z.B. USB-Stick)
Dim wks As Worksheet
Dim objHypLink As Hyperlink
Dim strAddress As String, strSubAddress As String, strInfo As String, _
strFormel As String
Dim rngZelle As Range
Set wks = ActiveSheet
For Each rngZelle In wks.UsedRange
If rngZelle.Hyperlinks.Count > 0 Then
Set objHypLink = rngZelle.Hyperlinks(1)
With objHypLink
strAddress = .Address
strSubAddress = .SubAddress
strInfo = .TextToDisplay
.Delete
End With
strFormel = "=HYPERLINK(""" & strAddress
If strSubAddress  "" Then
strFormel = strFormel & "#" & strSubAddress
End If
If strInfo  "" Then
strFormel = strFormel & """,""" & strInfo & """)"
Else
strFormel = strFormel & """)"
End If
rngZelle.FormulaR1C1 = strFormel
End If
Next rngZelle
End Sub

Anzeige
AW: Hyperlinks in Formeln umwandeln
20.08.2013 14:38:55
guebla
Hallo Franz,
leider bin ich erst heute dienstlich wieder dazu gekommen, an meinen Hyperlinkproblem weiter zu arbeiten. Ich habe soeben Deine Routine an einer Datei von mir getestet. Es hat wunderbar funktioniert. Hab recht herzlichen Dank dafür. Kann man in deiner VBA Routine noch die Informationen einbauen, dass die Zelleigenschaften (Zellfarbe, Zellrahmen usw.) nicht überschrieben, bzw. übernommen werden?
Gibt es denn dann eine Möglichkeit, wenn ich die Formeln entsprechend korrigiert habe, die Formeln per VBA wieder in "richtige Hyperlinks umzuwandeln?

AW: Hyperlinks in Formeln umwandeln
23.08.2013 12:26:35
guebla
Hallo Franz,
ich möchte mich gern noch einmal in Erinnerung bringen. Kannst Du mir vielleicht noch einmal helfen?
Ich hatte dir am 20.08.13 noch einmal geantwortet mit einem kleinen Änderungswunsch.
Kann man in deiner VBA Routine noch die Informationen einbauen, dass die Zelleigenschaften (Zellfarbe, Zellrahmen usw.) nicht überschrieben, bzw. übernommen werden?
Hab recht herzlichen Dank für Deine Hilfe.
Gruss
Günther
Anzeige

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige