Sub Remove_External_Links()
' Макрос снимающий все связи с другими книгами
For Each Worksheet In Worksheets
If Worksheet.ProtectContents = True Then
Worksheet.Unprotect (5)
End If
Next Worksheet
alinks = ActiveWorkbook.LinkSources
'проверяем есть ли ссылка на другие файлы, если нет, то выходим из поцедуры
If IsArray(alinks) = False Then GoTo SET_PW
'находим количество элементов в массиве
Links = UBound(alinks)
For i = 1 To Links
Name1 = ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink alinks(i), Type:=xlExcelLinks
Next i
SET_PW:
For Each Worksheet In Worksheets
Worksheet.Protect (5)
Next Worksheet
End Sub
Programatically removing all the Links to external workbooks from an Excel Workbook
ОтветитьУдалить'Code by Mahipal Padigela
'Open the Microsoft Excel file with the Links to external workbooks,then
'goto Visual Basic Editor(Alt+F11) and Insert a Module
'Paste the following code into code window
'Close VB Editor and run this Macro from Excel window(Alt+F8) (Make sure you take of the data
'referenced from all external workbooks before running the Macro as it removes all links
Sub RemoveLinks()
Dim Link As Variant
If ActiveWorkbook.LinkSources Then
If MsgBox("Sure you want to delete all external links?", vbYesNo + vbQuestion, "RemoveLinks") _
= vbYes Then
For Each Link In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
Next
MsgBox "All Links to external workbooks have been removed"
Else
Exit Sub
End If
Else
MsgBox "No Links to external workbooks have been found"
End If
End Sub
source: http://www.mahipalreddy.com/vba.htm#extlinks