Excel Hyperlink updaten/automatisch fortführen nach Laufwerksänderung

[PanzerHase]

Hallo Leute,

ich habe das Problem, dass ich in einer Excel Tabelle ein Verzeichnis für Zeitschriften angelegt habe. Neben sonstigen Informationen und Angaben zum Datum, etc. existiert ein Hyperlink, welcher per Klick die in der Tabelle beschriebene Datei öffnet. Nun hat jemand sich den Spaß erlaubt und den Laufwerkbuchstaben geändert weswegen alle aktuellen Hyperlinks natürlich ins leere führen. (Kein Kommentar hierzu ^^) Die Tabelle nun so groß geworden, dass es manuell gar nicht mehr zu bewältigen ist alle Links selber zu bearbeiten. Nun möchte ich euch fragen ob es möglich ist ohne viel Aufwand die Hyperlinks wieder funktionsfähig zu machen. Eine Änderung des Laufwerkbuchstaben ist leider keine Option.
Leider kommt noch hinzu das in der Tabelle keinerlei Namen von den Datein auftauchen. Allgemein sind die Daten recht unterschiedlich benannt ALLERDINGS auch in der richtigen Reihenfolge. Somit müsste man "nur" einen Hyperlink erstellen, welcher quasi bei Datei 0 ansetzt und dann bis xxx durchläuft.

Hier ein Beispiel


Grüße

 

[Janush]

Kann man das vielleicht mit suchen/ersetzen machen? Also den alten Laufwerkbuchstaben durch den neuen ersetzen?

Sowas wie c:\ durch e:\ ersetzen...

 

[areiland]

Warum ist die Änderung des Laufwerksbuchstabens keine Option?

 

[alxtraxxx]

Suchen und Ersetzen und statt gemappten Buchstaben den UNC-Pfad nehmen ist Dir zuviel Arbeit? 0o

 

[PanzerHase]

Also es geht nicht um meine persönliche Tabelle sondern um die bei einem Kollegen auf Arbeit. Dort wurde der Laufwerkbuchstabe aus was für Gründen auch immer geändert und damit ist die Wiederherstellung keine Option. Die Stelle an der er arbeitet muss die Server usw. als gegeben ansehen.
Ich kann die näheren Umstände auch nicht weiter ausführen.

Suchen und ersetzen würde zu viel Zeit kosten. Die Tabelle gibt es seit ca. 7 Jahren und dort werden wöchentlich mehrere Einträge getätigt.
Es ist auch das erste mal das ich mit Hyperlinks in Verbindung trete und in Excel kenne ich nur Grundlagen. Bisher hatte ich nur diese Anleitung gefunden, die funktioniert aber nur wenn der Dateiname bereits in der Tabelle erfasst ist: https://www.youtube.com/watch?v=tCy_Z5EJ_RQ

 

[alxtraxxx]

Nuja wenn Google + Copy&Paste vom VBA-Makro und bissl anpassen, sprich max. 10 Minuten, zu lange dauern dann will ich mich auch mal für so nen Job bewerben.

 

[PanzerHase]

Na von welchen Makro denn? VBA habe ich heute zum ersten mal gelesen und das Makro welches ich gefunden habe sah mir nicht allzu schlüssig aus.
In dem Job hilft man auch nur aus und macht unter anderem solche Sachen wie die Tabelle füttern. Stichwort: Studentenjob. Der Job an sich hat auch rein gar nichts mit Computern zu tun. Es wird eben nur eine Liste mit Verlinkungen erstellt und mal drauf zugreifen zu können falls es relevant wird. Ich will mich jetzt nicht aus den Fenster lehnen aber es sollten schon um die 7.500+ Einträge sein. Da man dort nur 10h/Monat arbeiten soll gibt es andere Prioritäten als sie Verfügbaren Arbeitskräfte mehrere Stunden damit zu beschäftigen jeden Pfad zu aktualisieren.

 

[alxtraxxx]

