Office: Hyperlinks prüfen und ändern

Hallo,

ich brauche dringend eure Hilfe.
Ich habe ca. 2000 Excel-Dateien mit sehr sehr vielen Hyperlinks zu Dateien auf einem Server. Jetzt hat sich der Pfad fast
komplett geändert.
Ich möchte jetzt ein Makro laufen lassen, welches mir anzeigt welche Hyperlinks "tot" sind und welche erreichbar.

Ich bin jetzt schon eine Ewigkeit damit beschäftigt und habe auch schon einiges geschafft. Aber jetzt komme ich nicht weiter.

Folgendes Makro funktioniert. Aber ich möchte nicht nur die "toten" Links rot gefärbt haben sondern auch die die ok sind grün
und am Ende die Mitteilung "Prüfung abgeschlossen". Außerdem sollen die Dateien nicht geöffnet werden. Da ich die Links
dann ändere (siehe Makro 2) möchte ich die Prüfung nochmals durchlaufen lassen, dazu muss aber vorher jedesmal die rote
Färbung wieder raus. Wie mache ich das?

Sub Hyperlinks_testen()
Dim H As Hyperlink

ActiveSheet.Unprotect

On Error GoTo LinkTot

For Each H In ActiveSheet.Hyperlinks
H.Follow True
Next
Exit Sub

LinkTot:
H.Range.Interior.ColorIndex = 3
Resume Next
End Sub

Außerdem suche ich nach einer Möglichkeit (Makro) wie ich die Hyperlinkbasis ändern kann, ohne manuell diese 2000 Dateien
öffnen und speichern zu müssen. Kann ich eventuell dieses Makro dazu nutzen ?

Sub Hyperlinks_aendern()
Dim hyper As Hyperlink, strPath As String
strPath = "Risse\"
For Each hyper In ActiveSheet.Hyperlinks
If InStr(hyper.Address, strPath) 0 Then
hyper.Address = Replace(hyper.Address, strPath, "")
End If
Next hyper

strPath = "Grenzniederschriften\"
For Each hyper In ActiveSheet.Hyperlinks
If InStr(hyper.Address, strPath) 0 Then
hyper.Address = Replace(hyper.Address, strPath, "")
End If
Next hyper

strPath = "Koordinaten\"
For Each hyper In ActiveSheet.Hyperlinks
If InStr(hyper.Address, strPath) 0 Then
hyper.Address = Replace(hyper.Address, strPath, "")
End If
Next hyper

strPath = "1698\"
For Each hyper In ActiveSheet.Hyperlinks
If InStr(hyper.Address, strPath) 0 Then
hyper.Address = Replace(hyper.Address, strPath, "Gnoien_1698\")
End If
Next hyper

End Sub

Bitte helft mir. Ich benutze EXCEL 2010 und habe keine Ahnung von VBA!!!

Danke schon mal im Voraus.

:)

 

Hi Katjuscha,

zu Frage 1 - versuche es mal so:

Code:

Zu Frage 2: ich würde sagen, der Code sollte funktionieren, habe aber jetzt keine Zeit ihn zu testen, da ich weg muss - das kannst du aber selbst machen. Bitte jedoch vorher Sicherheitskopie deiner Mappe anlegen!

 

Hi Katjuscha,

zu Beverlys Code hier mein Vorschlag zum 2. Teil.
Ich hab noch eine Abfrage hinzugefügt ob das Feld ROT ist (Grüne muss man ja nicht ändern)
Dann noch ein Array mit der I-Schleife um mehrere Ordner abzufragen, die kannst Du hinzufügen mit 3, 4, ...) und das "For I = 1 to 2" ändern, also die 2 auf den höchsten Wert den Du hinzugefügt hast.

Es wird auch noch einmal Teil 1 aufgerufen und wenn der neue Hyperlink tut, dann wird das Feld grün.

Und wie Beverly schon sagte, ALLE 2000 Excels in ein sicheres Verzeichnis kopieren bevor Du den Code probierst.

Hast Du mehr als 1 Sheet in einem Excel, oder alle Excel in einem Ordner? dann könnte man noch mehr automatisch laufen lassen.

Code:

 

Hi Katjuscha,

Das habe ich nicht geschrieben - mein Hinweis war, dass eine Sicherkeitskopie der Abeitsmappe (mit den zu ändernden Hyperlinks) angelegt werden sollte, denn nach Ausführung des Codes, kann man die Änderungen nicht mehr rückgängig machen. Diese Änderungen haben aber KEINEN Einfluss auf die Arbeitsmappen, die in den betreffenden Ordnern liegen - sie können also ruhig dort verbleiben wo sie sind.

 

