แก้ Broken Excel Links เร็วๆ: 3 วิธีที่พิสูจน์แล้ว

Microsoft Excel เป็นซอฟต์แวร์ทรงพลัง สามารถดำเนินการมากมายบนชุดข้อมูล มี Excel Functions เริ่มต้นมากมายสำหรับสร้างสูตร สถาบันการศึกษาและบริษัทหลายแห่งใช้ไฟล์ Excel เก็บข้อมูลสำคัญ บางครั้งนำข้อมูลจากแหล่งต่างๆ โดย Link ไฟล์ Excel หลายไฟล์ แต่ลิงก์อาจ Broken (เสียหาย) จากหลายสาเหตุ ทำให้เกิด Error ในชีตที่ทำงานอยู่ จึงต้องลบ Broken Links เหล่านั้น บทความนี้แสดง 3 วิธีง่าย Remove Broken Links ใน Excel

ดาวน์โหลด Workbook ต่อไปเพื่อฝึกเอง

บทนำเกี่ยวกับ Broken Links ใน Excel

บ่อยครั้ง Dataset มี Links ไปยัง Worksheets หรือ Workbooks อื่น แต่มีเหตุผลทำให้ Links Break สาเหตุที่เป็นไปได้: เปลี่ยนชื่อ Source Worksheet หรือ Workbook, เปลี่ยนตำแหน่ง Source File, และ ลบไฟล์ หากกรณีเหล่านี้เกิดขึ้น Links จะไม่ทำงานถูกต้องใน Active Worksheet จะเกิด Errors เรียก Links เหล่านี้ว่า Broken Links

ใน Dataset ต่อไป เซลล์ D5 ลิงก์กับเซลล์ D5 ของ Sheet1 ในไฟล์ Sample

อย่างไรก็ตาม ชื่อ Source Sheet เปลี่ยนในไฟล์ Sample ทำให้เห็น Error #REF! ในภาพด้านล่าง บทความนี้จะแสดงวิธีลบลิงก์แบบนี้

3 วิธีง่าย Remove Broken Links ใน Excel

มีหลายวิธี Remove Broken Links ที่นี่อธิบาย 3 วิธี ทำตามมา

1. Remove Broken Links ผ่านคำสั่ง Edit Links ใน Excel

กระบวนการง่ายที่สุดคือใช้ Edit Links Command ทำตามขั้นตอน

ขั้นตอน:

• ไปที่แท็บ Data
• เลือก Edit Links จากกลุ่ม Queries & Connections

• กล่อง Edit Links จะโผล่
• เลือก Broken Link
• คลิก Break Link option

• จะได้กล่องเตือน
• กด Break Links

• จะลบ Broken Links ทั้งหมดใน Worksheet

อ่านเพิ่มเติม: วิธี Break Links ใน Excel (3 วิธีเร็ว)

2. ใช้ Excel VBA ลบ Broken Links

นอกจากนี้ ยังใช้ VBA Codes ทำได้ เรียนรู้กระบวนการต่อไปนี้

ขั้นตอน:

• ไปที่แท็บ Developer
• เลือก Visual Basic จากกลุ่ม Code

• ในหน้าต่าง VBA คลิก Insert ➤ Module
• Copy โค้ดด้านล่าง วางใน Module Box

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)
                        'LinkSources returns full file path with file name
                        Filename = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
                        'extract just the file name
                        filePath2 = Left(alinks(j), InStrRev(alinks(j), "\")) & "[" & Filename & "]"
                        'file path with brackets
                        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), "[", "")
                            'remove =' and range in the file path
                            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
        End If
    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
                    dummy = MsgBox(countBroken & " broken links removed", vbInformation)
                End If
            Next
        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

• กด F5 รันโค้ด
• กล่อง Macro จะปรากฏ
• เลือก RemoveLinks กด Run

• จะคืนชีตใหม่
• เห็น Source unavailable ในคอลัมน์ outcome

• พร้อมกันจะได้กล่องเตือนในชีตเดียวกัน
• กด OK

• สุดท้าย Broken Links จะถูกลบ

อ่านเพิ่มเติม: วิธี Break Links ใน Excel เมื่อ Source ไม่พบ (4 วิธี)

3. ลบ Broken Links จาก Named Ranges

อีกครั้ง อาจมี Broken Links ใน Named Ranges ทำตามกระบวนการด้านล่างเพื่อลบ

ขั้นตอน:

• ไปที่แท็บ Formulas
• เลือก Drop-down Defined Names เลือก Name Manager

• กล่อง Name Manager จะปรากฏ
• เลือก Broken Link
• กด Delete

• ด้วยวิธีนี้ สามารถกำจัด Broken Links ได้

อ่านเพิ่มเติม: วิธีหา Broken Links ใน Excel (4 วิธีเร็ว)

สรุป

ดังนั้น คุณจะ Remove Broken Links ใน Excel ได้ตามวิธีที่อธิบาย ใช้งานต่อไปและแจ้งให้ทราบหากมีวิธีอื่น ติดตามเว็บไซต์ ExcelDemy สำหรับบทความแบบนี้ อย่าลืมคอมเมนต์ ข้อเสนอแนะ หรือคำถามในส่วนความคิดเห็น

บทความเกี่ยวข้อง

• วิธี Break Links ใน Excel ก่อนเปิดไฟล์ (พร้อมขั้นตอน)
• ทำไม Excel Links ของฉันถึง Break อยู่เสมอ? (3 เหตุผลพร้อมวิธีแก้)
• วิธีแก้ Broken Hyperlinks ใน Excel (5 วิธี)
• [Fixed!] Break Links Not Working in Excel (7 Solutions)
• วิธี Break Links ใน Excel และเก็บค่า (3 วิธีง่าย)