Break all links VBA

[davec8723]

Hi,

Does anyone know how to break all links using VBA quickly? I am using Excel 2003.

For some reason this code does not work for me, which I have been finding all over the internet.

Sub BreakLinks()
    Dim vLinks As Variant
    Dim lLink As Long
 
    ' Define variable as an Excel link type.
    vLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    If vLinks = vbNullString Then Exit Sub
 
    ' Break all links in the active workbook.
    For lLink = LBound(vLinks) To UBound(vLinks)
        ActiveWorkbook.BreakLink _
                Name:=vLinks(lLink), _
                Type:=xlLinkTypeExcelLinks
    Next lLink
End Sub

Thank You!







</PRE>

 

[Biz]

Try code below. Does it help?

Sub BreakLinks()
    Dim aLinksArray As Variant
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    
    aLinksArray = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    
    Do Until IsEmpty(aLinksArray)
        ActiveWorkbook.BreakLink Name:=aLinksArray(1), Type:=xlLinkTypeExcelLinks
        aLinksArray = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
    Loop
    
     Application.ScreenUpdating = True
     Application.DisplayAlerts = True
     Application.Calculation = xlCalculationAutomatic
End Sub

Biz

 

[BiocideJ]

This is the function I have always used. Similar to what you have, but let me know if it works for you.

Sub BreakLinks()
Dim Links As Variant
Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(Links)
ActiveWorkbook.BreakLink _
    Name:=Links(i), _
    Type:=xlLinkTypeExcelLinks
Next i
End Sub

If it is still not working perhaps you can give a little more detail as to what exactly is happening?

 

[RoryA]

What kind of links are you breaking? Does the break link function work manually?

 

[davec8723]

Hi,

Now that you mention it, the links will not break manually either.. Sorry I should have checked that first.. Any idea why they will not break? It does not return an error or anything, the link simply does not disappear from the list.

Thanks,
Dave

 

[RoryA]

I suspect the links are in defined names or charts. Have you tried Bill Manville's FindLink addin?

 

[davec8723]

I'm sorry I am not familiar with that addin.. what is it?

 

[RoryA]

You can get it here.

 

[onurc]

This is the function I have always used. Similar to what you have, but let me know if it works for you.

Sub BreakLinks()
Dim Links As Variant
Links = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For i = 1 To UBound(Links)
ActiveWorkbook.BreakLink _
    Name:=Links(i), _
    Type:=xlLinkTypeExcelLinks
Next i
End Sub

If it is still not working perhaps you can give a little more detail as to what exactly is happening?

Hi,

Could you help me for multi-workbooks?

how can i break all links for multiple workbooks?

thanks

Onur

 

[Twojay54]

I tried the code above but it would not remove a stubborn data link. However, I discovered that if I went to Formulas/Name Manager and removed all names where the reference was no longer valid then I could break the link manually.