Hallo Karin, Hallo Gerhard,

danke für eure schnelle Antwort.
Leider funktioniert der Code von Karin nicht ganz. Es werden alle Links rot gefärbt auch die die OK sind. Das ging zwar mit meinem Code, allerdings nicht die grün Färbung und alle Dateien wurden geöffnet. Was schon sehr lange dauern kann. Ist in deinem Code auch eine Entfärbung enthalten?
Liegt das eventuell auch an der Hyperlinkbasis?

Der Code von Gerhard läuft garnicht.
Es kommt sofort die Fehlermeldung: "Fehler beim Kompilieren: Typen unverträglich."
Habe keine Ahnung was das bedeutet!!!

Würdet ihr mir euren Code übersetzen, damit ich weiss was dieses "chinesisch" bedeutet???

Hier mal ein Beispiel was ich vorhabe:
alt
Hyperlinkbasis: T:\Archiv\Rissarchiv\
Hyperlink: Risse\1763\036\1763-036-030-FFR.tif
Koordinaten\1763\036\1763-036-030-KVZ.tif
Niederschriften\1763\036\1763-036-030-GN.pdf

neu
Hyperlinkbasis: G:\Archiv\Zahlenwerk\Nachweise\
Hyperlink: Teterow_1763\036\1763-036-030-FFR.tif
Teterow_1763\036\1763-036-030-KVZ.tif
Teterow_1763\036\1763-036-030-GN.pdf

Eigentlich muss ich "nur" die Hyperlinkbasis austauschen und Risse, Koordinaten und Grenzniederschriften löschen. Das löschen klappt mit dem 2. Code super. Aber kann ich auch die Basis durch einen Code austauschen? Wenn ja wie? Bei 2000 Dateien kann das ganz schön mühselig sein.

Das kniffeligste Problem was ich sehe ist:
Der Ordner 1763 wurde umbenannt in Teterow_1763. Teterow ist der Ort und 1763 der Schlüssel. Jetzt habe ich aber ca 400 Schlüssel wo der Ortsname hinzugefügt wurde. Kann man über einen Code vielleicht auf eine EXCEL-Tabelle zugreifen in der alle Änderungen auf gelistet sind?

so vielleicht: (Eine Liste mit Spalte A und B habe ich schon und durch VERKETTEN könnte ich Spalte C erzeugen)

spalte A Spalte B Spalte C
1763 Teterow Teterow_1763
1834 Güstrow Güstrow_1834
1698 Gnoien Gnoien_1698

wenn "spalte a"\ dann "Spalte c"\ ???

Ist sowas überhaupt möglich ??? Vielen Dank für eure Geduld.

Gruß Katja

 

Hi Katja,

Wenn Du Dateien verschiebst, z.B. zum testen, musst Du das Excel mit dem Code schliessen und wieder öffnen, denn Excel bekommt das Verschieben von Dateien nicht mit.

Bei meinem Code teste das mal:

Code:

Und ja, eine Tabelle mit Hyperlinks Alt und Neu in 2 Spalten nebeneinander wäre sehr hilfreich für Dein Problem

 

Hallo Gerhard,

leider passiert überhaupt nichts,wenn ich deinen Code starte. Es kommt nicht einmal eine Fehlermeldung. Muss ich noch was anpassen?

Was bedeutet der Teil?

strPathAlt(1) = "1698\"
strPathNeu(1) = "Gnoien_1698\"
strPathAlt(2) = "AAA\"
strPathNeu(2) = "Gnoien_AAA\"

Ich habe nicht nur den Ort Gnoien sondern noch viele andere mit unterschiedlichen Schlüssel-Nr. und es kommen tlw. mehrere Orte in einer Tabelle vor
Wie müsste der Code für den Zugriff auf eine EXCEL-Tabelle lauten?
Ich habe keine Ahnung. *confused.gif* Selbst bei deinem Code vermute ich nur.

Gruß Katja

 

Hi Katja,
mein Code oben müsste angepasst werden auf Deine Dateien, wenn Du ihn so ausprobierst kann es sein dass nichts passiert.

Es wird gesucht ob eine Datei die vorher in 1698 war und nach der Pfadänderung nun in Gnoien_1698 ist mit dem alten Hyperlink in der Excel wo der Code gerade läuft drin ist. Dann wird dieser Hyperlink auf den neuen Pfad geändert.
Hier mit Kommentaren:

