۳ روش برای شناسایی و رفع مشکل لینک های معیوب در اکسل

تاریخ انتشار ۱ اسفند ۱۳۹۹

دسته بندی

3 روش برای شناسایی و اصلاح هایپرلینک معیوب در اکسل

۳/۵ - (۲ امتیاز)

آنچه در این مقاله میخوانید:

مشکلات هایپرلینک معیوب در اکسل

روش اول: شناسایی و رفع مشکل لینک های معیوب با استفاده از Edit Links

روش دوم: شناسایی و رفع مشکل لینک های معیوب با ابزار Find and Replace

روش سوم: استفاده از VBA

دانلود فایل این آموزش

ثبت ایمیل جهت دانلود فایل

مشکلات هایپرلینک معیوب در اکسل

در مقاله مدیریت لینک های داخلی و خارجی اکسل دیدیم که چطور میتونیم لینک های موجود در یک فایل رو مدیریت کنیم. در این مقاله میخواهیم به موضوع مربوط به لینک های معیوب بپردازیم. حالا اینکه منظورمون از لینک معیوب چی هست و چطور میتونیم این مشکل رو رفع کنیم، در این مقاله بررسی خواهیم کرد.
معمولا وقتی در لینک های تعریف شده، فایل های مرجع حذف یا جا به جا بشن و یا مشکلی براشون پیش بیاد، لینک های مربوط به این فایل ها و فرمول هایی که به این فایل ها ارجاع شدن هم دچار مشکل می شن. برای حل این مشکل ابتدا باید لینک های معیوب رو شناسایی کنیم و در مرحله بعد مشکل رو حل کنیم. در ادامه به شرح این موضوع می پردازیم:

روش اول: شناسایی و رفع مشکل لینک های معیوب با استفاده از Edit Links

برای این منظور مراحل زیر رو طی می کنیم:

از تب Data و از زیر مجموعه Connections روی گزینه Edit Links کلیک می کنیم؛ (در صورتی که این گزینه غیر فعال (کم رنگ) باشه یعنی هیچ لینک خارجی در فایل وجود نداره)

شکل ۱- انتخاب Edit Links از تب Data

در پنجره Edit Links لیستی از لینک هایی رو مشاهده می کنیم که در فایل جاری وجود دارن. وقتی که این پنجره رو برای بار اول باز می کنیم در ستون Status وضعیت همه لینک ها Unknown هست. برای اینکه از وضعیت هر لینک مطلع بشیم بعد از انتخاب لینک مربوطه از لیست، روی دکمه Check Status که در سمت راست قرار گرفته کلیک می کنیم؛

شکل ۲- پنجره Edit Links

بعد از زدن دکمه Check Status در ستون Status وضعیت هر لینک نمایش داده میشه که مطابق شکل ۳ لینک هایی که خطا دارن با عبارت Error در ستون Status مشخص شدن؛

شکل ۳- نحوه نمایش لینک های معیوب در پنجره پنجره Edit Links

تا اینجای کار لینک های معیوب رو شناسایی کردیم. برای رفع مشکل لینک هایی که فایل مرجع اون ها پیدا نمیشن ابتدا لینک مربوطه رو انتخاب می کنیم و روی گزینه Change Source در سمت راست پنجره کلیک می کنیم؛

شکل ۴- انتخاب گزینه Change Source برای رفع مشکل لینک های معیوب

در پنجره Change Source فایل مرجع رو از مسیر درستش انتخاب می کنیم و دکمه OK رو می زنیم؛

شکل ۵- انتخاب مجدد فایل مرجع از پنجره Change Source

با انتخاب دوباره فایل مطابق شکل ۵، خطای نمایش داده شده در پنجره Edit Links رفع میشه و به جای عبارت خطا عبارت OK نمایش داده میشه (مطابق شکل زیر). برای تمامی خطاهای از این نوع ( عدم یافتن منبع) می تونیم همین مراحل رو طی کنیم.

شکل ۶- نحوه نمایش خطا های رفع شده در پنجره Edit Links

روش دوم: شناسایی و رفع مشکل لینک های معیوب با ابزار Find and Replace