7.500 Einträge ist eher niedlich als viel.https://excelribbon.tips.net/T008624_Changing_Huge_Numbers_of_Hyperlinks.html
Bei uns in der Firma gibts übrigens KEINE Bürojobs die nicht in der Stellenausschreibung "Gute Office-Kenntnisse" stehen haben, da müsste eher Google stehen. Aber Dein Kumpel kann ja immerhin Dich und Foren für sich arbeiten lassen.
Wenn die Technik nicht seine Aufgabe ist gibts normal jemand in der Firma der dafür zuständig ist.

 

[PanzerHase]

Danke! Das werde ich mir wohl jetzt mal ansehen. Anscheidend führt kein Weg an VBA vorbei wenn man nicht jede Zeile aufrufen und in ihr einen Buchstaben ändern möchte.

Aber um mal auf die Kritik zurückzukommen: Ich verstehe das Problem nicht. Ich hab vorher versucht selber Lösungen zu finden die aber mangels meiner Kenntnisse recht beschränkt sind. Danach habe ich eine Lösung via VBA und eine via normalen Befehlen (Video) gefunden. Da die VBA Lösung mich erstmal überfordert hat und die im Video gezeigte sich leider nicht anwenden lässt, dachte ich das ich hier fragen könnte ob jemand noch eine bessere Idee hat. Dabei habe ich zu keiner Zeit beabsichtigt jemanden hier dazu zu zwingen etwas rauszusuchen oder zu schreiben.. Die Beschreibung der Stelle ist auch recht vage damit man einfach nichts zuordnen kann (Datenschutz). Übrigens gehört das abändern der Links nicht zur Aufgabe in dem Job. Von daher erschleicht sich keiner seinen Stundenlohn durch die Problemlösung.

 

[effekt660]

Habe dir kurz mal was zusammengetippt. Das Sub musst du einfach im VBA Projekt unter 'DieseArbeitsmappe' einfügen.

Jedes Mal, wenn die Arbeitsmappe geöffnet wird, kontrolliert das Makro jeden Hyperlink auf dem ersten Arbeitsblatt. Wenn der Pfad nicht gefunden werden kann, werden alle verfügbaren Laufwerksbuchstaben getestet und wenn der richtige gefunden wurde, wird der Link entsprechend angepasst. Sollte keiner der Buchstaben passen, sprich der gesamte Link funktioniert nicht mehr, wird nichts geändert.

Ich garantiere für nichts. Also unbedingt in einer Kopie der eigentlichen Arbeitsmappe testen. Es kann sein, dass es bei 7500 Links ein paar Minuten dauert.

Private Sub Workbook_Open()
    Dim Link As Hyperlink, Zähler As Integer
    Zähler = 0
    
    'Für jeden Link im Arbeitsblatt 1
    For Each Link In ThisWorkbook.Worksheets(1).Hyperlinks
    
        'Wenn Pfad nicht gefunden werden kann, dann...
        If Dir(Link.Address) = "" Then
            Dim FSO As Object, LW As Variant, Pfad As String
            
            Pfad = Link.Address
            Set FSO = CreateObject("Scripting.filesystemobject")
            
            'Für jedes Laufwerk...
            For Each LW In FSO.drives
                If LW.isready Then
                    'Laufwerksbuchstaben aus Pfad löschen(ersten beiden Zeichen)
                    Pfad = Right(Pfad, Len(Pfad) - 2)
                    'Neuen Laufwerksbuchstaben einfügen
                    Pfad = LW & Pfad
                    
                    'Pfad mit jeweiligem Laufwerksbuchstaben gefunden?
                    If Dir(Pfad) <> "" Then
                        'Link ändern
                        Link.Parent.Hyperlinks(1).Address = Replace(LCase(Link.Address), LCase(Link.Address), LCase(Pfad))
                        Zähler = Zähler + 1
                        Exit For
                    End If
                End If
            Next
        End If
    Next
    
    If Zähler > 0 Then
        'Msgbox mit Anzahl der bearbeiteten Links
        MsgBox "Alle Hyperlinks wurden kontrolliert. Bei " & Zähler & " Hyperlinks wurde der Laufwerksbuchstabe angepasst."
    Else
        MsgBox "Alle Hyperlinks wurden kontrolliert."
    End If
