none
Vba to list all external links RRS feed

  • Question

  • Hi,

    do you have any VBA CODE  to list all the external link in the workbook

    something like this with the source name and where are the formulas/objects/named ranges that uses the external links.

    I need the source name next to every formula address because I have thousands of formulas with external links and I need to filter them by the source


    FORMULA ADRESS WORKSHEET SOURCE NAME
    A1 SHEET1 ABC.XLSX
    B1 SHEET2 EFG.XLSX

    Monday, August 27, 2018 8:43 AM

All replies

  • Hi

    You can try the following worked code.

    Sub ListLinks()
        Dim xSheet As Worksheet
        Dim xRg As Range
        Dim xCell As Range
        Dim xCount As Long
        Dim xLinkArr() As String
        On Error Resume Next
        For Each xSheet In Worksheets
            Set xRg = xSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
            If xRg Is Nothing Then GoTo LblNext
            For Each xCell In xRg
                If InStr(1, xCell.Formula, "[") > 0 Then
                    xCount = xCount + 1
                    ReDim Preserve xLinkArr(1 To 2, 1 To xCount)
                    xLinkArr(1, xCount) = xCell.Address(, , , True)
                    xLinkArr(2, xCount) = "'" & xCell.Formula
               End If
            Next
    LblNext:
        Next
        If xCount > 0 Then
            Sheets.Add(Sheets(1)).Name = "Link Sheet"
            Range("A1").Resize(, 2).Value = Array("Location", "Reference")
            Range("A2").Resize(UBound(xLinkArr, 2), UBound(xLinkArr, 1)).Value = Application.Transpose(xLinkArr)
            Columns("A:B").AutoFit
        Else
            MsgBox "No links were found within the active workbook.", vbInformation, "KuTools for Excel"
        End If
    End Sub

    Feel free let me know if you have any other update.

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, August 27, 2018 9:15 AM
    Moderator
  • Thank for that! but could you please add a column to the code for the source named?

    you have in the code the formula with external link and the location of the fromula in the workbook but I also need the a column of the the souce ( the name of the workbook that is linked to every row of data)

    Thanks again

    Monday, August 27, 2018 10:13 AM
  • xSheet.UsedRange.SpecialCells(xlCellTypeFormulas) have contains the column you needed,such as "source name"/" Formuals Address"

    Maybe you can modify this line to suit your "source name" column:

     If InStr(1, xCell.Formula, "[formula value of source name]") > 0 Then

    Seiya


     Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.



    Tuesday, August 28, 2018 8:36 AM
    Moderator