Links in the file
Posted by satish patil on April 23, 2001 3:02 PM
I inherited a huge excel file with data and graphs and it is linked to 4-5 other files. I can see the links in the links box but not sure which cell is linked to them.
'find' does not work. 'find' in formula option also does not work. we suspected that some of the graphs may be linked outside, but there are so many of them that it is difficult to find out.
any clues?
Posted by Mark W. on April 23, 2001 3:34 PM
See...
http://support.microsoft.com/support/kb/articles/Q126/0/93.asp?LN=EN-US&SD=gn&FR=0&qry=find%20link&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97
Posted by Dave Hawley on April 23, 2001 10:40 PM
Hi satish
Here is a Macro I have written that will generate a Sheet with a list of ALL external links.
Sub ListExternalLinks()
'Written by OzGrid Business Applications
'www.ozgrid.com
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'Creates a Worsheet called "Link List" and lists ALL _
external links in the Workbook.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim sht As Worksheet
Dim LinkCells As Range, Cell As Range
'Add a new sheet to list all external links.
On Error Resume Next
Sheets.Add().Name = "Link List"
Application.DisplayAlerts = False
'If name does NOT = "Link List" then it already exists
If ActiveSheet.Name <> "Link List" Then ActiveSheet.Delete
Application.DisplayAlerts = True
'Clear column A and format as text.
Sheets("Link List").Columns(1).Clear
Sheets("Link List").Columns(1).NumberFormat = "@"
'Loop through each worksheet
For Each sht In ThisWorkbook.Worksheets
'Set "LinkCells" to range that has formulas
Set LinkCells = Nothing
Set LinkCells = sht.Cells.SpecialCells(xlCellTypeFormulas)
If Not LinkCells Is Nothing Then
'Loop through each cell in "LinkCells"
For Each Cell In LinkCells
'See if if an external link or not.
If Cell.Formula Like "[*" Then
'It is, so copy the formula to column A of "Link List"
Sheets("Link List").Cells _
(65536, 1).End(xlUp).Offset(1, 0) = Cell.Formula
End If
Next Cell
End If 'Not LinkCells Is Nothing
Next sht
End Sub
Dave
OzGrid Business Applications
