Nicht mehr genutzte Verknüpfungen in Excel

Dieses Thema Nicht mehr genutzte Verknüpfungen in Excel im Forum "Microsoft Office Suite" wurde erstellt von estoep, 27. Sep. 2004.

Thema: Nicht mehr genutzte Verknüpfungen in Excel Hallole, ich habe folgendes Problem: Ich habe von meinem Vorgänger jede Menge Excel-Sheets übernommen und auf mein...

  1. Hallole,
    ich habe folgendes Problem:
    Ich habe von meinem Vorgänger jede Menge Excel-Sheets übernommen
    und auf mein Verzeichnis verschoben.
    Diese Sheets enthalten:
    - Verknüpfungen
    - Pivot-Tabellen
    - Grafiken
    Beim öffnen der Sheets bekomme ich die Frage nach dem aktualisieren der Verknüpfungen.
    Manche dieser Verknüpfungsdateien gibt es aber nicht mehr. :-[

    Frage:
    1. Kann es überhaupt sein, dass Excel Verknüpfungen anzeigt, die es
    nicht mehr benötigt?

    2. Wie kann ich feststellen, ob die Verknüpfungen überhaupt noch im Sheet benutzt werden?

    Danke für Eure Hilfe
     
  2. Excel zeigt die Verknüpfungen an wenn die Formeln ursprünglich so erstellt worden sind. Wird wohl damals seinen Grund gehabt haben ...

    Wenn du die Aktualisierung der Verknüpfungen bestätigst und sie sind nicht mehr vorhanden, dann meckert Excel und will den Speicherort der verknüpften Datei wissen. Wenn diese Datei nicht mehr vorhanden ist, kannst du eh nix mehr machen.

    Um herauszufinden, welche Zelle eine Verknüpfung hat, gehst du in den Suchmodus (Strg+F) und stellst unten Suchen in: Formeln ein.
    Dann suchst du in jedem Tabellenblatt nach der eckigen Klammer [[
    Dabei stößt du dann auf Zellen mit solchem Inhalt:
    =[Blabla.xls]Tabelle1!A1
    Diese Zelle ist verknüpft mit der Zelle A1 im Tabellenblatt Tabelle1 in der Datei Blabla.xls

    Nachdem du die Quelldatei geprüft und für überflüssig befunden hast, kannst du die Zelle mit Strg+C und Bearbeiten >Inhalte einfügen > Werte entknüpfen.

    Weil man dieses Werte einfügen praktisch dauernd braucht, habe ich mir dafür eine Schaltfläche gebastelt und mit folgendem Makro belegt:
    Code:
    Sub Werte_einfügen()
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End Sub
    
    Kann ich jedem nur empfehlen

    Du kannst Werte einfügen auch blind übers ganze Tabellenblatt machen, aber dann gehen alle Formeln flöten - nicht nur die mit externen Verknüpfungen.

    Verknüpfungen können auch bei Buttons vorliegen, die in Exceltabellen eingefügt sind, wenn sich diese auf ein in einer anderen Datei befindliches Makro beziehen. Da mußt du wohl bei jedem Button die Makro-Zuordnung manuell überprüfen.
    Auch Hyperlinks können Verknüpfungen nach draußen enthalten. Wie man die mittels Suchmodus entdeckt, weiß ich allerdings nicht.
     
  3. @klexy:
    Aber jetzt ;D

    Gruß Matjes ;)

    Code:
    Option Explicit
    '**********************************************************
    Sub HYPERLINKS_Entfernen_MitAbfrage()
       ->im aktiven Blatt wird für alle Hyperlinks abgefragt:
       ->- die Zellanzeige und den Hyperlink entfernen
       ->- nur den Hyperlink entfernen
       ->- überspringen
       ->Entsprechend der Auswahl wird die Aktion durchgeführt.
    '**********************************************************
      Dim h As Hyperlink, ret As Integer
      Dim s_ZelleAnzeige As String, s_Hyperlink As String, s_ZelleAddr As String
      
      For Each h In ActiveWorkbook.ActiveSheet.Hyperlinks
       ->Hyperlink
        s_Hyperlink = h.Address
       ->angezeigter Wert
        s_ZelleAnzeige = h.Parent.Value
       ->Adresse auf dem Blatt
        s_ZelleAddr = h.Parent.Address(RowAbsolute:=False, _
                                       ColumnAbsolute:=False, _
                                       ReferenceStyle:=xlA1)
       ->Zelle mit Hyperlink selektieren
        h.Range.Select
        
        ret = MsgBox( _
            Hyperlink in  & s_ZelleAddr & vbLf & _
            Zelleanzeige:  & s_ZelleAnzeige & vbLf & _
            Adresse:  & s_Hyperlink & vbLf & vbLf & _
            Wollen Sie  & vbLf & _
            - die Zellanzeige und den Hyperlink entfernen -> Ja & vbLf & _
            - nur den Hyperlink entfernen -> Nein & vbLf & _
            - überspringen -> Abbrechen, _
            vbQuestion + vbYesNoCancel + vbDefaultButton3)
            
        If ret = vbYes Then->Ja???
         ->die Zellanzeige und den Hyperlink entfernen
          h.Parent.Value = 
        ElseIf ret = vbNo Then->Nein???
         ->nur den Hyperlink entfernen
          h.Delete
        Else
         ->Überspringen
        End If
      Next
    End Sub
    
     
  4. und noch was ganz gefahrloses :D

    Gruß Matjes :)

    Code:
    Option Explicit
    '**********************************************************
    Sub HYPERLINKS_AlleEinerSeiteMarkieren()
       ->alle Hyperlinks des Blattes werden markiert
    '**********************************************************
      Dim r As Range, x As Long
      If ActiveSheet.Hyperlinks.Count Then
        Set r = ActiveSheet.Hyperlinks(1).Parent.Cells
        For x = 2 To ActiveSheet.Hyperlinks.Count
          Set r = Application.Union(r, _
                      ActiveSheet.Hyperlinks(x).Parent.Cells)
        Next
        r.Select
        Set r = Nothing
      Else
        MsgBox (kein Hyperlink auf dem Blatt zu finden :-()
      End If
    End Sub
    
     
  5. WAAAhnsinn! ;D ;D ;D
    Nach dem Markieren mit dem zweiten Makro geht man einfach auf die gelbe Füllfarbe und kann die Zellen danach in Ruhe untersuchen.

    Beim ersten Makro wird allerdings nur dann eine Adresse angezeigt wenn es eine externe Datei ist. Kann da auch eine interne Sprungmarke angezeigt werden (z.B. auf A3 in Tabellenblatt5?)

    Und was bedeutet bei deinen Makros Option explicit? Wenn man das löscht, funktionieren sie trotzdem. Außerdem ärgern diese Einträge das jeweils darüberliegende Makro.
     
  6. So ein Zufall. Exakt so eine Datei hat mich heute auch wieder genervt. Werde die Tipps morgen gleich mal ausprobieren. :)
     
  7. Hi klexy,

    mit der->Option explicit'-Anweisung am Anfang eines Moduls wird erzwungen, das innerhalb des Moduls alle Variablen deklariert - sprich ihr Typ definiert - sein muß, z.B. per Dim, Public, ... usw..

    Beim Kompilieren des VB-Projektes werden dann alle nicht deklarierten Variablen als Fehler angezeigt.

    Wenn man z.B. eine Variable l_cnt verwendet und man verschreibt sich im Programm (z.B. l_count), wird dies bei der Kompilierung angezeigt. Ohne die->Option Explicit'-Anweisung macht Excel einfach eine weitere Variable daraus. Im Programm führt das dann zur Fehlersuche (Stecknadel im Heuhaufen).

    Es macht zwar etwas mehr Mühe alle Variablen zu deklarieren, aber die Zeit spart man hinterher bei der Fehlersuche.

    Gruß Matjes :)
     
  8. Hi klexy,

    so, jetzt wird auch die Subadress mit angezeigt :D

    Ausserdem hab ich für->Formeln mit externem Verweis' auch einen Makro zum Markieren angefügt. :D

    Vorschläge und Anregungen zum weiteren Ausbau sind jederzeit willkommen. ;D

    Gruß Matjes ;)

    (jetzt mit ausgeblendetem->Option Explicit' ;D
    - darf übrigens in jedem Modul nur einmal am Anfang stehen)

    Code:
    '**********************************************************
    Sub HYPERLINKS_AlleEinerSeiteMarkieren()
       ->alle Hyperlinks des Blattes werden markiert
    '**********************************************************
      Dim r As Range, x As Long
      If ActiveSheet.Hyperlinks.Count Then
        Set r = ActiveSheet.Hyperlinks(1).Parent.Cells
        For x = 2 To ActiveSheet.Hyperlinks.Count
          Set r = Application.Union(r, _
                      ActiveSheet.Hyperlinks(x).Parent.Cells)
        Next
        r.Select
        Set r = Nothing
      Else
        MsgBox (kein Hyperlink auf dem Blatt zu finden :-()
      End If
    End Sub
    '**********************************************************
    Sub HYPERLINKS_Entfernen_MitAbfrage()
       ->im aktiven Blatt wird für alle Hyperlinks abgefragt:
       ->- die Zellanzeige und der Hyperlink entfernen
       ->- nur den Hyperlink entfernen
       ->- überspringen
       ->Entsprechend der Auswahl wird die Aktion durchgeführt.
    '**********************************************************
      Dim h As Hyperlink, ret As Integer
      Dim s_ZelleAnzeige As String, s_ZelleAddr As String
      Dim s_HyperlinkAddr As String, s_HyperlinkSubAddr As String
      
      For Each h In ActiveWorkbook.ActiveSheet.Hyperlinks
       ->Hyperlink
        s_HyperlinkAddr = h.Address
       ->Hyperlink
        s_HyperlinkSubAddr = h.SubAddress
       ->angezeigter Wert
        s_ZelleAnzeige = h.Parent.Value
       ->Adresse auf dem Blatt
        s_ZelleAddr = h.Parent.Address(RowAbsolute:=False, _
                                       ColumnAbsolute:=False, _
                                       ReferenceStyle:=xlA1)
       ->Zelle mit Hyperlink selektieren
        h.Range.Select
        
        ret = MsgBox( _
            Hyperlink in  & s_ZelleAddr & vbLf & _
            Zelleanzeige:  & s_ZelleAnzeige & vbLf & _
            Adresse1:  & s_HyperlinkAddr & vbLf & _
            Adresse2:  & s_HyperlinkSubAddr & vbLf & vbLf & _
            Wollen Sie  & vbLf & _
            - die Zellanzeige und den Hyperlink entfernen -> Ja & vbLf & _
            - nur den Hyperlink entfernen -> Nein & vbLf & _
            - überspringen -> Abbrechen, _
            vbQuestion + vbYesNoCancel + vbDefaultButton3)
            
        If ret = vbYes Then->Ja???
         ->die Zellanzeige und den Hyperlink entfernen
          h.Parent.Value = 
        ElseIf ret = vbNo Then->Nein???
         ->nur den Hyperlink entfernen
          h.Delete
        Else
         ->Überspringen
        End If
      Next
    End Sub
    '**********************************************************
    Sub FORMEL_ExterneVerweiseAlleEinerSeiteMarkieren()
       ->alle Formeln des Blattes werden markiert,
       ->die eine externen Verweis einhalten
    '**********************************************************
      Dim r As Range, Zelle As Range, l_cnt As Long
      Dim pos1 As Long, pos2 As Long, pos3 As Long
     ->Formeln mit externen Verweisen suchen
      l_cnt = 0->Zaehler initialisieren
      For Each Zelle In ActiveSheet.UsedRange
        If Left(Zelle.Formula, 1) = = Then
          pos1 = InStr(1, Zelle.Formula, [)
          If (pos1 > 0) Then
            pos2 = InStr(1, Zelle.Formula, ])
            If (pos2 > 0) And (pos2 > pos1) Then
              pos1 = InStr(1, Zelle.Formula, !)
              If (pos1 > 0) And (pos1 > pos2) Then
               ->Jo, ist eine Zelle mit externem Verweis
               ->Muster ...[....]....!.....
                l_cnt = l_cnt + 1
                If l_cnt = 1 Then
                  Set r = Zelle.Cells
                Else
                  Set r = Application.Union(r, Zelle.Cells)
                End If
              End If
            End If
          End If
        End If
      Next
     ->gefundene Zellen markieren
      If l_cnt > 0 Then
        r.Select
        Set r = Nothing
      Else
        MsgBox (kein Formel mit externem Verweis zu finden :-()
      End If
    End Sub
     
  9. Danke, Ihr habt mir echt geholfen! :)

    Leider bekomme ich unter Verknüpfungen
    nicht den kompletten Pfad der verknüpften Datei angezeigt.
    Da ich Dateien mit gleichen Namen in unterschiedlichen Verzeichnissen habe,
    kann ich leider nicht erkennen, welche Verknüpfung ursprünglich wohin verweist. :-[

    Daher muß ich jetzt jede Celle einzeln durchgehen und die Verknüpfung ersetzen. Da würde ein Macro helfen, dass mir über alle
    Sheets eines WBs in den Formeln einen bestimmten Text durch einen
    neuen ersetzt.
    Gibt es so etwas hier im Forum?
     
  10. Hi estoep,

    jetzt ja ;D

    Gruß Matjes ;)

    Code:
    '**********************************************************
    Sub FORMEL_ErsetzenTextAlleInMappe()
       ->in alle Formeln jedes Blattes der Arbeitsmappe wird
       ->der Suchtext gesucht und in der jeweiligen Formel ersetzt
       ->
       ->Suchtext und Ersetzentext - werden abgefragt
        
       ->Option: Mehrfachersetzen in einer Formel - wird abgefragt
       ->in der jeweiligen Formel:
       ->- ja: werden alle Fundstellen ersetzt
       ->- nein: wird nur die erste Fundstelle ersetzt
       ->
       ->Option: Einzelabfrage vor dem Ersetzen - wird abgefragt
       ->- ja: vor jedem Ersetzen wird die Formel und die
       ->geänderte Formel angezeigt.
       ->Entspechend der Wahl ja/nein wird die Ersetzung
       ->durchgführt/übersprungen
       ->-nein: es wird ohne Nachfrage ersetzt
    '**********************************************************
      Dim ws As Worksheet, ws_akt As Worksheet
      Dim s_Suchen As String, s_Ersetzen As String
      Dim ret As Integer, ret2 As Integer
      Dim b_mehrfachersetzen As Boolean, b_einzelNachfrage As Boolean
      Dim s_Text_mehrfachersetzen As String
      Dim s_Text_einzelNachfrage As String
      
      Set ws_akt = ActiveSheet
      ret = vbNo
      Do While ret = vbNo
      
      s_Suchen = InputBox( _
        Bitte geben Sie den in Formeln zu ersetzenden Text ein. & _
        vbLf & _
        (Groß-/Kleinschreibung beahten!) & vbLf & _
        Keine Eingabe -> Abbruch, _
        Suchtext-Eingabe, s_Suchen)
      If s_Suchen =  Then Exit Sub
      
      s_Ersetzen = InputBox( _
        Bitte geben Sie den Ersatztext ein. & _
        (Groß-/Kleinschreibung beahten!), _
        Ersatztext-Eingabe, s_Ersetzen)
        
      ret2 = MsgBox( _
        Wenn der Suchbegriff mehrfach in einer Formel enthalten ist, & vbLf & _
        sollen dann mehrfach ersetzt werden?, _
        vbDefaultButton2 + vbYesNo + vbQuestion)
      If ret2 = vbYes Then
        b_mehrfachersetzen = True: s_Text_mehrfachersetzen = ja
      Else
        b_mehrfachersetzen = False: s_Text_mehrfachersetzen = nein
      End If
      
      ret2 = MsgBox( _
        Soll jedes Ersetzen vorher bestätigt werden?, _
        vbDefaultButton2 + vbYesNo + vbQuestion)
      If ret2 = vbYes Then
        b_einzelNachfrage = True: s_Text_einzelNachfrage = ja
      Else
        b_einzelNachfrage = False: s_Text_einzelNachfrage = nein
      End If
      
      ret = MsgBox( _
        folgender Text: & vbLf & vbLf & s_Suchen & vbLf & vbLf & _
        soll in allen Formeln der Arbeitsmappe durch: & vbLf & vbLf & _
        s_Ersetzen & vbLf & vbLf & ersetzt werden ? & vbLf & vbLf & _
        Mehrfachersetzung:  & s_Text_mehrfachersetzen & vbLf & _
        Einzelnachfrage:  & s_Text_einzelNachfrage, _
        vbDefaultButton2 + vbYesNo + vbQuestion)
      
      Loop
      
      
      For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        Call FORMEL_ErsetzenTextAlleEinerSeite( _
                ws, s_Suchen, s_Ersetzen, b_mehrfachersetzen, b_einzelNachfrage)
      Next
      
      ws_akt.Activate
      Set ws_akt = Nothing
    End Sub
    
    '**********************************************************
    Function FORMEL_ErsetzenTextAlleEinerSeite(ws As Worksheet, _
                          s_Suchen As String, s_Ersetzen As String, _
                          b_mehrfachersetzen As Boolean, _
                          b_einzelNachfrage As Boolean)
    '**********************************************************
      Dim Zelle As Range, pos1 As Long, s_tmp As String, ret As Integer
     ->Formeln mit externen Verweisen suchen
      If Len(s_Suchen) = 0 Then
        MsgBox ( _
          FORMEL_ErsetzenTextAlleEinerSeite:  & vbLf & _
          kein Suchtext angegeben :-()
      Else
        For Each Zelle In ws.UsedRange
          If Left(Zelle.Formula, 1) = = Then
            pos1 = 1
            Do While pos1 > 0
              pos1 = InStr(pos1 + 1, Zelle.Formula, s_Suchen)
              If (pos1 > 0) Then
               ->suchtext gefunden
                s_tmp = Left(Zelle.Formula, pos1 - 1) & s_Ersetzen & _
                Right(Zelle.Formula, Len(Zelle.Formula) - pos1 + 1 - Len(s_Suchen))
                If b_einzelNachfrage Then
                  ret = MsgBox( _
                    soll Formel & vbLf & vbLf & Zelle.Formula & vbLf & vbLf & _
                    durch Formel & vbLf & vbLf & _
                    s_tmp & vbLf & vbLf & ersetzt werden ?, _
                    vbDefaultButton2 + vbYesNo + vbQuestion)
                  If ret = vbYes Then
                    Application.DisplayAlerts = False
                    Zelle.Formula = s_tmp
                    Application.DisplayAlerts = True
                   ->Suchanfangsposition auf den noch nicht behandlten
                   ->Stringteil stellen, da sonst im ersetzen Text gesucht wird
                    pos1 = Len(Left(Zelle.Formula, pos1 - 1) & s_Ersetzen)
                  Else
                   ->Suchanfangsposition auf den noch nicht behandlten
                   ->Stringteil stellen, da sonst im ersetzen Text gesucht wird
                    pos1 = Len(Left(Zelle.Formula, pos1 - 1) & s_Suchen)
                  End If
                Else
                  Application.DisplayAlerts = False
                  Zelle.Formula = s_tmp
                  Application.DisplayAlerts = True
                 ->Suchanfangsposition auf den noch nicht behandlten
                 ->Stringteil stellen, da sonst im ersetzen Text gesucht wird
                  pos1 = Len(Left(Zelle.Formula, pos1 - 1) & s_Ersetzen)
                End If
                If b_mehrfachersetzen = False Then Exit Do
              End If
            Loop
          End If
        Next
      End If
    End Function
     
Die Seite wird geladen...

Nicht mehr genutzte Verknüpfungen in Excel - Ähnliche Themen

Forum Datum
Festplatte geht aus und danach nicht mehr an Windows 10 Forum Dienstag um 01:23 Uhr
EXE-Dateien nach einer Zeit nicht mehr ausführbar Windows 8 Forum Sonntag um 21:21 Uhr
Task Symbole , Bildsymbole werden nicht mehr angezeigt Windows 8 Forum 25. Okt. 2016
Internetzugang per LAN funzt nicht mehr Netzwerk 11. Okt. 2016
Heimnetzwerk findet dateien nicht mehr Netzwerk 30. Aug. 2016