VBA to Open excel and Break links

[12Rev79]

Hi Experts,

I have a number of of Excel workbook in my folder C:\Desktop\Fabrication
Is there a way of creating a command button from my Extract1.xlsb to automate Open each workbook in the folder and break links, save in the same folder and close up to the last workbook?
My workbooks has link to another Excel workbook that I want to be remove.

Please if you may share the code.

Thanks in advance and I appreciate your help.
12Rev79

 

[Domenic]

The following code will prompt the user to select a folder, then it loops through each .xlsx workbook, then it opens each workbook and breaks all links within that workbook.

Option Explicit

Sub Break_Links_In_Workbooks_Within_Selected_Folder()

    Dim sourceFolder As String
    Dim currentFile As String
    Dim currentWorkbook As Workbook
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .ButtonName = "Select"
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Select Folder"
        If .Show = 0 Then Exit Sub
        sourceFolder = .SelectedItems(1) & "\"
    End With
 
    currentFile = Dir(sourceFolder & "*.xlsx") 'change the extension as desired
 
    If Len(currentFile) = 0 Then
        MsgBox "No workbooks found.", vbExclamation
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
 
    Do
        Set currentWorkbook = Application.Workbooks.Open(sourceFolder & currentFile)
        BreakLinks currentWorkbook
        currentWorkbook.Close SaveChanges:=True
        currentFile = Dir
    Loop While Len(currentFile) > 0
 
    Application.ScreenUpdating = True

    MsgBox "Completed . . .", vbInformation
 
End Sub


Sub BreakLinks(ByVal wb As Workbook)
 
    Dim links As Variant
    Dim link As Variant
 
    links = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
 
    If Not IsEmpty(links) Then
        For Each link In links
            wb.BreakLink _
                Name:=link, _
                Type:=xlLinkTypeExcelLinks
        Next link
    End If
 
End Sub

Hope this helps!