Code:

Hast Du eine Excel mit alten/neuen Pfaden, zb Spalte A alter Pfad und Spalte B neuer Pfad? So wie Du hier beschrieben hast:

Und hast Du eine Tabelle mit allen Hyperlink Excels die Du prüfen und vielleicht ändern musst?
Damit kann man auf einen Rutsch alle Hyperlinks ändern. Kannst Du die Tabellen hier hochladen? Dann kann ich schauen wie man auf die Tabellen zugreifen kann.

 

Hi Katja,

Das erledigen diese Zeilen:

Code:

Weshalb der Code bei dir nicht funktioniert kann ich leider nicht sagen - bei mir werden die korrekten Hyperlinks grün und die nicht mehr gültigen rot markiert.

 

Hallo Karin, Hallo Gerhard,

in der einen Dateien sind die Änderungen enthalten, die ich durchführen muss. Die Datei 1834_005.xls ist eine der Dateien in der ich die Änderungen durchführen muss. Davon gibt es ca. 2000 Stück und abgelegt sind sie alle in einem Ordner auf einem Server. Eine Liste aller Hyperlinks die geändert werden müssen habe ich nicht.

Karin kann es sein, dass deine Code nicht richtig klappt, weil ich keinen Laufwerksbuchstaben als Basis benutze?

Gruß Katja

 

Hi Katja,

Das kann ich nicht sagen - Gerhard verwendet ja für das Testen der Hyperlinks den von mir geposteten Code 1:1.

 

Hi Karin,

da hast du zwar recht, aber bei deinem Code sehe ich das was passiert, also die Links rot werden. Bei dem Teil von Gerhard passiert nichts. Aber ich weis auch nicht was ich da anpassen soll.

Gruß Katja

 

Hi katja,

ich habe den Codeteil von Gerhard zum Umbenennen nicht getestet. Mir ist aber aufgefallen, dass du einen Blattschutz auf der Tabelle hast - was passiert, wenn du den Blattschutz vorher aufhebst?

 

Hi Katja, Hi Karin,

das unser Code so bei Dir nicht tut ist nun klar, die Hyperlinks verweisen auf einen Server, ich habe es an einer lokalen Testtabelle probiert, aber der Code sollte so funtionieren. Den von Karin angesprochenen Blattschutz konnte ich ohne Passwort entfernen, dass kann später auch ein Makro machen.

Und wenn ich mir das Vorhaben mit den 2 Excels so anschaue, sollten wir die generelle Vorgehensweise erst mal festlegen, bevor wir ans Erstellen von Code denken.

Wir brauchen also eine neue Excel mit einen Code der alle Excels, die Hyperlink haben, den Hypelink prüft

Wir wollen also (aus "Hyperlink_Aenderungen.xls") und in dieser Reihenfolge:

1. wenn in Hyperlinks \\hpgue06\G-Archiv\Riss-Archiv\ vorkommt, dass durch \\fs02.local\Archiv62\Zahlenwerk\Nachweise\ innerhalb des Hyperlinkpfades ersetzen

2. Hyperlinks die enthalten z,B, 2074\ wird dann, gemäß der Tabelle, dieser Teil ersetzt durch Admannshagen_2074\

3. Hyperlinks die enthalten:
Grenzniederschriften\ oder Koordinaten\ oder Risse\
werden ganz und komplett gelöscht ??? bitte prüfen

und \\hpgue06\G-Archiv\Riss-Archiv\ ist in 1. schon drin und wird umgewandelt in eine neue Hypelinkbasis, soll aber gelöscht werden ??? kannst Du das bitte prüfen?

Alle Hyperlinks werden rot oder grün je nach Funktionalität hinterlegt.

Ich denke das bekommen wir hin, dauert vielleicht einige Tage

 

\@Gerhard,

Mein Hinweis, dass dein Code für das Umbenennen nicht funktioniert, zielte auf etwas anderes: es ging darum dass bei aktivem Blattschutz nichts umbenannt werden kann - versuche einfach mal bei aktiviertem Blattschutz, irgend etwas von Hand an einem der vorhandenen Hyperlinks zu ändern. Wird der Blattschutz jedoch aufgehoben, ist das möglich und auch dein Code für das Umbenennen funktioniert dann völlig problemlos. Ob allerdings das richtige richtig umbenannt wird, habe ich nicht getestet.


@Katja,

könntest du bitte ganz genau erklären, was wie in den beiden hochgeladenen Arbeitsmappen passieren soll?