I recently spent a "pleasant" hour trying to find a stray link to another spreadsheet in a large spreadsheet. I couldn't just kill the links as I needed to know where they were to rebuild them later pointing to another file.
In the end I wrote a macro to search for kinks, but couldn't find a direct way to identify a link. This works by looking for ".xls" in a formula, but is there a more elegant way?
For Each Sh In ActiveWorkbook.Worksheets
For Col = 1 To Sh.Cells.SpecialCells(xlCellTypeLastCell).Column
For Row = 1 To Sh.Cells.SpecialCells(xlCellTypeLastCell).Row
If Cells(Row, Col).HasFormula Then
If InStr(1, Cells(Row, Col).Formula, ".xls", vbTextCompare) <> 0 Then
M = M & Sh.Name & " - " & Cells(Row, Col).Address & vbCr
End If
End If
Next Row
Next Col
Next Sh
If M = "" Then M = "No links"
MsgBox M, , ActiveWorkbook.Name
In the end I wrote a macro to search for kinks, but couldn't find a direct way to identify a link. This works by looking for ".xls" in a formula, but is there a more elegant way?
For Each Sh In ActiveWorkbook.Worksheets
For Col = 1 To Sh.Cells.SpecialCells(xlCellTypeLastCell).Column
For Row = 1 To Sh.Cells.SpecialCells(xlCellTypeLastCell).Row
If Cells(Row, Col).HasFormula Then
If InStr(1, Cells(Row, Col).Formula, ".xls", vbTextCompare) <> 0 Then
M = M & Sh.Name & " - " & Cells(Row, Col).Address & vbCr
End If
End If
Next Row
Next Col
Next Sh
If M = "" Then M = "No links"
MsgBox M, , ActiveWorkbook.Name
