Excel und VBA: Formeln, Programmierung, Lösungen

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

    

Beiträge aus dem Excel-Forum zu den Themen Link und Extern