همان طور که در روش اول شناسایی هایپرلینک معیوب مشاهده کردیم استفاده از پنجره Edit Links این امکان رو فراهم می کنه تا لیستی از منابع خارجی که فایل جاری به آن ها لینک شده فراهم بشه اما در Edit Links مشخص نمیشه که چه سلول هایی حاوی لینک هستند لذا برای شناسایی این سلول ها از ابزار Find and Replace استفاده می کنیم. برای این منظور مراحل زیر رو طی می کنیم:

کلید های Ctrl + F رو می زنیم تا پنجره Find and Replace باز بشه یا از تب Home و از زیر مجموعه Editing گزینه Find & Select و بعد گزینه Find رو انتخاب می کنیم؛
در پنجره Find and Replace و در تب Find روی دکمه Options کلیک می کنیم؛
در قسمت Find what عبارت .xl رو وارد می کنیم. این نوع جستجو باعث میشه تا تمامی فایل های اکسل با فرمت قدیمی (.xls)، فرمت جدید (.xlsx) و فایل های اکسل حاوی ماکرو (.xlsm) که به فایل جاری لینک شدن در نتیجه جستجو نمایش داده بشه. اگر اسم فایل مربوطه رو داشته باشیم می تونیم در این قسمت به جای عبارت (.xls) اسم فایل رو درج کنیم؛
در قسمت Within گزینه Workbook رو انتخاب می کنیم تا جستجو در کل شیت ها انجام بشه؛
در قسمت Look in گزینه Formulas رو انتخاب می کنیم؛
روی دکمه Find All کلیک می کنیم. نتایج جستجو مطابق شکل زیر نشان داده شده.

همانطور که در شکل زیر مشخص هست هایپرلینک معیوب در ستون Value با خطای #REF! نمایش داده شدند.

شکل ۷- نمایش لینک های معیوب در ابزار Finde and Replace با عبارت جستجوی.xl

اگر به دنبال اسم فایل مشخصی باشیم که معیوب هست می تونیم اسم فایل رو در قسمت Find What بنویسیم و نتایج جستجو رو بررسی کنیم مطابق شکل ۸ نام فایل “آذر” رو جستجو کردیم و در نتایج جستجو هم فایل های معیوب مشخص شدن که به دلیل املای نادرست اسم فایل (فروشآذر) لینک مربوطه با خطا مواجه شده.

شکل ۸- نمایش لینک های معیوب در ابزار Finde and Replace با جستجوی نام فایل

نکته:
این روش برای شناسایی لینک های معیوب صد در صد قابل اعتماد نیست. به طور مثال ممکن هست خطای #REF! نمایش داده شده در شکل ۸ مربوط به فرمولی باشه که در سلول لینک شده از فایل مرجع نوشته شده و در این حالت مشکلی در لینک بین فایل ها وجود نداره و این یعنی همه خطاهای #REF! به معنی وجود مشکل در لینک نیست. پس اگر خواستیم از این روش برای شناسایی لینک های معیوب استفاده کنیم باید روی تک تک خطاها کلیک کنیم و منشا بوجود آمدن خطا رو بررسی کنیم.

حل مشکل لینک های معیوب با ابزار Find and Replace

در بخش مربوط به نتایج جستجو در پنجره Find and Replace با کلیک بر روی هر نتیجه ای که با خطای #REF! مشخص شده می تونیم علت خطا رو شناسایی و حل کنیم یا اینکه با استفاده از امکان Replace All همه خطاهای مربوط به یک فایل نامعتبر رو برطرف کنیم. برای این منظور مراحل زیر رو طی می کنیم:

در پنجره Find and Replace روی تب Replace کلیک می کنیم؛
در قسمت Find what اسم فایل نادرست یا مسیر نادرست رو وارد می کنیم؛
در قسمت Replace with اسم فایل درست یا مسیر درست رو وارد می کنیم؛
دکمه Replace All رو می زنیم.

نکته:
بعد از زدن دکمه Replace All، پنجره Update Values مطابق شکل زیر باز میشه تا فایل اکسل درست رو انتخاب کنیم. در این حالت نباید فایلی رو انتخاب کنیم و بازدن دکمه Cancel پنجره Update Values رو می بندیم.

شکل ۹- پنجره Update Values

