Excel: How to fix broken links

In Excel, links to other workbooks are often used to reference relevant information. However, if a source workbook is renamed or moved to a different location, these links may break, leading to errors. This tutorial provides two effective methods - using the Edit Links feature or VBA code - to quickly identify and fix broken links in your workbook.

Fix broken links by applying Edit Links

Fix broken links by using VBA code

In Excel, you can go to the Edit Links dialog to fix them one by one.

1. Enable the workbook which contains the broken links, and click Data > Edit Links (in Queries & Connections group).

2. In the Edit Links dialog, click Check Status to find which links are broken.
 

3. Then click on the broken links, then click Change Source button, in the Change Source window, choose the correct source workbook, and click OK.

Now you can see the status has changed from Error to OK. Repeat step 3 to fix other broken links.

This section introduces a VBA code to help fix broken links.

1. Enable the workbook that you want to fix the broken links, and press Alt + F11 keys.

2. Then in the popping Microsoft Visual Basic for Applications window, click Insert > Module to create a new blank module.

3. Copy and paste the below code to the new module.

VBA: Fix broken links

Sub ResetInvalidLinks()
'UpdatedbyExtendoffice20220701
Dim xWB As Workbook
Dim xLks As Variant
Dim xFNum, xStatus As Integer
Dim xStrLk, xLinAddress As String
Dim xF
Dim xLk
Set xWB = Application.ActiveWorkbook
xLks = xWB.LinkSources(xlExcelLinks)
If IsEmpty(xLks) Then
    MsgBox "Select updated source"
    Exit Sub
End If
On Error Resume Next
For xFNum = LBound(xLks) To UBound(xLks)
    xStrLk = xLks(xFNum)
    xStrLk = Right(xStrLk, Len(xStrLk) - InStrRev(xStrLk, "\"))
    xStatus = ActiveWorkbook.LinkInfo(xStrLk, xlLinkInfoStatus)
    If xStatus <> 0 And xStatus <> 3 Then
        MsgBox xStrLk & " the link is broken, please select new source"
        xF = Application.GetOpenFilename()
        If xF <> "" Then
            For Each xLk In ActiveSheet.UsedRange.Hyperlinks
                xLinAddress = Right(xLk.Address, Len(xLk.Address) - InStrRev(xLk.Address, "\"))
                    If InStr(xStrLk, GetAddress) <> 0 Then
                        ActiveSheet.Hyperlinks.Add Anchor:=xLk.Range, Address:=xF
                    End If
            Next
            ActiveWorkbook.ChangeLink xLks(xFNum), xF, xlLinkTypeExcelLinks
        End If
        End If
    Next
End Sub

4. Press F5 key or Run button  to activate the code. Now it searches if there are any broken links. If so, it pops out a dialog to tell you which link is broken, please select a new source. Click OK to continue.

5. In the Open window, choose the updated source, Click OK.

If there is still a broken link in the workbook, the above dialogs pop again until all broken links are fixed.

Note: if there is no broken link in the workbook, the VBA will stop after running.

Tip: if you want to view all extra links and break the broken links, Kutools for Excel’s Find and Break Broken Links feature will be a good helper.

To learn more about this feature, please view our detailed tutorial.