Gruppe
Link
Bereich
Extern
Thema
Details von Verknüpfungen sollen aufgelistet werden
Problem
Alle Verknüpfungen im Bereich A1:E9 sollen mit Detailangaben aufgelistet werden.
Lösung
Geben Sie den nachfolgenden Code in ein Standardmodul ein und weisen Sie ihn einer Schaltfläche zu.
StandardModule: Modul1
Sub LinkInfo()
Dim arrDetails As Variant
Dim rng As Range, rngSel As Range
Dim iCounter As Integer, iRow As Integer, sText As String
Set rngSel = Range("A1:E9")
Workbooks.Add 1
Range("A1").Value = "LinkAddress:"
Range("B1").Value = "Path:"
Range("C1").Value = "Workbook:"
Range("D1").Value = "Worksheet:"
Range("E1").Value = "Range:"
Range("A1:E1").Font.Bold = True
iRow = 1
For Each rng In rngSel
If rng.HasFormula Then
If InStr(rng.Formula, "\[") Then
arrDetails = GetDetails(rng.Formula)
iRow = iRow + 1
Cells(iRow, 1).Value = rng.Address
For iCounter = 1 To 4
Cells(iRow, iCounter + 1).Value = arrDetails(iCounter)
Next iCounter
End If
End If
Next rng
Columns.AutoFit
End Sub
Private Function GetDetails(sTxt As String) As Variant
Dim sWkb As String, sWks As String, sRng As String
Dim sPath As String
Dim arr(1 To 4) As String
arr(1) = Mid(sTxt, InStr(sTxt, "'") + 1, _
InStr(sTxt, "[") - InStr(sTxt, "'") - 2)
arr(2) = Mid(sTxt, InStr(sTxt, "[") + 1, _
InStr(sTxt, "]") - InStr(sTxt, "[") - 1)
arr(3) = Mid(sTxt, InStr(sTxt, "]") + 1, _
InStr(sTxt, "'!") - InStr(sTxt, "]") - 1)
arr(4) = Right(sTxt, Len(sTxt) - InStr(sTxt, "!"))
GetDetails = arr
End Function