External Reference removal
 

Excel 97.
I have a link to an external Excel file that shouldn't be there. I know
it is there because it shows up in the Links dialogue window. Since
there is no way to simply delete the link I assume there has to be
reference in one of the equations to this file.

I created a procedure to check each cell (assuming the reference resides
somewhere in or near to the used range of cells) for part of the text of
the external filename in the formula and debug.print the range address
and worksheet name.

The procedure returns no cells, so where else could this external
reference be? And how can I elliminate it?

Here is the procedure. I tested the procedure using other text that I
knew were in formulas and the procedure correctly found those cells. The
external file reference is for "Liqs_weekly 2005". Searching for "Liqs"
is sufficient for finding the reference.


Sub find_external_ref()
Dim ws As Worksheet
Dim wbk As Workbook
Dim r As Integer
Dim c As Integer

On Error GoTo FER_errorhandler
Set wbk = ActiveWorkbook
For Each ws In Worksheets
For c = 0 To 255
For r = 0 To 600
If InStr(1, Range("A1").Offset(r, c).Formula, "Liqs")
0 Then
Debug.Print ws.Name
Debug.Print Range("A1").Offset(r, c).Address
End If
Next r
Next c
Next ws
Set wbk = Nothing
Set ws = Nothing
Exit Sub


FER_errorhandler:
Debug.Print Err.Number & ", " & Err.Description
Exit Sub





End Sub

External Reference removal
 

Hi
could be a defined name. You max check:
http://oaltd.co.uk/MVP/Default.htm

and search for Findlink.zip
(http://oaltd.co.uk/DLCount/DLCount.a...e=FindLink.zip)

--
Regards
Frank Kabel
Frankfurt, Germany
"Dave the Wave" schrieb im Newsbeitrag
...
Excel 97.
I have a link to an external Excel file that shouldn't be there. I know
it is there because it shows up in the Links dialogue window. Since
there is no way to simply delete the link I assume there has to be
reference in one of the equations to this file.

I created a procedure to check each cell (assuming the reference resides
somewhere in or near to the used range of cells) for part of the text of
the external filename in the formula and debug.print the range address
and worksheet name.

The procedure returns no cells, so where else could this external
reference be? And how can I elliminate it?

Here is the procedure. I tested the procedure using other text that I
knew were in formulas and the procedure correctly found those cells. The
external file reference is for "Liqs_weekly 2005". Searching for "Liqs"
is sufficient for finding the reference.


Sub find_external_ref()
Dim ws As Worksheet
Dim wbk As Workbook
Dim r As Integer
Dim c As Integer

On Error GoTo FER_errorhandler
Set wbk = ActiveWorkbook
For Each ws In Worksheets
For c = 0 To 255
For r = 0 To 600
If InStr(1, Range("A1").Offset(r, c).Formula, "Liqs")
0 Then
Debug.Print ws.Name
Debug.Print Range("A1").Offset(r, c).Address
End If
Next r
Next c
Next ws
Set wbk = Nothing
Set ws = Nothing
Exit Sub


FER_errorhandler:
Debug.Print Err.Number & ", " & Err.Description
Exit Sub





End Sub

External Reference removal
 

Check your named ranges on each sheet. (You have to check each sheet
individually) Your problem is probably in there...

"Dave the Wave" wrote:

Excel 97.
I have a link to an external Excel file that shouldn't be there. I know
it is there because it shows up in the Links dialogue window. Since
there is no way to simply delete the link I assume there has to be
reference in one of the equations to this file.

I created a procedure to check each cell (assuming the reference resides
somewhere in or near to the used range of cells) for part of the text of
the external filename in the formula and debug.print the range address
and worksheet name.

The procedure returns no cells, so where else could this external
reference be? And how can I elliminate it?

Here is the procedure. I tested the procedure using other text that I
knew were in formulas and the procedure correctly found those cells. The
external file reference is for "Liqs_weekly 2005". Searching for "Liqs"
is sufficient for finding the reference.


Sub find_external_ref()
Dim ws As Worksheet
Dim wbk As Workbook
Dim r As Integer
Dim c As Integer

On Error GoTo FER_errorhandler
Set wbk = ActiveWorkbook
For Each ws In Worksheets
For c = 0 To 255
For r = 0 To 600
If InStr(1, Range("A1").Offset(r, c).Formula, "Liqs")
0 Then
Debug.Print ws.Name
Debug.Print Range("A1").Offset(r, c).Address
End If
Next r
Next c
Next ws
Set wbk = Nothing
Set ws = Nothing
Exit Sub


FER_errorhandler:
Debug.Print Err.Number & ", " & Err.Description
Exit Sub





End Sub

External Reference removal
 

"Frank Kabel" wrote in news:OOOgA9r5EHA.3644
@tk2msftngp13.phx.gbl:

http://oaltd.co.uk/MVP/Default.htm

Thanks. That addin found the links.

External Reference removal
 

Dave the Wave wrote in news:Mb2dnUMVB_hGhlrcRVn-
:

"Frank Kabel" wrote in news:OOOgA9r5EHA.3644
@tk2msftngp13.phx.gbl:

http://oaltd.co.uk/MVP/Default.htm

Thanks. That addin found the links.

FYI:
There were links to an external file where I had copied some cells from one
file and then did a pastespecial--values into another file. (Thought I was
saving some typing time.) The formula contained no external reference, but
the reference was still there somewhere. The addin located the links
somehow and allowed me to delete the references.