AW: Verknüpfungen suchen
Heinz
Hallo Peter,
hier eine Anleitung von SmartTools:
Verknüpfungen auf externe Arbeitsmappen löschen - SmartTools
Versionen: Excel 97, 2000, 2002/XP und 2003
Excel zeigt beim Öffnen einer Datei eine Meldung wie "Diese Datei enthält Verknüpfungen zu anderen Datenquellen" an, wenn Sie in Formeln auf Inhalte aus anderen Arbeitsmappen verweisen. Solange Sie wissen, dass die geöffnete Arbeitsmappe externe Verknüpfungen enthält und das auch in Ihrem Sinne ist, hat die Meldung durchaus ihre Berechtigung, denn schließlich erlaubt sie die sofortige Aktualisierung der Basisdaten.
Problematisch wird es, wenn Sie die externen Verknüpfungen entfernen wollen. Das gilt in besonderem Maße für Excel 97 und 2000, da das Dialogfeld, das Sie mit dem Befehl Bearbeiten-Verknüpfungen aufrufen, keine Option zum Löschen von Verknüpfungen bietet.
Dieses Manko besteht seit Excel 2002 erfreulicherweise nicht mehr. Dennoch können Sie auch in den neueren Excel-Versionen die eine oder andere Überraschung erleben, denn nicht immer funktioniert das Löschen der Verknüpfungen fehlerfrei. Manchmal lassen sich die Quellverbindungen trotz mehrfacher Versuche nicht entfernen. Oder manchmal meldet Excel externe Verknüpfungen, obwohl die Tabellen überhaupt keine Verweise auf andere Dateien enthalten.
Mit dem im Anschluss vorgestellten VBA-Makro beseitigen Sie einfache Verknüpfungen zu anderen Dateien in allen Excel-Versionen wirkungsvoll aus Ihren Arbeitsmappen. Berücksichtigt werden nicht nur direkte Verweise auf externe Zellen oder Zellbereiche. Das Makro kümmert sich auch um die in der Arbeitsmappe definierten Namen, die auf Inhalte aus anderen Dateien verweisen. An die Stelle der externen Bezüge setzt das Makro die resultierenden Werte. Namen, die auf fremde Arbeitsmappen verweisen, werden gelöscht.
Nicht abgedeckt werden komplexe Verknüpfungen, die sich zum Beispiel in den Datenreihen eines Diagramms verbergen oder die als Quelle eines Listen- bzw. Dropdown-Steuerelements eingesetzt werden. Falls Sie nach Ausführung der unten stehenden Löschprozedur immer noch Meldungen über externe Verknüpfungen erhalten, sollten Sie diese grafischen Elemente auf Fremdverweise untersuchen.
Gehen Sie nun wie folgt vor, um das Makro zum Löschen einfacher Verknüpfungen einzugeben und auszuführen:
1. Legen Sie eine Sicherungskopie der Datei mit den externen Verknüpfungen an, damit Sie notfalls auf die Originalwerte und -formeln zurückgreifen können.
2. Öffnen Sie die Datei mit den externen Verknüpfungen, die Sie löschen möchten.
3. Drücken Sie Alt+F11, um den Visual Basic-Editor zu starten.
4. Wählen Sie Einfügen-Modul an, um das VBA-Projekt der Datei um ein Modul zu erweitern und das zugehörige Codefenster zu öffnen.
5. Geben Sie folgenden Programmcode in das Fenster ein:
Sub VerknuepfungenLoeschen()
Dim varLinks
Dim lngLinkCount As Long
Dim i As Long
Dim strLinkedFile As String
Dim lngChrPos As Long
Dim objRefName As Name
Dim strExtRef As String
Dim objWSh As Worksheet
Dim LinkRange As Range
Dim ar As Range
If MsgBox("Wollen Sie alle externen " & _
"Verknüpfungen löschen und durch die " & _
"entsprechenden Werte ersetzen?", vbYesNo) _
= vbYes Then
varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsArray(varLinks) Then
lngLinkCount = UBound(varLinks)
For i = 1 To lngLinkCount
strLinkedFile = varLinks(i)
Do
lngChrPos = InStr(1, strLinkedFile, "\\")
strLinkedFile = _
Right(strLinkedFile, _
Len(strLinkedFile) - lngChrPos)
Loop Until lngChrPos = 0
For Each objWSh In ActiveWorkbook.Worksheets
Set LinkRange = GetLinkRange(objWSh, _
strLinkedFile)
If Not LinkRange Is Nothing Then
For Each ar In LinkRange.Areas
ar.Value = ar.Value2
Next ar
End If
Next objWSh
Next i
End If
For Each objRefName In ActiveWorkbook.Names
If InStr(1, objRefName.RefersTo, ".xl") > 0 Then
strExtRef = objRefName.Name
For Each objWSh In ActiveWorkbook.Worksheets
Set LinkRange = GetLinkRange(objWSh, strExtRef)
If Not LinkRange Is Nothing Then
For Each ar In LinkRange.Areas
ar.Value = ar.Value2
Next ar
End If
Next objWSh
objRefName.Delete
End If
Next objRefName
End If
End Sub
Function GetLinkRange _
(objSheet As Worksheet, _
strSearchFor As String) _
As Range
Dim TempCell As Range
Dim TempRange As Range
Dim strTempAdr As String
With objSheet.UsedRange
Set TempCell = _
.Find _
(What:=strSearchFor, _
LookIn:=xlFormulas, _
LookAt:=xlPart)
If Not TempCell Is Nothing Then
strTempAdr = TempCell.Address
Set TempRange = TempCell
Do
Set TempCell = .FindNext(TempCell)
If Not TempCell Is Nothing Then
Set TempRange = Application.Union(TempRange, _
&nb End If
Loop While _
Not TempCell Is Nothing _
And TempCell.Address <> strTempAdr
End If
End With
Set GetLinkRange = TempRange
End Function
Verlassen Sie den Visual Basic-Editor per Datei-Schließen und zurück zu Microsoft Excel.
Drücken Sie Alt+F8, um das Dialogfeld mit den verfügbaren Makros zu öffnen.
Doppelklicken Sie auf den Makronamen "VerknuepfungenLoeschen".
Beantworten Sie die Frage, ob die externen Verknüpfungen gelöscht werden sollen, mit Ja.
Nach Abschluss der Prozedurausführung sind alle Formeln und Namen mit externen Verknüpfungen beseitigt. Wenn die aktuelle Arbeitsmappe keine anderen Verknüpfungen enthält, sehen Sie den Erfolg daran, dass der Befehl Bearbeiten-Verknüpfungen nicht mehr anwählbar ist.
Der Makrocode lässt sich natürlich noch komfortabler gestalten - beispielsweise durch separate Abfragen bei jeder einzelnen Verknüpfung. Um Ihnen die Anpassung der Prozeduren zu erleichtern, stellen wir Ihnen deshalb die Funktionsweise vor:
Der Code gliedert sich in die Hauptprozedur "VerknuepfungenLoeschen" und die Hilfsfunktion "GetLinkRange". Die Aufgabe der Hilfsfunktion besteht darin, die Zellen zu ermitteln, in denen auf externe Dateien verwiesen wird. Dazu übergeben Sie das Tabellenblatt-Objekt, in dem gesucht werden soll, und das Suchkriterium als Funktionsparameter.
Die eigentliche Arbeit erledigt die Hauptprozedur. Darin ermitteln Sie zunächst die Datenquellen, die mit der aktuellen Mappe verknüpft sind:
varLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
LinkSources liefert ein Array mit den entsprechenden Pfad- und Dateinamen. Dieses Array gehen Sie in einer For-Schleife Element für Element durch. Dabei reduzieren Sie jeweils den vollständigen Pfad auf den Dateinamen, den Sie dann als Suchkriterium an die Hilfsfunktion "GetLinkRange" übergeben. Die Funktion liefert alle Zellen einer Tabelle, in denen auf die externe Datei verwiesen wird. Mit der Schleife
For Each ar In LinkRange.Areas
ar.Value = ar.Value2
Next ar
wandeln Sie die Formeln in die entsprechenden Werte um, wobei die Zusammenfassung in Zellbereiche ("Areas") auch auf Arrayformeln oder verbundene Zellen Rücksicht nimmt.
Danach widmet sich die Prozedur den Namen in der Arbeitsmappe:
For Each objRefName In ActiveWorkbook.Names
Falls sich ein Name auf eine andere Excel-Datei bezieht, also falls im Bezug der Text ".xl" auftritt ...
If InStr(1, objRefName.RefersTo, ".xl") > 0
... wird ähnlich wie zuvor per "GetLinkRange" nach dem Namen gesucht. Und ebenfalls wie zuvor werden die Zellinhalte durch die entsprechenden Werte ersetzt.
Damit bezieht sich der Name aber weiterhin auf eine andere Datei. Um auch diese versteckte Verknüpfung zu beseitigen, müssen Sie schließlich noch den Namen aus der Arbeitsmappe löschen:
objRefName.Delete
Gruß
Heinz