Getting rid of a link
Getting rid of a link
(OP)
I inheirited an excell file that has a link to a non existant file. I have talked to the author and this link is no longer needed. But I cannot find the cell with the referenced file.
I have ran edit-find on every woork shhet and that reference is simply not found.
How do I delete this link that is not in any cell?
I have ran edit-find on every woork shhet and that reference is simply not found.
How do I delete this link that is not in any cell?





RE: Getting rid of a link
http
RE: Getting rid of a link
Is the link hidden in a macro?
RE: Getting rid of a link
I believe that it was left in the numerous charts because I got errors from the link cowski gavce me on every chart there. These charts have data in have a series 1 and a series 3 but no series 2. I think it is some left over crap from the numerous iteration these files have gone through.
It would be very nice if I could simply, from the tool bar, click on the link and then delete it. any thoughts?
RE: Getting rid of a link
Because my link was to other Excel Spreadsheet.
But I wanted to say I have one spreadsheet which I "sterlized" it with that method and again shows a message that there is an external link. I search for that and finally found that just a bug in such "externally linked Excel files" can be exist.
RE: Getting rid of a link
CODE
Dim s As Worksheet, c As Range, D As Name
Dim Links As Variant, l As Variant
Dim i As Integer, n As Integer, Lref As String
Dim ncell As Integer, nname As Integer
Const SepChar As String * 1 = "\"
ncell = 0
nname = 0
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(Links) Then Exit Sub
Debug.Print
For Each l In Links
Debug.Print "Checking: ", l
'First remove path info from link
i = 1
Do While i <> 0
n = i
i = InStr(i + 1, l, SepChar)
Loop
Lref = Mid(l, n + 1)
'find link in cell formula and format conditions
For Each s In ActiveWorkbook.Worksheets
Set c = s.UsedRange.Find(Lref)
If Not (c Is Nothing) Then 'found link ?
Debug.Print s.Name, c.Address 'then print location
c.Formula = c.Value 'and replace ref with cell value
ncell = ncell + 1
End If
Next s
'find link in names collection
For Each D In ActiveWorkbook.Names
If InStr(1, D.RefersTo, Lref) Then 'found link ?
Debug.Print D.Name, D.RefersTo 'then print location
D.Delete 'and delete the name
nname = nname + 1
End If
Next D
Next l
Debug.Print "--- Ready ---"
MsgBox ncell & " cell formulas replaced and " & nname & " names deleted."
End Sub
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Getting rid of a link
I do not think it is doing anything, buecause nothing is wrong when I hit cancle.
I guess I am worring about nothing, but I would like it gone.
RE: Getting rid of a link
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Getting rid of a link
Delete Links Wizard
http://support.microsoft.com/kb/q188449/
Regards
Ste
RE: Getting rid of a link
Cyril Guichard
Mechanical Engineer Consultant
France
RE: Getting rid of a link
It will find all links. Asks if you want to get rid of them.
http://www.cpearson.com/excel/xltools.htm
Go here and look for FindLink by Bill Manville
GT
RE: Getting rid of a link
The link is from the original creator and after modifying the file, he quit using that link. But now I own this and I do not have the file.
So now I am stuck with this undeletable link in these charts. As a work around, I just created an empty excell file with that name.
RE: Getting rid of a link
I have had similar problems, and if I am understanding your situation properly, trying the following could help.
One way to search for all external links is to go to Edit, Find, click on the options button, select within workbook, by rows, and look in formulas. In the find what space, type in the left bracket located above the enter key. Click find all. Then if there is anything to display, it will display the cells in that workbook that have external links. Looking in the Formula column, scroll down until you see an entry. Check what sheet this entry is on and delete the cell or change the calculation being performed in that cell.
Regards,
Fred
RE: Getting rid of a link
have you tried changing the link source to the name of opened file (i.e. Edit => Links =>Change Source)? for example, the filename with 32 charts is "32charts.xls". go through the process of changing the source link to "32charts.xls".
the charts are "looking" for the data on another file. simply change the source link file to the same filename. this may impact the display of data or other information placed on the chart. hence, the question as to what is the specific link for?
good luck!
-pmover