Hi all,
I have used the code posted by @Debraj in this thread: http://forum.chandoo.org/threads/find-the-hidden-links-in-excel-work-book.9040/
Here is the code:
However, because there are no instances of "[" found, there are no link references to be copied to the sheet ListLink (this macro does the same thing as Excel's Find in Worksheets, looking in Formulas for "["), so the code ends up crashing as there is nothing in the array to paste.
In relation to my workbook (workbook 1):
I have tried searching for Objects, but with no results.
There are no charts in the workbook.
There are no hidden sheets which might contain charts or formulae linking to the external workbook.
The only thing I can think of is there was a dump from a worksheet in an external workbook, but I have even deleted the sheet in workbook 1 which contained the external link. I remember reading how Excel keeps a copy of the linked data in the background, so maybe that's still there?
Is there vba code which looks everywhere for the link?
Thank you,
Mitchy
I have used the code posted by @Debraj in this thread: http://forum.chandoo.org/threads/find-the-hidden-links-in-excel-work-book.9040/
Here is the code:
Code:
Sub ListLink()
Dim deb()
Dim i As Long: i = 0
For Each sh In ActiveWorkbook.Worksheets
For Each cell In sh.UsedRange
If Left(cell.Formula, 1) = "=" And InStr(cell.Formula, "[") > 1 Then
ReDim Preserve deb(i)
deb(i) = sh.name & "-" & cell.Address
i = i + 1
End If
Next cell
Next sh
Sheets.Add.name = "ListLink"
Sheets("listLink").Range("A1:A" & UBound(deb) + 1).Value = deb
End SubHowever, because there are no instances of "[" found, there are no link references to be copied to the sheet ListLink (this macro does the same thing as Excel's Find in Worksheets, looking in Formulas for "["), so the code ends up crashing as there is nothing in the array to paste.
In relation to my workbook (workbook 1):
I have tried searching for Objects, but with no results.
There are no charts in the workbook.
There are no hidden sheets which might contain charts or formulae linking to the external workbook.
The only thing I can think of is there was a dump from a worksheet in an external workbook, but I have even deleted the sheet in workbook 1 which contained the external link. I remember reading how Excel keeps a copy of the linked data in the background, so maybe that's still there?
Is there vba code which looks everywhere for the link?
Thank you,
Mitchy