End Sub

Edit: Ich hoffe das geht so, VB Code kann man hier offensichtlich nicht schön einfügen.

 

[PanzerHase]

Danke! Das funktioniert!
Das ist auch tatsächlich die Variante mit der man arbeiten kann wenn man über STRG+K Hyperlinks einfügt. Alternativ wenn man mit der Formel arbeitet (=Hyperlink(...)) gibt es diese Möglichkeit:

Sub FixHyperlinks()
    Dim wks As Worksheet
    Dim sOld As String
    Dim sNew As String
    Dim obenLinks As String
    Dim untenRechts As String
    Dim c As Variant

    Set wks = ActiveSheet
    sOld = "C:\"
    sNew = "D:\"
    
    obenLinks = "B2"
    untenRechts = "C5"
    
    For Each c In wks.Range(obenLinks, untenRechts)
        c.Formula = Replace(c.Formula, sOld, sNew)
    Next c
    
End Sub

Ich hoffe hier ist dann auch in Zukunft Leuten geholfen die eine Tabelle mit einer der beiden Varianten arbeiten und sich so eine menge Zeit sparen ^^

Hast du eine "Kaffekasse"?

 

[effekt660]

Kein Thema...

Aber wieso wird überhaupt (ständig) der Laufwerksbuchstabe geändert? Das bringt doch nur Probleme.

 

[PanzerHase]

Frag mich nicht. Die Website geht auch 1x in der Woche für mehrere Stunden offline wegen Wartungsarbeiten und gelegentlich geht auch mal gar nichts. Aber immerhin ist jetzt vorgesorgt wenn es noch mal passiere sollte

 

[effekt660]

Vielleicht würde ein Gespräch mit der IT Abteilung oder einer dafür verantwortlichen Person helfen.
Extra VB Programme schreiben zu müssen, um Baustellen zu beseitigen, welche durch andere (unnötig) hervorgerufen worden sind, ist ja, wie du selbst sagst, nicht deine Aufgabe.
Und unter guten Office Kenntnissen erwarten wohl eher die wenigsten Firmen, dass man VB oder andere Programmiersprachen gut beherrscht.

Und bei dem, von dir aufgeführten Beispiel, wäre ich auch noch etwas vorsichtig. So, wie es da steht, musst du es manuell starten, oder einem Button(o.Ä.) auf einem Arbeitsblatt zuweisen. Eine Gefahr besteht in Zeile 9 mit 'ActiveSheet'. Wenn du mehrere Arbeitsmappen offen hast und das ActiveSheet in dem Moment womöglich ein anderes ist, werden dir in der jeweiligen Mappe alle Links bearbeitet/beschädigt. Also lieber ein 'InThisWorkbook.ActiveSheet' oder besser 'InThisWorkbook.Worksheet("Index")' verwenden. Den Index könnte man automatisiert setzen.
Desweiteren liegt ein großer Nachteil in dem manuellen setzen der vier Variablen. Das ist fehleranfällig und bedeutet extra Aufwand, da du erst selbst suchen musst, wie der alte/neue Buchstabe ist. Die Range manuell zu setzen, kann auf sehr großen Blättern auch lästig sein.
Außerdem würde ich Abfragen machen, um zu testen, ob die Links überhaupt funktionieren. Den Alten nicht unbedingt, aber den Neuen, damit die Links zumindest nur durch funktionierende und nicht durch falsche Links ersetzt werden.
Gerade, wenn du nicht der Einzige bist, der mit der Mappe arbeitet, solltest du das von dir gewählte Skript(meins ist womöglich auch nicht absolut wasserdicht) mit Vorsicht anwenden und hinterher auf jeden Fall wieder aus dem Projekt entfernen, damit kein weiterer Benutzer irgendwas damit kaputt machen kann.