AW: werd eine verknüpfung nicht los
07.12.2006 00:56:52
Dieter.K
Hallo Sepp,
hier der Auszug aus einem Newsletter von "Smart....."!
Vielleicht hilft Dir das weiter.
Gruß
Dieter.K
"Viele Anwender stehen vor dem Problem, dass Sie externe Verknüpfungen möglichst schnell und komfortabel aus Tabellen oder Arbeitsmappen zu entfernen. Dabei müssen Sie eine Unterscheidung treffen zwischen normalen Formeln und Funktionen, Verknüpfungen innerhalb einer Mappe und externen Verknüpfungen zu anderen Arbeitsmappen.
Dabei können Sie leider nicht die Eigenschaft "HasFormula" einsetzen, da diese Eigenschaft nicht zwischen Formeln und Verknüpfungen unterscheidet. Suchen Sie stattdessen lieber im Formeltext nach dem Zeichen "[" oder "]". Wenn diese Zeichen im Formeltext auftauchen, dann handelt es sich ziemlich sicher um eine Verknüpfung zu einer anderen Arbeitsmappe.
Wo wird diese Aufgabenstellung benötigt?
Wenn Sie eine Tabelle per Mail versenden möchten und davor alle Verknüpfungen zu anderen Arbeitsmappen entfernt werden sollen, können Sie ganz elegant ein Makro einsetzen. Auch wenn Sie Excel-Tabelle mehr oder weniger unveränderbar "einfrieren" möchten, beispielsweise bei einer Planverabschiedung, muss dafür gesorgt werden, dass sich Daten nicht mehr durch eventuell veränderte Verknüpfungen im Nachhinein verändern.
Manuell externe Verknüpfungen zu finden und zu entfernen ist eine mühevolle Sache. Automatisieren Sie doch lieber diesen Vorgang durch ein Makro. Gehen Sie dazu wie folgt vor:
Drücken Sie die Tastenkombination Alt+F11, um in die Entwicklungsumgebung von Excel zu gelangen.
Wählen Sie aus dem Menü Einfügen den Befehl Modul.
Erfassen Sie das folgende Makro:
Sub ExterneLinksRaus()
Dim Zelle As Range
For Each Zelle In ActiveSheet.UsedRange
If InStr(Zelle.Formula, "]") > 0 Then
Zelle.Value = Zelle.Value
End If
Next Zelle
End Sub
Setzen Sie den Mauszeiger in das Makro und drücken Sie die Taste F5.
In einer For-Each-Next - Schleife durchlaufen Sie alle Zellen des verwendeten Bereichs einer Tabelle. Innerhalb der Schleife prüfen Sie mit Hilfe der Funktion "Instr", ob in einer eventuellen Formel das Zeichen "]" vorkommt. Wenn das der Fall ist, handelt es sich ziemlich sicher um eine Verknüpfung zu einer externen Arbeitsmappe. In diesem Fall weisen Sie der Zelle einen neuen Wert zu und überschreiben damit die in der Zelle enthaltene Formel.
Möchten Sie nicht nur die externen Verknüpfungen aus einer Tabelle, sondern aus allen Tabellen einer Arbeitsmappe entfernen, erweitern Sie obige Makro um eine weitere For-Each-Next-Schleife, die alle Tabellen der Arbeitsmappe nacheinander abarbeitet.
Sub ExterneLinksRausGanzeMappe()
Dim Blatt As Worksheet
Dim Zelle As Range
For Each Blatt In ThisWorkbook.Worksheets
For Each Zelle In Blatt.UsedRange
If InStr(Zelle.Formula, "]") > 0 Then
Zelle.Value = Zelle.Value
End If
Next Zelle
Next Blatt
End Sub
Setzen Sie den Mauszeiger in das Makro und drücken Sie die Taste F5.
Nach dem Durchlaufen des letzten Makros existieren in der Arbeitsmappe keine externen Verknüpfungen zu anderen Arbeitsmappen mehr. Sie können das kontrollieren, indem Sie versuchen, aus dem Menü Bearbeiten den Befehl Verknüpfungen anwählen. Dieser Befehl ist nur auswählbar, wenn noch Verknüpfungen in der Arbeitsmappe existieren. In unserem Fall bleibt der Befehl deaktiviert."