Remove spreadsheet links

[gsfmike]

I am attempting to remove links in a spreadsheet. The help files only speak about hyperlinks. Can anyone give me some insight into how to accomplish this? Many thanks

 

[Cresby]

if the cursor changes to a hand when hovering, right click & use the edit hyperlinks and uncheck any boxes or delete text.

otherwise what kind of link is it?

 

[gsfmike]

I appreciate the quick repsonse Crespy. The links I am speaking about are linked spreadsheets. I can use - Edit - Links to view and or update the linked sheets, but nowhere is there a delete action that I can find. A review of the help files only mentions what you offered. Any other thoughts?

Thanks

 

[Cresby]

if this is a linked document you have referrenced another workbook and need to find the offending cell(s). Not easy but try searching for the square bracket [ or ] in "formulas" (sic) and remove brackets and filepath

You may have a lot of them.

 

[GlennUK]

Well, links to other sheets usually occur in cell formulae, so you could search for the filenames of the links, and then erase the formulae that do linking.

Watch out for Defined Name formulae, which can also have external links. Do menu command Insert/Name/Define and look to see if there are any names defined, and if any of those names point to an external file.

Glenn.

 

[Cresby]

edit-out the offending text in the formula bar. or if it is repeated you can substitute (say) [D:\WIFFLE\doodab\thatfile.xls] for nothing leaving the local cell reference only but if it is referencing another workbook it begs the question of what it is using the data for. Think before you destroy the evidence.

 

[gsfmike]

I appreciate your repsonses. I was hoping this was not going to be the case, but I guess that it does make sense. The integrity of the sheet would be completely lost if there was a quick method to simply remove the links.

Thanks again

 

[mudstuffin]

Hello. I've just knocked this up and it seems to work, and hopefully is what you're looking for.

Try it and see. They will loop through each sheet and remove any external links but keep the data that originated from the link. Place them both in a new module and run the StartIt sub from a commandbutton or however you want to trigger it.....

Sub StartIt()

Dim Sht As Worksheet
For Each Sht In Worksheets
Sht.Activate
MsgBox "Deleting links from " & Sht.Name
deleteLinks
Next Sht
deleteLinks

End Sub


Sub deleteLinks()

Dim C As Range
For Each C In ActiveSheet.UsedRange

If Left(C.Formula, 1) = "=" And _
InStr(C.Formula, "[&quot > 1 Then
C.Value = C.Value
End If
Next

End Sub

Regards,


mudstuffin

 

[gsfmike]

mudstuffin, thanks for 'knocking' up the piece of code. It worked. I really appreciate the insight into the use of macros/code to help with other tasks.

 

[tazman99]

Gsfmike,

By the way, an even easier way to break links is with Excel XP. With Excel XP Go to Edit/ Links and click on the 'Break Link' button. Editing links has been improved with Excel XP.

Enjoy,
tazman

 

[KenWright]

Bill Manville has created an excellent addin for dealing with links called FINDLINK.XLA. It will find even the sneakiest of the little suckers, wherever it might be hiding, and it can be freely downloaded from the following link (Wouldn't be without it):-

http://www.bmsltd.co.uk/MVP/Default.htm

Regards
Ken............