Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
external Excel Add-In reference | Excel Worksheet Functions | |||
External reference not working? | Excel Discussion (Misc queries) | |||
External Reference in Vlookup | Excel Discussion (Misc queries) | |||
External Reference | Excel Discussion (Misc queries) | |||
external reference | Excel Programming |