Break all external links

[YasserKhalil]

Hello everyone
I have a workbook which I need to break all the links in it .. I have searched a lot and found some solutions but these solutions doesn't help me
I have used some codes to help me doing that task ..

Sub UnhideAllNames()
Dim objName As Name

If Not Application.ActiveWorkbook Is Nothing Then
For Each objName In Application.ActiveWorkbook.Names
objName.Visible = True
Next objName
End If
End Sub


Sub ListExternalLinks()
Dim wb As Workbook
Dim Link As Variant
Dim xIndex As Long

Set wb = Application.ActiveWorkbook


If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
wb.Sheets.Add
xIndex = 1
For Each Link In wb.LinkSources(xlExcelLinks)
Application.ActiveSheet.Cells(xIndex, 1).Value = Link
xIndex = xIndex + 1
Next Link
End If


Columns(1).AutoFit
End Sub


Sub BreakExternalLinks()
Dim externalLinks As Variant
Dim wb As Workbook
Dim x As Long

Set wb = ActiveWorkbook


externalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)


For x = 1 To UBound(externalLinks)
wb.BreakLink Name:=externalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
End Sub




I tried to unhide named ranges then deleted these hidden named ranges manually .. and run the code that beaks all the external links and nothing happened .. The external links are still there
Any help please

[snb]

After running the macro, saving the file, reopening the file, the link will be vanished.


Sub M_snb()
On Error Resume Next
For Each it In ThisWorkbook.LinkSources
For Each sh In Sheets
sh.Cells.Replace it, ""
For Each cl In sh.UsedRange.SpecialCells(-4174)
If InStr(cl.Validation.Formula1, "#REF") Then cl.Validation.Delete
Next
Next
ThisWorkbook.BreakLink it, 1
Next
End Sub

[YasserKhalil]

That's great ans awesome snb. Thanks a lot for great help
As for the last line ThisWorkbook.BreakLink it,1
1 refers to xlLinkTypeExcelLinks ... What is the difference between using 1 and 2? (xlLinkTypeOLELinks )

[snb]

Use F1 or F2 in the VBEditor.

[YasserKhalil]

Yes I already did to know about 1 and find out that it is related to "xlLinkTypeExcelLinks" and found another constant which is 2 but I would like to know bout that. F1 doesn't work for me in VBE so no further information ..

[jolivanes]

Or
Press Ctrl+H to activate the Replace dialog.
- Enter an asterisk between square brackets in the 'Find what' box:

- Leave the 'Replace with' box blank.
- Click 'Replace All'.
From
http://www.eileenslounge.com/viewtopic.php?f=27&t=26592

[snb]

@Joli

external links can be part of
- Excel Formulae,
- named ranges,
- validation rules

The method you describe only removes the external links from Excelformulae. (the 'replace'-line in my suggestion)

[YasserKhalil]

Or
Press Ctrl+H to activate the Replace dialog.
- Enter an asterisk between square brackets in the 'Find what' box:

- Leave the 'Replace with' box blank.
- Click 'Replace All'.
From
http://www.eileenslounge.com/viewtopic.php?f=27&t=26592

Thanks a lot Jolivanes for sharing us. This doesn't work for my issue as it is embedded in validation as I discovered later
Thanks a lot snb for great support