Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1256to1260
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Hyperlinkpfad via Macro ändern

Hyperlinkpfad via Macro ändern
Anke
Habe folgendes Problem:
Excel-Datei mit mehreren Arbeitsblättern. Innerhalb jedes Arbeitsblattes befinden sich ca. 500 Zeilen mit Daten, die jeweils via Hyperlink mit Bildern verknüpft sind. Die Hyperlinks haben auch alle eine eigene Bezeichnung. Da sich das Verzeichnis der Bilder geändert hat (Serverdefekt) muß ich jetzt alle Hyperlinks ändern (ca. 1000). Ich habe zwar im Internet ein Macro gefunden, was auch funktioniert, allerdings ist hier dann die Bezeichnung der Hyperlinks weg. Die sollte aber erhalten bleiben.
Die Exceldatei umwandeln in eine Html-Datei und dort die Hyperlinks via suchen/ersetzen tauschen und die Datei anschließend in Excel öffnen und abspeichern scheidet als Lösung aus, da ich ein "vorgelagertes" Archivierungsprogramm nutzen muß und nicht an den eigentlichen Speicherort der Dateien gelangen kann. Ich kann also schon von vornherein die speichern/unter Website nicht durchführen. Wäre jetzt zu umständlich das zu erklären. Es kommt also nur eine Makrolösung in Frage. Kann mir jemand helfen?
AW: Hyperlinkpfad via Macro ändern
22.03.2012 10:55:09
marcl
Hallo Anke,
da ist für Dich wohl die Eigenschaft TextToDisplay wichtig. Ic hhabe ... für die
Hyperlinks.Add Anchor:=..., Address:= ...,TextToDisplay:=....
Gruß
marcl
AW: Hyperlinkpfad via Macro ändern
22.03.2012 11:13:37
Anke
Marcl,
ja. Kannst Du mir bitte den kompletten Code geben anhand des Beispiels:
alter Pfad: C:\server1\Digitalbilder
neuer Pfad: G:\test7\Digitalbilder
Kann leider nicht programmieren. Wäre super nett.
AW: Hyperlinkpfad via Macro ändern
22.03.2012 11:42:18
marcl
ok, gerne,
was wird als Beschriftung in der Zelle angezeigt und wie Heißt das Bild dann wirklich? Ist das eventuell identisch? Welche Dateiendung haben die Bilder?
In welcher Zeile und Spalte beginnt der Auflistung?
Gruß
marcl
Anzeige
AW: Hyperlinkpfad via Macro ändern
22.03.2012 12:01:33
Anke
Marcl,
ok ich versuchs mal zu erklären:
Zeile 1 = Überschriftenzeile
Zeile 2 bis zur Zeile 200 stehen jeweils in den Spalten J und L die Hyperlinks, die derzeit auf dem erwähnten alten Beispielpfad liegen. Die Bezeichnungen der Hyperlinks (Hyperlink bearbeiten, Text anzeigen als) sind teilweise identisch und entweder eine Zahl oder ein Buchstabe. Diese müssen bei der Änderung des Pfades so erhalten bleiben. Zur näheren Erklärung, es handelt sich hier um eine Art Lageplan mit Abteilung, Reihe und Nr. Von der jeweiligen Abteilung und der Nr. gibt es dann ein Foto (jpg.Datei), was via Hyperlink aktiviert wird.
Hoffe, ich konnte es so einigermaßen erklären. Lieben Dank schon mal für die Mühe.
Anzeige
AW: Hyperlinkpfad via Macro ändern
22.03.2012 12:13:28
marcl
ok,
am Besten eine Kopie der Datei anlegen und dort das Makro probieren. Ich möchte ja nichts vernichten.
Option Explicit
Sub Pfad()
Dim neuerPfad As String
Dim Dateiname As String
Dim i As Integer
Dim j As Integer
neuerPfad = "G:\test7\Digitalbilder\"
For i = 2 To 200
For j = 10 To 12 Step (2)
Dateiname = Cells(i, j).Value ' Cells(Zeile, Spalte)
Cells(i, j).Hyperlinks.Add Anchor:=Selection, Address:= _
neuerPfad & Dateiname & ".jpg", TextToDisplay _
:=Dateiname
Next j
Next i
End Sub
Gruß
marcl
AW: Hyperlinkpfad via Macro ändern
22.03.2012 17:07:53
dan
Hallo Anke,
hier ein Code der aendert Adresse fuer alle Hyperlinks, die mit "jpg" beendet sind in gesamtem Workbook.
Nicht vergessen:
Vor dem Makro Start solltest Du von Deiner Datei eine Back-Up Kopie machen!!! :-).
Mfg, dan, cz.
Option Explicit
' Private Const ALTER_PFAD As String = "C:\server1\Digitalbilder"
Private Const NEUER_PFAD As String = "G:\test7\Digitalbilder\"
Private Const HYPERLINK_TARGET_TYPE As String = "JPG"
Public Sub Main(): On Error GoTo Err_Main
ReplaceAllHyperlinkAddress
Exit Sub
Err_Main:
MsgBox Err.Description, vbCritical, "Error"
End Sub

