Break a Link

Are Square · #1 07-07-2014, 01:08 PM

I have a workbook that says (in the Edit Links dialog box) that it has a link to another workbook even though I can't find where that link is. As well, the workbook that is linked no longer exists. When I click "Break Link", nothing happens and the bogus link remains. How can I delete a link to a file that does not exist?

I am using Excel 2013 in Windows 8

Thanks,
RR

macropod · #2 07-10-2014, 12:50 AM

You could run a macro like the following from the Excel VBA help file:

Code:

Sub UseBreakLink()
    Dim astrLinks As Variant
    ' Define variable as an Excel link type.
    astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    ' Break the first link in the active workbook.
    ActiveWorkbook.BreakLink _
        Name:=astrLinks(1), _
        Type:=xlLinkTypeExcelLinks
End Sub

Catalin.B · #3 07-11-2014, 10:57 PM

Check the defined names, you may have external references in names.
Catalin
L.E.:
Also, if you have shapes (buttons, etc) in your sheets, with macro's assigned to them, these links will remain, even if you break the links.
Use codes like these:

Code:

For Each Shp In ActiveSheet.Shapes 'remove assigned macro's
           Shp.RefersTo = ""
       Next
  
     
    Dim Nm As Name 'there are names linked to source workbook, delete them...
    For Each Nm In ActiveWorkbook.Names
        Nm.Delete
    Next

You can change the code to see if that shape or name has external links, and only then take action.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trying to link page numbers in the footer, but refuses to link after new section	fl0shizzle	Word	7	05-06-2014 12:15 PM
Section Break (Next Page) replaces Section Break (Continuous) when deleted	Carlabasson	Word	2	03-25-2013 10:13 PM
How to link an object to a URL link to update automatically	expert4knowledge	Excel	1	06-11-2012 09:30 AM
why the "section break-next page" is switched to "section break-continuous"?	Jamal NUMAN	Word	6	12-14-2011 03:35 PM
paragaph hard break, soft break and ...strange break	czomberzdaniela	Word	2	12-03-2010 06:58 PM