برای مثال فرض کنید در شکل ۱۰ می خواهیم اسم فایل “فروش آذر” رو جایگزین عبارت “فروشآذر” کنیم تا خطاهای #REF! بر طرف بشه. همان طور که در شکل ۱۰ نمایش داده شده در قسمت Find what عبارت ” فروشآذر.xlsx” رو می نویسیم و در قسمت Replace with عبارت ” فروش آذر.xlsx” و بعد دکمه Replace All رو می زنیم. مشاهده می کنیم که مطابق شکل ۱۱ تمامی خطاها در ستون Value برطرف شده و اسم فایل هم در ستون Formula تصحیح شده.

شکل ۱۰- رفع مشکل لینک های معیوب با استفاده از ابزار Find and Replace

شکل ۱۱- وضعیت ستون های Value و Formula بعد از جایگزینی

در خصوص مسیر ذخیره سازی فایل مرجع هم می تونیم از ابزار Find and Replace استفاده کنیم به این صورت که در قسمت Find What آدرس مسیر اشتباه فایل رو وارد می کنیم و در قسمت Replace With آدرس مسیر درست رو وارد می کنیم. برای مثال اگر فایل مرجع در ابتدا در فولدر Documents بوده و بعد به فولدر Reports که در زیر مجموعه فولدر Documents قرار داره منتقل شده باشه، در این حالت در قسمت Find What عبارت “\Documents\” و در قسمت Replace With عبارت “\Documents\Reports\” رو وارد می کنیم.

روش سوم: استفاده از VBA

کد VBA که در زیر نمایش داده شده همه لینک های خارجی که در یک فایل اکسل وجود دارند رو بررسی می کنه و هایپرلینک معیوب رو به صورت یک لیست نمایش میده. در کد VAB زیر برای شناسایی لینک های خارجی از روش LinkSources استفاده شده و برای شناسایی لینک های معیوب از روش LinkInfo استفاده شده.

Sub FindBrokenLinks()
    linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)
    Dim reportHeaders() As String
    Dim rangeCur As Range
    Dim sheetCur As Worksheet
    Dim rowNo As Integer
    Dim linkFilePath, linkFilePath2, linkFileName As String
    Dim linksStatusDescr As String  'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus
    Dim sheetReportName As String
 
    sheetReportName = "Broken Links report"
    linksStatusDescr = "File missing"
    reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")
    rowNo = 1 'Header row
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then
        ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear
    Else
        Sheets.Add.Name = sheetReportName
    End If
    Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)
 
    For indI = 0 To UBound(reportHeaders)
        sheetReport.Cells(rowNo, indI + 1) = reportHeaders(indI)
    Next
 
    For Each sheetCur In ActiveWorkbook.Worksheets
        If sheetCur.Name <> sheetReport.Name Then
            For Each rangeCur In sheetCur.UsedRange
                If rangeCur.HasFormula Then
                    For indI = LBound(linksDataArray) To UBound(linksDataArray)
                        linkFilePath = linksDataArray(indI)   'LinkSrouces returns the full file path with the file name
                        linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, "\"))   'extract only the file name
                        linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "\")) & "[" & linkFileName & "]"  'the file path with the workbook name in square brackets
                        linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
 
                        If xlLinkStatusMissingFile = linksStatusCode And (InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2)) Then
                            rowNo = rowNo + 1
                            With sheetReport
                                .Cells(rowNo, 1) = sheetCur.Name
                                .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
                                .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
                                .Cells(rowNo, 3) = "'" & rangeCur.Formula
                                .Cells(rowNo, 4) = linkFilePath
                                .Cells(rowNo, 5) = linksStatusDescr
                            End With
                            Exit For
                        End If
                    Next indI
 
                    For Each namedrangeCur In Names
                       If InStr(rangeCur.Formula, namedrangeCur.Name) Then
                            linkFilePath = ""
                            linksStatusCode = -1
 
                            If 0 < InStr(namedrangeCur.RefersTo, "[") Then
                                linkFilePath = Replace(Split(Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
                                linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
                            End If
                            If xlLinkStatusMissingFile = linksStatusCode Then
                                rowNo = rowNo + 1
                               With sheetReport
                                   .Cells(rowNo, 1) = sheetCur.Name
                                   .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
                                   .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
                                   .Cells(rowNo, 3) = "'" & rangeCur.Formula
                                   .Cells(rowNo, 4) = linkFilePath
                                   If 0 < Len(linkFilePath) Then
                                    .Cells(rowNo, 5) = linksStatusDescr
                                   End If
                               End With
                            End If
                            Exit For
                        End If
                    Next namedrangeCur
                End If
            Next rangeCur
        End If
    Next
    Columns("A:E").EntireColumn.AutoFit
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

۱۰

۱۱

۱۲

۱۳

۱۴

۱۵

۱۶

۱۷

۱۸

۱۹

۲۰

۲۱

۲۲

۲۳

۲۴

۲۵

۲۶

۲۷

۲۸

۲۹

۳۰

۳۱

۳۲

۳۳

۳۴

۳۵

۳۶

۳۷

۳۸

۳۹

۴۰

۴۱

۴۲

۴۳

۴۴

۴۵

۴۶

۴۷

۴۸

۴۹

۵۰

۵۱

۵۲

۵۳

۵۴

۵۵

۵۶

۵۷

۵۸

۵۹

۶۰

۶۱

۶۲

۶۳

۶۴

۶۵

۶۶

۶۷

۶۸

۶۹

۷۰

۷۱

۷۲

۷۳

۷۴

۷۵

۷۶

۷۷

۷۸

۷۹

۸۰

۸۱

۸۲

۸۳

۸۴

۸۵

۸۶

۸۷

Sub FindBrokenLinks()

linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)

Dim reportHeaders() As String

Dim rangeCur As Range

Dim sheetCur As Worksheet

Dim rowNo As Integer

Dim linkFilePath, linkFilePath2, linkFileName As String

Dim linksStatusDescr As String 'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus

Dim sheetReportName As String

sheetReportName = "Broken Links report"

linksStatusDescr = "File missing"

reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")

rowNo = ۱ 'Header row

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then

ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear

Else

Sheets.Add.Name = sheetReportName

End If

Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)