Private Sub ReplaceAllHyperlinkAddress()
Dim oneSheet As Worksheet
Dim oneCell As Range
Dim oneHyperlink As Hyperlink
Dim hyperlinksCount As Long
Dim replaceCount As Long
Dim notRepacedAddresses As String: notRepacedAddresses = "Not replaced: " & vbCrLf
Dim fileName As String
For Each oneSheet In ThisWorkbook.Worksheets
For Each oneCell In oneSheet.UsedRange.Cells
For Each oneHyperlink In oneCell.Hyperlinks
hyperlinksCount = hyperlinksCount + 1
If (Strings.InStr(Strings.UCase(oneHyperlink.Address), HYPERLINK_TARGET_TYPE) > 0) Then
fileName = GetFileNameFromHyperlinkAddress(oneHyperlink.Address)
If (fileName  "") Then
oneHyperlink.Address = NEUER_PFAD & fileName
replaceCount = replaceCount + 1
End If
Else
notRepacedAddresses = notRepacedAddresses & oneSheet.Name & ":" & oneHyperlink.Range. _
Address & ", " & oneHyperlink.Address & vbCrLf
End If
Next oneHyperlink
Next oneCell
Next oneSheet
If (hyperlinksCount - replaceCount > 0) Then MsgBox notRepacedAddresses, vbExclamation, " _
Caution, not all hyperlinks replaced"
End Sub

Private Function GetFileNameFromHyperlinkAddress(ByVal hyperlinkAddress As String):  _
GetFileNameFromHyperlinkAddress = ""
Dim fileNameSeparatorPosition As Integer: fileNameSeparatorPosition = Strings.InStrRev( _
hyperlinkAddress, "/")
If (fileNameSeparatorPosition  0) Then GetFileNameFromHyperlinkAddress = Strings.Right( _
hyperlinkAddress, Strings.Len(hyperlinkAddress) - fileNameSeparatorPosition)
End Function

