Removing External Links

[dbmccallum]

Hi All,

I have several "links" that I just can not find (3 links). I have cleaned up the Defined Names. Used fine *.xl* across the whole workbook. But whenever I open my spreadsheet it looking for an extremely old version of its companion spreadsheet. It is just a nuisance error, but I would love to kill it.

On the data tab, edit links is grayed out.

running Excel 2016

 

[shift-del]

Dbmccallum,

check these articles:


http://blog.contextures.com/archives/2013/11/12/find-external-links-in-an-excel-file/

http://www.exceldashboardtemplates.com/how-to-fix-security-links-warnings-in-an-excel-spreadsheet/

https://trumpexcel.com/find-external-links-excel/

http://nhsexcel.com/how-to-break-resistant-external-links-in-excel/

http://superuser.com/questions/9152...k-in-excel-to-break-if-the-file-cant-be-found

http://www.manville.org.uk/software/findlink.htm

 

[dbmccallum]

Thanks for the links, I had tried most of those methods, however in my investigations, I found out the "find" does not search hyperlinks.

I used the following VBA to list all of my links and removed the unwanted ones:
Public Sub findhyperlinks()
Dim h As Hyperlink
Dim Sh As Worksheet
Dim Csh As Worksheet
Call SetPassword(spw)
ActiveWorkbook.Unprotect Password:=spw

On Error GoTo errEnd
Sheets("listoflinks").Delete
errEnd:

Set Csh = ActiveWorkbook.Worksheets.Add
Csh.Name = "listoflinks"
With Csh
.Range("A1").Value = "Sheet Address"
.Range("B1").Value = "Link Address"
.Range("C1").Value = "Address"
.Range("D1").Value = "ScreenTip"
End With
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name <> Csh.Name Then
For Each h In Sh.Hyperlinks
With Csh.Cells(Csh.Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = Sh.Name & " - " & h.Parent.Address
.Offset(0, 1).Value = h.SubAddress
.Offset(0, 2).Value = h.Address
.Offset(0, 3).Value = h.ScreenTip
End With
Next
End If
Next
Columns("A:D").AutoFit
End Sub