For indI = ۰ To UBound(reportHeaders)

sheetReport.Cells(rowNo, indI + ۱) = reportHeaders(indI)

For Each sheetCur In ActiveWorkbook.Worksheets

If sheetCur.Name <> sheetReport.Name Then

For Each rangeCur In sheetCur.UsedRange

If rangeCur.HasFormula Then

For indI = LBound(linksDataArray) To UBound(linksDataArray)

linkFilePath = linksDataArray(indI) 'LinkSrouces returns the full file path with the file name

linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, "\")) 'extract only the file name

linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "\")) & "[" & linkFileName & "]" 'the file path with the workbook name in square brackets

linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)

If xlLinkStatusMissingFile = linksStatusCode And (InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2)) Then

rowNo = rowNo + ۱

With sheetReport

.Cells(rowNo, ۱) = sheetCur.Name

.Cells(rowNo, ۲) = Replace(rangeCur.Address, "$", "")

.Hyperlinks.Add Anchor:=.Cells(rowNo, ۲), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address

.Cells(rowNo, ۳) = "'" & rangeCur.Formula

.Cells(rowNo, ۴) = linkFilePath

.Cells(rowNo, ۵) = linksStatusDescr

End With

Exit For

End If

Next indI

For Each namedrangeCur In Names

If InStr(rangeCur.Formula, namedrangeCur.Name) Then

linkFilePath = ""

linksStatusCode = -۱

If ۰ < InStr(namedrangeCur.RefersTo, "[") Then

linkFilePath = Replace(Split(Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - ۲), "]")(۰), "[", "")

linksStatusCode = ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)

End If

If xlLinkStatusMissingFile = linksStatusCode Then

rowNo = rowNo + ۱

With sheetReport

.Cells(rowNo, ۱) = sheetCur.Name

.Cells(rowNo, ۲) = Replace(rangeCur.Address, "$", "")

.Hyperlinks.Add Anchor:=.Cells(rowNo, ۲), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address

.Cells(rowNo, ۳) = "'" & rangeCur.Formula

.Cells(rowNo, ۴) = linkFilePath

If ۰ < Len(linkFilePath) Then

.Cells(rowNo, ۵) = linksStatusDescr

End If

End With

End If

Exit For

End If

Next namedrangeCur

End If

Next rangeCur

End If

Columns("A:E").EntireColumn.AutoFit

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub

کد VBA مورد نظر در فایل اکسل جهت پیدا کردن هایپرلینک معیوب در انتهای مقاله قرار داده شده. برای اجرای این کد مراحل زیر رو طی می کنیم.