Anzeige
AW: Hyperlinkpfad via Macro ändern
23.03.2012 08:58:57
Anke
Hi Marcl,
sorry, dass ich mich noch nicht gemeldet habe, war den restlichen Tag unterwegs. Erst mal lieben Dank für Deine Mühe.
Aber...geht leider nett. Fehlermeldung: Syntaxfehler. Gelb markiert ist: "Privat Sub Replace...." und in roter Farbe: If (hyperlinksCount - replaceCount ..
Meine Güte, hätte ich doch nur etwas Ahnung von dem ganzen. Falls Du noch Lust hast zu helfen, wäre ich Dir tierisch dankbar.
LG Anke
AW: Hyperlinkpfad via Macro ändern
23.03.2012 10:04:28
dan
Hallo Anke,
hast Du den Code komplett kopiert? Ich schicke den Code noch einmal. Kopiere es vom Text ' # Start # bis zu Text ' # End #.
Es soll dann so aussehen im VBA Editor:
Userbild
Userbild
Wenn Du den Code in Editor (Module) kopiert hast, dann postiere den Cursor in die 'Sub Main' und druecke F5. Kommt immer noch der selbe Fehler?
Marko Code:
' # Start #
Option Explicit
' Private Const ALTER_PFAD As String = "C:\server1\Digitalbilder"
Private Const NEUER_PFAD As String = "G:\test7\Digitalbilder\"
Private Const HYPERLINK_TARGET_TYPE As String = "JPG"
Public Sub Main(): On Error GoTo Err_Main
ReplaceAllHyperlinkAddress
Exit Sub
Err_Main:
MsgBox Err.Description, vbCritical, "Error"
End Sub

Private Sub ReplaceAllHyperlinkAddress()
Dim oneSheet As Worksheet
Dim oneCell As Range
Dim oneHyperlink As Hyperlink
Dim hyperlinksCount As Long
Dim replaceCount As Long
Dim notRepacedAddresses As String: notRepacedAddresses = "Not replaced: " & vbCrLf
Dim fileName As String
Dim endPart As String
For Each oneSheet In ThisWorkbook.Worksheets
For Each oneCell In oneSheet.UsedRange.Cells
For Each oneHyperlink In oneCell.Hyperlinks
hyperlinksCount = hyperlinksCount + 1
endPart = Strings.Right(Strings.UCase(oneHyperlink.Address), Strings.Len( _
HYPERLINK_TARGET_TYPE))
If (endPart = HYPERLINK_TARGET_TYPE) Then
fileName = GetFileNameFromHyperlinkAddress(oneHyperlink.Address)
If (fileName  "") Then
oneHyperlink.Address = NEUER_PFAD & fileName
replaceCount = replaceCount + 1
End If
Else
notRepacedAddresses = notRepacedAddresses & oneSheet.Name & ":" & oneHyperlink.Range. _
Address & ", " & oneHyperlink.Address & vbCrLf
End If
Next oneHyperlink
Next oneCell
Next oneSheet
If (hyperlinksCount - replaceCount > 0) Then MsgBox notRepacedAddresses, vbExclamation, " _
Caution, not all hyperlinks replaced"
End Sub

Private Function GetFileNameFromHyperlinkAddress(ByVal hyperlinkAddress As String):  _
GetFileNameFromHyperlinkAddress = ""
Dim fileNameSeparatorPosition As Integer: fileNameSeparatorPosition = Strings.InStrRev( _
hyperlinkAddress, "/")
If (fileNameSeparatorPosition  0) Then GetFileNameFromHyperlinkAddress = Strings.Right( _
hyperlinkAddress, Strings.Len(hyperlinkAddress) - fileNameSeparatorPosition)
End Function
' # End #
Anzeige
AW: Hyperlinkpfad via Macro ändern
23.03.2012 11:08:44
Anke
Hi Marcl,
hab den Code nochmal kopiert. Beim Kopieren bei Caution not all .... erhalte ich 2 "_" liegt wohl an meinem Rechner, dass er diese Symbole mitnimmt. Jedenfalls ist es so, dass wenn ich das korrigiere - sprich " und _ wegnehme, das Makro funktioniert und alle Pfade ordnungsgemäß geändert sind. Nur nimmt er trotzdem die Bilder nicht. Wie vorhin schon erwähnt. Es liegt an dem Archivieungsprogramm. Wenn ich eine bestehende Exceldatei in dieses Programm importiere, legt er die Datei in einer Art Zwischenspeicher (Storage) ab. Ich bin jetzt den Weg über HTML gegangen, aber diesmal über dieses Storage. Exceldatei mit "speichern unter" als Website in diesem Storage gespeichert. Dann über Excel "speichern unter" die Website mit rechte Maustaste "öffnen mit" Frontpage geöffnet und über suchen/ersetzen die Links geändert und wieder gespeichert. Dann habe ich über Excel die Website in Excel wieder geöffnet und die Spalten kopiert und in die immer noch geöffnete Orginalexceldatei . Das hat funktioniert. Schon irre. Normalerweise kann man so gar keine Links kopieren in diesem Fall aber schon. Wie sagt man so schön "nicht hinterfragen, einfach als gegeben hinnehmen". Danke Dir trotzdem für die Mühe und wünsche Dir noch einen schönen Tag.
Anzeige
AW: Hyperlinkpfad via Macro ändern
23.03.2012 11:31:50
dan
Hallo Anke,
haupsache es funktioniert egal wie man das geschaft hat :-).
Dir auch einen schoenen Tag! Gruss dan, cz.
AW: Hyperlinkpfad via Macro ändern
23.03.2012 09:38:19
Anke
Hi Marcl,
Syntaxfehler gefunden. Bin total stolz auf mich, mit ohne Ahnung auf die Lösung gekommen, war der Unterstrich vor Caution. Marko funktioniert erst mal. Lieben lieben Dank. Jetzt kommt aber ein ABER, Excel findet die Bilder nicht. Sehr sehr merkwürdig. Wenn ich händisch den Hyperlink ändere, hat er das Bild. Wenn ich mit der Maus über den Link gehe, sind aber die Pfade absolut identisch. Kann also nur an diesem scheiß Archivierungsprogramm liegen. Wenn man eine bereits bestehende Exceldatei (wie in meinem Fall) in dieses Archivierungsprogramm hinzufügt, wird diese in einem Storage zwischengespeichert. Aber ich habe ja erst die Exceldatei hinzugefügt und dann das Makro ausgeführt. Seltsam. Wenn ich händisch ändern will, sucht Excel aber zu erst in diesem Storage-Verzeichnis, d. h. ich muß ihm dann das neue Laufwerk sagen usw. und dann hat er das Bild und der Link geht. Wenn Du noch eine Idee hast, wäre das supi. Liegt nicht am Makro, das geht super.
LG Anke
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige