Excel VBA Procedures to Break All External References in Workbook

The following Excel VBA procedures may be used to break all external references and links in the active workbook. The BreakExternalReferences() procedure will change all formulas containing references to external workbooks into the resulting value of that formula. The DeleteExternalNames() procedure will delete all defined names that contain external workbook references. Again, any formulas containing references to the defined name will be converted to the resulting value of that formula.

Option Explicit

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub

Function to Check If Workbook is Already Open Using Excel VBA

The following Excel VBA function may be used to test if an Excel workbook is currently open. The wbName parameter refers to the full path of a specific file. The function will return true if the workbook is already open.

Option Explicit

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
    On Error GoTo 0
End Function

Function to Check If File Exists Using Excel VBA

The following Excel VBA function may be used to test if a file exists. The wbName parameter refers to the full path of a specific file. The function will return true if the file is found.

Option Explicit

Function IsFileExist(wbName As String) As Boolean
    IsFileExist = Len(Dir(wbName))
End Function