Giới thiệu về liên kết hỏng (Broken Links) trong Excel
Thường dữ liệu chứa liên kết đến sheet/workbook khác. Tuy nhiên, liên kết có thể bị hỏng do: Đổi tên sheet/file nguồn, Thay đổi vị trí file nguồn, Xóa file nguồn. Khi đó, liên kết không hoạt động, gây lỗi (thường là #REF!). Những liên kết này gọi là broken links.
Ví dụ: ô D5 liên kết đến ô D5 của Sheet1 trong file Sample. Nhưng tên sheet nguồn bị đổi -> ô D5 báo lỗi #REF!. Bài viết hướng dẫn 3 cách xóa liên kết hỏng này.
3 phương pháp đơn giản xóa liên kết hỏng trong Excel
1. Xóa liên kết hỏng qua lệnh Edit Links
Cách đơn giản nhất.
CÁC BƯỚC:
- Tab Data > chọn Edit Links (nhóm Queries & Connections).
- Hộp thoại Edit Links hiện ra. Chọn liên kết hỏng.
- Nhấp Break Link.
- Hộp thoại cảnh báo -> nhấn Break Links.
- Kết quả: Tất cả liên kết hỏng trong sheet bị xóa.
2. Dùng VBA Excel để xóa liên kết hỏng
Áp dụng mã VBA để quét toàn workbook, liệt kê liên kết hỏng và cho phép xóa.
CÁC BƯỚC:
- Tab Developer > Visual Basic (Code group).
- Cửa sổ VBA > Insert ➤ Module.
- Dán mã VBA sau (mã dài, bao gồm hàm kiểm tra trạng thái liên kết):
Sub RemoveLinks()
alinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(alinks) Then
Sheets.Add
shtName = ActiveSheet.Name
Set summaryWS = ActiveWorkbook.Worksheets(shtName)
summaryWS.Range("A1") = "sheet"
summaryWS.Range("B1") = "location"
summaryWS.Range("C1") = "function"
summaryWS.Range("D1") = "file"
summaryWS.Range("E1") = "outcome"
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> summaryWS.Name Then
For Each Rng In ws.UsedRange
If Rng.HasFormula Then
For j = LBound(alinks) To UBound(alinks)
filePath = alinks(j)
Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]"
If InStr(Rng.Formula, filePath) Or InStr(Rng.Formula, filePath2) Then
nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
summaryWS.Range("A" & nextrow) = ws.Name
summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
summaryWS.Range("D" & nextrow) = filePath
summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
Exit For
End If
Next j
For Each namedRng In Names
If InStr(Rng.Formula, namedRng.Name) Then
filePath = Replace(Split(Right(namedRng.RefersTo, Len(namedRng.RefersTo) - 2), "]")(0), "[", "")
nextrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row + 1
summaryWS.Range("A" & nextrow) = ws.Name
summaryWS.Range("B" & nextrow) = Replace(Rng.Address, "$", "")
summaryWS.Hyperlinks.Add Anchor:=summaryWS.Range("B" & nextrow), Address:="", SubAddress:="'" & ws.Name & "'!" & Rng.Address
summaryWS.Range("C" & nextrow) = "'" & Rng.Formula
summaryWS.Range("D" & nextrow) = filePath
summaryWS.Range("E" & nextrow) = linkStatusDescr(ActiveWorkbook.LinkInfo(CStr(filePath), xlLinkInfoStatus))
Exit For
End If
Next namedRng
End If
Next Rng
End If
Next
Columns("A:E").EntireColumn.AutoFit
lastrow = summaryWS.Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lastrow
If ActiveSheet.Range("E" & r).Value = "Source unavailable" Then countBroken = countBroken + 1
Next
If countBroken > 0 Then
sInput = MsgBox("Do you want to remove broken links of status 'Source unavailable'?", vbOKCancel + vbExclamation, "Warning")
If sInput = vbOK Then
For r = 2 To lastrow
If ActiveSheet.Range("E" & r).Value = "Source unavailable" Then
Sheets(Range("A" & r).Value).Range(Range("B" & r).Value).ClearContents
End If
Next
dummy = MsgBox(countBroken & " broken links removed", vbInformation)
End If
End If
Else
MsgBox "links absent"
End If
End Sub
Public Function linkStatusDescr(statusCode)
Select Case statusCode
Case xlLinkStatusCopiedValues: linkStatusDescr = "data copied"
Case xlLinkStatusIndeterminate: linkStatusDescr = "status unavailable"
Case xlLinkStatusInvalidName: linkStatusDescr = "wrong name"
Case xlLinkStatusMissingFile: linkStatusDescr = "Source unavailable"
Case xlLinkStatusMissingSheet: linkStatusDescr = "worksheet absent"
Case xlLinkStatusNotStarted: linkStatusDescr = "yet to start"
Case xlLinkStatusOK: linkStatusDescr = "all okay"
Case xlLinkStatusOld: linkStatusDescr = "expired"
Case xlLinkStatusSourceNotCalculated: linkStatusDescr = "yet to compute"
Case xlLinkStatusSourceNotOpen: linkStatusDescr = "inactive source"
Case xlLinkStatusSourceOpen: linkStatusDescr = "active source"
Case Else: linkStatusDescr = "status undetected"
End Select
End Function- Nhấn F5 chạy mã. Hộp thoại Macro hiện ra > chọn RemoveLinks > Run.
- Mã tạo sheet mới liệt kê các liên kết, cột "outcome" hiển thị "Source unavailable" cho liên kết hỏng.
- Hộp thoại xác nhận: "Do you want to remove broken links...?" -> nhấn OK.
- Kết quả: Các liên kết hỏng bị xóa (ClearContents).
3. Xóa liên kết hỏng từ Named Ranges (Phạm vi đặt tên)
Liên kết hỏng cũng có thể ẩn trong Named Ranges.
CÁC BƯỚC:
- Tab Formulas > thả xuống Defined Names > chọn Name Manager.
- Hộp thoại Name Manager hiện ra. Chọn liên kết hỏng.
- Nhấn Delete.
- Liên kết hỏng trong Named Range bị loại bỏ.
Kết luận
Bạn đã có thể Xóa liên kết hỏng trong Excel theo 3 phương pháp trên. Hãy áp dụng và cho biết nếu có cách khác. Theo dõi ExcelDemy để đọc thêm bài viết tương tự. Đừng quên bình luận, góp ý.
Các bài viết liên quan
- Cách ngắt liên kết trong Excel trước khi mở file (với các bước dễ dàng)
- Tại sao liên kết Excel của tôi luôn bị ngắt? (3 nguyên nhân và giải pháp)
- Cách sửa siêu liên kết hỏng trong Excel (5 cách)
- [Đã sửa!] Break Links không hoạt động trong Excel (7 giải pháp)
- Cách ngắt liên kết trong Excel và giữ giá trị (3 cách dễ dàng)