بعد از باز کردن فایل اکسل کلیدهای Alt + F8 رو می زنیم؛
از پنجره Macro گزینه FindBrokenLinks رو انتخاب می کنیم؛
دکمه Run رو می زنیم. مطابق شکل ۱۲

شکل ۱۲- پنجره Macro

در یک شیت جداگانه به نام ” Broken Links report” لیستی از لینک های معیوب مطابق شکل زیر نمایش داده میشه

شکل ۱۳- لیست لینک های معیوب پس از اجرا ماکرو FindBrokenLinks

در این مقاله سه روش شناسایی هایپرلینک معیوب رو بررسی کردیم و نحوه برطرف کردن مشکل لینک های معیوب رو هم توضیح دادیم تا با توجه به نیاز از بین این سه روش، روش مناسب تر رو انتخاب کنید.

دانلود فایل این آموزش

جهت دانلود فایل آموزش شناسایی و اصلاح هایپرلینک معیوب از لینک زیر استفاده کنید:

کلیدواژه : ترفندهای اکسل

حسنا خاکزاد 144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران

سعید زال نژاد ۲۴ آذر ۱۴۰۳ / ۱۰:۱۳ ق٫ظ

پاسخ

سلام خانم خاکزاد
من وقتی میخواهم فایل modofficebazshamsi.bas رو درگ کنم توی یک فایل اکسل(قبلش با alt+f11 داخل قسمت vba اون فایل شدم) که بتونم از تاریخ شمسی استفاده کنم، پیغام ارور path not found میده و نمیدونم چکار کنم..
لطفا راهنمایی بفرمایید.
- حسنا خاکزاد ۲۴ آذر ۱۴۰۳ / ۱۱:۱۱ ق٫ظ
  
  پاسخ
  
  درود
  این چه فایلی هست؟
  اگر کد رو جدا دارید import کنید در اون محیط
نیما ۱۳ آذر ۱۴۰۳ / ۹:۴۷ ب٫ظ

پاسخ

سلام بنده میخواستم تو اکسل وقتی روی سلول کلیک میکنم هاپر لینک باز نشه و کنترل رو هم بگیرم بعد فایل هاپر شده باز بشه ممنون مبشم
- حسنا خاکزاد ۱۴ آذر ۱۴۰۳ / ۱۰:۴۹ ق٫ظ
  
  پاسخ
  
  درود
  تابع اینطوری نمیتونه بشه
  ابزار هم در مورد لینک های بیرون از اکسل اینطوری عمل میکنه روی سلول رو اینطوری عمل نمیکنه
شهروز ۲۲ خرداد ۱۴۰۲ / ۱:۲۷ ب٫ظ

پاسخ

ممنون از شما.
فقط یک سوال من هنگام کار با اکسل چند بار برق سیستمم قطع و وصل شد. فایل اتوسیو رو که باز کردم و سیو اس گرفتم لینک هام دیگه کار نمیکن. با همون اسم قبلی هم سیو کردم.
به نظرتون مشکل چیه ؟
- حسنا خاکزاد ۲۳ خرداد ۱۴۰۲ / ۱۲:۵۲ ب٫ظ
  
  پاسخ
  
  درود
  در قسمت ادیت بینید لینکها چه مشکلی دارن
  چی چیزی تغییر کرده
  شاید اون کلمه autosave و … اینا اضافه شده
علی ۲۱ دی ۱۴۰۱ / ۱۱:۲۴ ق٫ظ

پاسخ

خیلی خوب بود ، مشکلم حل شد ، ممنون
nima ۸ تیر ۱۴۰۱ / ۱:۲۷ ب٫ظ

پاسخ

سلام توضیحات بسیار عالی بود من فقط یه مشکل با لینک های دارم که وقتی فایل اکسل به سیستم دیگه جابجا میکنم دیگه لینک ها فایل بایز نمی کنند نمیدونم تیک گزینه خاصی باید زد؟
- حسنا خاکزاد ۸ تیر ۱۴۰۱ / ۵:۲۳ ب٫ظ
  
  پاسخ
  
  درود
  باید همه با هم در یک فولدر جابجا بشن
  یا اینکه وقتی جابجا کردین لینک فایل جدید رو بدید

ارسال دیدگاه