VBA to list all external links together with the cells containing the links

[lynnsong986]

Hello,

I need to use VBA to list all external links in a workbook and have the location of the corresponding cells that contain these links listed beside them for reference. I do have codes that generate a list of the external links, but I need it to go one more step further to also give me the location of the cells that hold them as part of the formula in those cells.

Your help is greatly appreicated.
Lynn

 

[Domenic]

The following macro will add a new worksheet to the active workbook, and then will list all links within the active workbook in the new worksheet...

Option Explicit

Sub ListLinks()

    Dim Wks             As Worksheet
    Dim rFormulas       As Range
    Dim rCell           As Range
    Dim aLinks()        As String
    Dim Cnt             As Long

    If ActiveWorkbook Is Nothing Then Exit Sub
    
    Cnt = 0
    For Each Wks In Worksheets
        On Error Resume Next
        Set rFormulas = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rFormulas Is Nothing Then
            For Each rCell In rFormulas
                If InStr(1, rCell.Formula, "[") > 0 Then
                    Cnt = Cnt + 1
                    ReDim Preserve aLinks(1 To 2, 1 To Cnt)
                    aLinks(1, Cnt) = rCell.Address(, , , True)
                    aLinks(2, Cnt) = "'" & rCell.Formula
                End If
            Next rCell
        End If
    Next Wks
    
    If Cnt > 0 Then
        Worksheets.Add before:=Worksheets(1)
        Range("A1").Resize(, 2).Value = Array("Location", "Reference")
        Range("A2").Resize(UBound(aLinks, 2), UBound(aLinks, 1)).Value = Application.Transpose(aLinks)
        Columns("A:B").AutoFit
    Else
        MsgBox "No links were found within the active workbook.", vbInformation
    End If
    
End Sub

Hope this helps!

 

[lynnsong986]

Hello Domenic, this works like a charm...thank you so much for the quick response! Lynn

 

[Domenic]

You're very welcome, and thanks for the feedback!

Cheers!

 

[lynnsong986]

Hello Domenic,

Just let you know that I found another code you provided in this forum that changes cell value in multiple workbooks at one go...just amazing. I'm so glad that I joined the forum, thanks so much for your help!
Lynn

 

[Domenic]

Hi Lynn,

That's great to hear. There are a lot of talented people here who are willing to provide help, advice, opinion, etc., along with other resources on this website. So hopefully you'll find it helpful.

Cheers!

 

[austinda]

This is a great Macro, I added it to my toolbox and will be using it a lot.

 

[lcraigaz]

This is a great Macro, I added it to my toolbox and will be using it a lot.

So I'm kina new a is. When you find macros like this that you like and you say add to your tool box. How/where do you put and keep these to use. A long time ago someone set it up for me and I remember I had them in the ribbon in excel that I could click and run. But now I work somewhere else and don't know how to set this up again. Any help is appreciated.

 

[navkat]

Thank you for the code it works like a champ!.

I just started learning Macros and VBA for excel so i am not familiar with much of the code. I need more time to understand it. But can we go step further or can u give me your opinion about my question.

We have a master excel file that gets data from bunch of other excel files/workbooks. So your script kind of gave me 616 cells that have external references. So when my users update the one of the source excel file that data will feed into master excel and there bunch of calculations happens in the master excel. I am trying to make sure the source data was properly feed into master excel, so far we did manual check. Is there a way to make sure source and destination data is same by Macros or any other way or is it a possibility we can use above code and add couple more columns. Please let me know any help is much appreciated.

 

[navkat]

HI I am able to add a new column that pulls values from the master excel work book that have external links. Small changes were made to your code. So can you help me adding one more column that can pull value from source files as well?

Option Explicit

Sub ListLinks()

Dim Wks As Worksheet
Dim rFormulas As Range
Dim rCell As Range
Dim aLinks() As String
Dim Cnt As Long

If ActiveWorkbook Is Nothing Then Exit Sub

Cnt = 0
For Each Wks In Worksheets
On Error Resume Next
Set rFormulas = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rFormulas Is Nothing Then
For Each rCell In rFormulas
If InStr(1, rCell.Formula, "[") > 0 Then
Cnt = Cnt + 1
ReDim Preserve aLinks(1 To 3, 1 To Cnt)
aLinks(1, Cnt) = rCell.Address(, , , True)
aLinks(2, Cnt) = "'" & rCell.Formula
aLinks(3, Cnt) = "'" & rCell.Value
End If
Next rCell
End If
Next Wks

If Cnt > 0 Then
Worksheets.Add before:=Worksheets(1)
Range("A1").Resize(, 3).Value = Array("Location", "Reference", "Value")
Range("A2").Resize(UBound(aLinks, 2), UBound(aLinks, 1)).Value = Application.Transpose(aLinks)
Columns("A:C").AutoFit
Else
MsgBox "No links were found within the active workbook.", vbInformation
End If

End Sub