Gruppe
Link
Problem
Alle Verknüpfungen im Bereich A1:E9 sollen mit Detailangaben aufgelistet werden.
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