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]

Hi,

Could you help me for multi-workbooks?

how can i break all links for multiple workbooks?

thanks

Onur

The code would break links in activeworkbook.
It should work.

 

[Biz]

Try code below. Hope it helps.

Sub DeleteExternalNames()
    Dim objDefinedName As Object
    Dim iCount As Long
    
    '~~> Speeding Up VBA Code
    Call SpeedUp(False)
    
    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
            iCount = iCount + 1
        End If
    Next objDefinedName
    
    '~~> Speeding Up VBA Code
    Call SpeedUp(True)
    
    '~~> Report Result
    If iCount = 1 Then
        MsgBox "[1] external named range was removed from this workbook."
    Else
        MsgBox "[" & iCount & "] external named ranges were removed from this workbook."
    End If
End Sub


'#### SpeedUp (False) - Speeds the VBA Code #####
'#### SpeedUp (True) - Slows down the VBA Code ####
Public Function SpeedUp(Optional bSpeed As Boolean = True)
With Application
    .ScreenUpdating = bSpeed 'Prevent screen flickering
    .Calculation = IIf(bSpeed, xlAutomatic, xlCalculationManual) 'Preventing calculation
    .DisplayAlerts = bSpeed 'Turn OFF alerts
    .EnableEvents = bSpeed 'Prevent All Events
End With
End Function

 

[yaseensallam]

iT IS GREAT
THANKS

 

[yaseensallam]

Thank you ,,
it is working well

 

[dof]

Just had this issue this morning. It turned out that I had some cells subject to Data Validation i.e. I had restricted the value of certain cells to the values in a range. And this range was in another workbook.

Regarding how I tracked down the problem....I took advantage of the fact that an xlsx / xslm file is basically a zip file. So I made a copy of the file, and changed the extension to .zip. I then unzipped the contents and used a text editor to locate the external workbook. Using an xml editor (although a text editor should suffice) I was able to find the worksheet and the range of cells that were problematic.

I then went back to my Excel document and hey presto, I found the data validation was in error.

Hope this helps.