مدیریت لینک های داخلی و خارجی در اکسل

استفاده از لینک و ایجاد ارتباط بین اجزاء مختلف یک فایل یا حتی بین فایل های دیگه یکی از مسائل پرکاربرد و مهم در اکسل هست. پس توانایی مدیریت لینک ها (ایجاد، ویرایش و حذف) خیلی خیلی اهمیت داره. به همین دلیل ردیابی تمامی ارجاعات و لینک های مورد استفاده در یک فایل اکسل موضوعی چالش برانگیز برای کاربران هست. قطعا هیچ راه سریعی برای شناسایی تمام لینک ها در یک فایل اکسل وجود نداره و براساس اینکه لینک در فرمول، شکل، نمودار یا Defined Names تعریف شده باشه، روش های شناسایی متفاوت خواهد بود.

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

پیدا کردن لینک ها در فایل اکسل

در بخش اول روش های شناسایی لینک ها رو در هر کدوم از حالات گفته شده (فرمول، اشکال، نمودار یا Defined Names) بررسی می کنیم.

جستجوی لینک در سلول ها

مرسوم ترین نوع استفاده از لینک ها به کارگیری اون ها در سلول ها هست که راحت ترین حالت برای جستجو و حذف رو دارند. برای پیدا کردن این لینک ها مراحل زیر رو طی می کنیم:

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

شکل ۱- جستجوی سلول های حاوی لینک با استفاده از ابزار Find

مطابق شکل ۱ لیستی از تمامی سلول هایی که در آن ها از لینک استفاده شده (در ستون Cell) و نام فایلی که سلول به آن لینک شده (در ستون (Formula مشخص هست.در صورتی که بخواهیم یک از سلول هایی که لینک دارند رو انتخاب کنیم، از ستون Cell روی آدرس سلول مورد نظر کلیک می کنیم. برای اینکه تمامی سلول هایی که لینک دارند رو انتخاب کنیم، در پنجره ای که نتایج جستجو نمایش داده شده کلیک می کنیم و Ctrl + A رو می زنیم.

نکته:
با استفاده از ابزار Find and Replace فقط می تونیم لینک های استفاده شده در سلول ها رو شناسایی کنیم و لینک های استفاده شده در نمودارها، Defined Names و شکل ها رو باید با روش های دیگه ای جستجو کنیم که در ادامه به این موضوع می پردازیم

جستجوی لینک در Define Name

کاربران اکسل اغلب برای تسهیل در نوشتن و فهم بهتر فرمول ها، محدوده های مورد استفاده در فرمول ها رو نام گذاری می کنند که این محدوده ها می تونن در خارج از فایل جاری هم قرار داشته باشن. برای اینکه محدوده های نام گذاری شده ای که با فایل های دیگه لینک هستند رو شناسایی کنیم، مسیر زیر رو طی می کنیم:

در تب Formula و در زیر مجموعه Define Name روی گزینه Name Manager کلیک می کنیم یا کلید میانبر Ctrl + F3 رو می زنیم؛
در پنجره Name Manager مطابق شکل ۲ لیست محدوده هایی که نام گذاری شدند نمایش داده شده که در ستون Refers To محدوده هایی که به یک فایل خارجی لینک شدند در داخل دو تا براکت نمایش داده شدن مثل [xlsx]

شکل ۲- شناسایی و مدیریت لینک های خارجی در Define Name

جستجوی لینک در Objects

برای شناسایی تمامی Objectهای استفاده شده در فایل اکسل از ابزار Go To Special استفاده می کنیم و مسیر زیر رو طی می کنیم:

در تب Home و در زیرمجموعه Editing روی گزینه Find & Select کلیک می کنیم و گزینه Go to Special رو انتخاب می کنیم و یا کلید میانبر F5 رو می زنیم و در پنجره Go To گزینه Special رو انتخاب می کنیم.
در پنجره Go to Special مطابق شکل ۳ گزینه Objects رو انتخاب می کنیم و تمامی Objects به حالت انتخاب درمیان.

شکل ۳- پنجره Go To Special

کلید تب رو می زنیم تا Objects موجود در فایل یکی یکی به حالت انتخاب در بیان، و با انتخاب هر Object چک می کنیم که آیا به فایل دیگری لینک شده یا خیر. در صورتی که Object به یک سلول از یک فایل خارجی لینک شده باشه وقتی Object رو انتخاب می کنیم در نوار فرمول (Formula Bar) می تونیم اسم فایل و آدرس سلول رو مطابق شکل ۴ مشاهده کنیم. راجع به اتصال Shape به سلول این مقاله رو مطالعه کنید

شکل ۴- لینک Object به یک سلول از فایل خارجی

در صورتی که Object به یک فایل لینک شده باشه (Hyperlink)، وقتی موس رو روی Object نگه می داریم مطابق شکل ۵ آدرس فایل مربوطه نمایش داده می شه.

شکل ۵- لینک Object به یک فایل خارجی

نکته:
در صورتی که Object به یک فایل خارجی لینک شده باشه برای حذف کردن لینک نمی توانیم از Edit Links استفاده کنیم. برای این منظور روی Object کلیک راست می کنیم و از لیست نمایش داده شده گزینه Remove link رو انتخاب می کنیم.

جستجوی لینک در نمودارها

برای شناسایی و مدیریت لینک های استفاده شده در یک نمودار مراحل زیر روی طی می کنیم:

در نمودار مورد نظر روی عنوان نمودار (chart title) یا سری داده ای (data series) کلیک می کنیم؛
در صورتی که عنوان نمودار یا سری داده ای به فایلی لینک شده باشند مطابق شکل ۶ در نوار فرمول نام فایل مربوطه (“فروش مهر”) نشان داده میشه.

شکل ۶- لینک عنوان نمودار و سری داده ای به فایل خارجی

اگر نمودار از چند سری داده ای تشکیل شده باشه (مثل شکل ۶ که نمودار دو تا سری داده ای داره) برای شناسایی لینک های هر سری داده ای به شکل زیر عمل می کنیم:

نمودار رو انتخاب می کنیم؛
از تب Format و از زیر مجموعه Current Selection، بر روی فلش Chart Elements کلیک می کنیم و از لیست نمایش داده شده، سری مورد نظرمون رو انتخاب می کنیم و با مراجعه به نوار فرمول می تونیم لینک تعریف شده رو مشاهده کنیم. (مطابق شکل ۷)

شکل ۷- نمایش لینک ها در نمودار با بیش از یک سری داده ای

جستجوی و مدیریت لینک در Pivot Table

بیشتر مواقع Pivot Table و منبع داده های ورودی اون در یک فایل قرار دارند اما در بعضی مواقع از داده های یک منبع خارجی برای ساخت Pivot Table استفاده می کنیم. برای اینکه متوجه بشیم منبع داده های Pivot Table کجا قرار گرفته به شکل زیر عمل می کنیم:

یکی از سلول های Pivot Table رو انتخاب می کنیم؛
در تب Analyze و در زیر مجموعه Data روی دکمه Change Data Source کلیک می کنیم؛

شکل ۸- شناسایی منبع داده ای Pivot Table

در پنجره Change Pivot Table Data Source و در قسمت Table/Range آدرس داده های مورد استفاده در Pivot Table مشخص شده. همان طور که در شکل ۹ مشخص شده داده ها از فایلی با نام “فروش مهر” گرفته شده.

شکل ۹- پنجره Change Pivot Table Data Source

بروزرسانی و مدیریت لینک های ایجاد شده به فایل های خارجی

وقتی فایل اکسلی رو باز می کنیم که به فایل های دیگری لینک شده، اکسل پیغامی رو مطابق شکل ۱۰ نمایش میده مبنی بر اینکه فایل مربوطه با فایل های دیگری لینک هست. در صورتی که از لینک های فایل اطمینان داشته باشیم، گزینه Update رو انتخاب می کنیم تا اطلاعات فایل بروز رسانی بشه.

شکل ۱۰- پیغام بروز رسانی لینک های فایل

هر بار که فایل حاوی لینک (به فایل های دیگر) رو باز می کنیم اکسل این پیغام رو نمایش میده. برای تنظیم نمایش/ عدم نمایش این پیغام و نحوه بروز رسانی لینک ها به شکل زیر عمل می کنیم:

در تب Data و در زیرمجموعه Connections بر روی گزینه Edit Links کلیک می کنیم؛
مطابق شکل ۱۱ در گوشه پایین سمت چپ پنجره Edit Links، روی گزینه Startup Prompt… کلیک می کنیم؛

شکل ۱۱- پنجره Edit Links برای مدیریت لینک های خارجی

در پنجره Startup Prompt مطابق شکل ۱۲ یکی از گزینه ها رو انتخاب می کنیم. عملکرد هر کدوم از گزینه ها به شرح زیر است:

گزینه اول که به صورت پیش فرض انخاب شده، این امکان رو به کاربر میده تا خودش نمایش / عدم نمایش پیغام رو مشخص بکنه؛

با انتخاب گزینه دوم پیغام نمایش داده نمیشه و لینک ها هم به طور خودکار بروز رسانی نمیشن؛

با انتخاب گزینه سوم پیغام نمایش داده نمیشه و لینک ها بروزرسانی می شن.

شکل ۱۲- پنجره Startup Prompt

برای حذف پیغام و خودکار کردن بروزرسانی لینک ها یک روش دیگر هم وجود داره که تنظیمات اون به ترتیب زیر هست:

در فایل اکسل مربوطه بر روی تب File روی گزینه Options کلیک می کنیم؛
در پنجره Excel Options روی Trust Center کلیک می کنیم و بعد مطابق شکل ۱۳ روی Trust Center Settings کلیک می کنیم؛

شکل ۱۳- Trust Center در پنجره Excel Options

در پنجره Trust Center مطابق شکل ۱۴ روی گزینه External Content کلیک می کنیم و بعد گزینه Enable automatic update for all Workbook Links که در زیر عنوان Security Settings for Workbook Links قرار دارد را انتخاب می کنیم؛

شکل ۱۴- تنظیمات بروزرسانی و مدیریت لینک ها در پنجره Trust Center

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

توجه داشته باشید که بروزرسانی خودکار لینک های مربوط به فایل های شناخته نشده می تونه خطرناک باشه و به همین دلیل انتخاب این گزینه توصیه نمیشه. بهتره این گزینه رو زمانی فعال کنیم که صد در صد از امنیت فایل های خارجی مطمئن هستیم یا اینکه به طور موقت این گزینه رو فعال کنیم و بعد دوباره حالت پیش فرض رو (Prompt user on automatic update for Workbook Links) انتخاب کنیم.

در صورتی که فایل حاوی لینک های حذف شده یا نامعتبر باشه پیغام زیر (شکل ۱۵) نمایش داده میشه.

شکل ۱۵- پیغام نمایش داده در صورت نامعتبر بودن یا حذف شدن لینک ها

قطع کردن لینک ها با فایل های خارجی

وقتی در فایل اکسل ارتباط لینک خارجی رو با فایل قطع می کنیم در واقع مقدار فعلی منبع خارجی رو در فایل جاری جایگزین کردیم. به طور مثال اگر در ارجاع زیر لینک Source data.xlsx رو قطع کنیم، مقدار سلول A1 در شیت Jan در فایل Source data جایگزین عبارت زیر خواهد شد.

='[Source data.xlsx]Jan’!$A$1

نکته:
با توجه به اینکه قطع کردن لینک ها با Undo کردن قابل بازگشت نیست بهتره قبل از اینکه لینکی رو قطع کنیم حتما تمامی جوانب رو بسنجیم.

برای قطع کردن لینک به صورت زیر عمل می کنیم:

در تب Data در زیر مجموعه Connections رو گزینه Edit Links کلیک می کنیم؛(در صورتی که در فایل مربوطه لینکی وجود نداشته باشه این گزینه به حالت غیر فعال در میاد)
در پنجره Edit Links مطابق شکل ۱۶ لینک هایی که می خواهیم حذف کنیم رو انتخاب می کنیم. برای انتخاب بیش از یک لینک، دکمه Ctrl رو نگه می داریم و لینک های مورد نظر رو انتخاب می کنیم و برای انتخاب همه لینک ها Ctrl + A رو می زنیم.
روی دکمه Break Link کلیک می کنیم.

شکل ۱۶- نحوه حذف و مدیریت لینک ها در پنجره Edit Links

نکته:
با توجه به اینکه همه لینک های مورد استفاده در فایل در پنجره Edit Links نمایش داده نمیشه (برای مثال لینک های که در Pivot Table تعریف شدند) پس اگر گزینه Edit Links غیر فعال بود و باز هم در مورد لینک به فایل های خارجی پیغامی دریافت کردیم باید تمامی قسمت هایی که احتمال میدیم که لینک داشته باشن (مثلا نمودارها، شکل ها و…) رو چک کنیم.

دسترسی به لیستی از لینک های خارجی

یکی از مرسوم ترین راه ها برای دسترسی به لیست لینک های موجود در فایل، استفاده از پنجرهEdit Links هست. مطابق شکل ۱۶ این پنجره اطلاعات زیر رو در مورد لینک ها مشخص می کنه:

Source: در این بخش اسم فایل لینک شده نمایش داده میشه

Type: این قسمت مشخص می کنه که فایل Worksheet یا Workbook

Update: این قسمت روش بروزرسانی لینک رو مشخص می کنه که می تونه دستی (Manually) یا خودکار (Automatically) باشه

Status: این بخش وضعیت لینک رو مشخص می کنه که می تونه یکی از موارد ,OK, Warning, Unknown Source is Open باشه. برای دریافت آخرین وضعیت مربوط به لینک روی گزینه Check Status کلیک می کنیم.

اما همان طور که قبلا هم گفتیم در این پنجره تمامی لینکها نمایش داده نمی شن.

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

Sub Get_Links()
    Dim links As Variant
    links = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(links) Then
        Sheets.Add
        For i = 1 To UBound(links)
            Cells(i, 1).Value = links(i)
        Next i
    Else
        MsgBox "No external links are found.", vbInformation, "Find Links"
    End If
End Sub

۱۰

۱۱

۱۲

Sub Get_Links()

Dim links As Variant

links = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(links) Then

Sheets.Add

For i = ۱ To UBound(links)

Cells(i, ۱).Value = links(i)

Next i

Else

MsgBox "No external links are found.", vbInformation, "Find Links"

End If

End Sub

شکل ۱۷- کد VBA برای نمایش لیستی از لینک های خارجی

برای استفاده از این کد مطابق مراحل زیر عمل می کنیم:

کلید های Alt + F11 رو می زنیم تا Visual Basic Editor نمایش داده بشه؛
در سمت چپ صفحه مطابق شکل ۱۸ روی ThisWorkbook کلیک راست می کنیم و از زیر مجموعه Insert گزینه Module رو انتخاب می کنیم؛

شکل ۱۸- پنجره Visual Basic Editor

کد شکل ۱۷ رو در صفحه ای که باز شده کپی می کنیم؛
برای اجرای ماکرو، در صفحه اکسل کلیدهای Alt + F8 رو می زنیم، پنجره Macro مطابق شکل ۱۹ نمایش داده می شه. در پنجره Macro، اسم ماکرو مورد نظر رو انتخاب می کنیم (Get_Links) و روی دکمه Run کلیک می کنیم تا ماکرو اجرا بشه. مطابق شکل ۱۹ لیست لینک هایی که در فایل استفاده شده نمایش داده میشه

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

شکل ۲۰- نمایش لیست لینک های موجود در فایل با استفاده از اجرای ماکرو Get_Links

در صورتی که بخواهیم در نتایج شکل ۲۰ آدرس سلول هایی که حاوی لینک هستند هم نمایش داده بشه از کد VBA زیر استفاده می کنیم (برای دسترسی کامل به این کد از فایل اکسلی که در انتهای مقاله درج شده استفاده کنید) و دقیقا مراحل مثال قبل رو برای اجرای ماکرو طی می کنیم.

Sub Cells_With_Links()
 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
 Dim sheetReportName As String
 
 sheetReportName = "All Links report"
 linksStatusDescr = Split("No errors/File missing/Sheet missing/Status may be out of date/Not yet calculated/Unable to determine status/Not started/Invalid name/Not open/Source document is open/Copied values", "/")
 reportHeaders = Split("Worksheet,Cell,Formula,Workbook,Link Status", ",")
 
 rowNo = 1 'Header row
 
 If Not IsEmpty(linksDataArray) Then
  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
 
       If 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(ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus))
        End With
        Exit For
       End If
      Next indI
 
      For Each namedrangeCur In Names
         If InStr(rangeCur.Formula, namedrangeCur.Name) Then
        rowNo = rowNo + 1
        linkFilePath = ""
 
        If 0 < InStr(namedrangeCur.RefersTo, "[") Then
         linkFilePath = Replace(Split(Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
        End If
 
          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
           If 0 < Len(linkFilePath) Then
          .Cells(rowNo, 4) = linkFilePath
           .Cells(rowNo, 5) = "'" & ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)
           Else
          .Cells(rowNo, 4) = "Unknown"
          .Cells(rowNo, 5) = "Unknown"
           End If
          End With
          Exit For
       End If
      Next namedrangeCur
     End If
    Next rangeCur
   End If
  Next
  Columns("A:E").EntireColumn.AutoFit
 
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
 Else
  MsgBox "No external links"
 End If
End Sub

۱۰

۱۱

۱۲

۱۳

۱۴

۱۵

۱۶

۱۷

۱۸

۱۹

۲۰

۲۱

۲۲

۲۳

۲۴

۲۵

۲۶

۲۷

۲۸

۲۹

۳۰

۳۱

۳۲

۳۳

۳۴

۳۵

۳۶

۳۷

۳۸

۳۹

۴۰

۴۱

۴۲

۴۳

۴۴

۴۵

۴۶

۴۷

۴۸

۴۹

۵۰

۵۱

۵۲

۵۳

۵۴

۵۵

۵۶

۵۷

۵۸

۵۹

۶۰

۶۱

۶۲

۶۳

۶۴

۶۵

۶۶

۶۷

۶۸

۶۹

۷۰

۷۱

۷۲

۷۳

۷۴

۷۵

۷۶

۷۷

۷۸

۷۹

۸۰

۸۱

۸۲

۸۳

۸۴

۸۵

۸۶

۸۷

۸۸

۸۹

۹۰

۹۱

Sub Cells_With_Links()

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

Dim sheetReportName As String

sheetReportName = "All Links report"

linksStatusDescr = Split("No errors/File missing/Sheet missing/Status may be out of date/Not yet calculated/Unable to determine status/Not started/Invalid name/Not open/Source document is open/Copied values", "/")

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

rowNo = ۱ 'Header row

If Not IsEmpty(linksDataArray) Then

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

If 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(ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus))

End With

Exit For

End If

Next indI

For Each namedrangeCur In Names

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

rowNo = rowNo + ۱

linkFilePath = ""

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

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

End If

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

If ۰ < Len(linkFilePath) Then

.Cells(rowNo, ۴) = linkFilePath

.Cells(rowNo, ۵) = "'" & ActiveWorkbook.LinkInfo(CStr(linkFilePath), xlLinkInfoStatus)

Else

.Cells(rowNo, ۴) = "Unknown"

.Cells(rowNo, ۵) = "Unknown"

End If

End With

Exit For

End If

Next namedrangeCur

End If

Next rangeCur

End If

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

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Else

MsgBox "No external links"

End If

End Sub

نتیجه اجرای این کد در شکل زیر نمایش داده شده. همان طور که مشخص هست در خروجی این ماکرو، آدرس سلول ها و شیت هایی که حاوی لینک هستند هم اضافه شده.

شکل ۱۲- نمایش لیست لینک های موجود در فایل به همراه آدرس سلول ها با اجرای ماکرو Cells_With_Links

در این مقاله روش جستجوی لینک ها در سلول، Pivot Table، نمودار و Object رو یاد گرفتیم همچنین نحوه تنظیم پیغام مربوط به بروزرسانی لینک ها و روش حذف لینک ها رو توضیح دادیم و در انتها هم دو نمونه کد VBA برای نمایش لیست لینک ها در فایل اکسل رو ارائه کردیم.

دانلود فایل های آموزش مدیریت لینک

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

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

رضا ۲ تیر ۱۴۰۳ / ۵:۳۰ ب٫ظ

پاسخ

سلام
آیا میشود تابع هایپرلینک را به نحوی نوشت که اگر پوشه فایل ها دو نوع پسوند را داشته باشیم مثلا jpg و pdf خودش بگردد و هرکدام موجود بود را باز کند؟
- حسنا خاکزاد ۸ تیر ۱۴۰۳ / ۸:۱۳ ب٫ظ
  
  پاسخ
  
  درود بر شما
  یعنی اسم فایل یکی باشه؟
گلزاری ۱۶ مهر ۱۴۰۲ / ۸:۱۱ ب٫ظ

پاسخ

سلام، وقت بخیر
مطالبتان خیلی عالی و کاربردی بودند. من لینک یک عکس چارت را از سایت تریدینگ ویو در سلول اکسل وارد می کنم ولی باز نمیشه مشکل از چیست؟
- حسنا خاکزاد ۱۷ مهر ۱۴۰۲ / ۱۲:۴۸ ب٫ظ
  
  پاسخ
  
  درود بر شما
  با چی وارد میکنید؟
  hyperlink؟
  چه خطایی میده
محمد ۱۷ فروردین ۱۴۰۲ / ۱۰:۵۸ ق٫ظ

پاسخ

در مورد لینکهایی که در کاندیشنال فرمتینگها و دیتا ولیدیشن ها درست کردیم باید چکار کنیم ؟ من فایلی دارم که مدام ارور میده و مشخص نیست در کجای از این ۲ ویژگی استفاده کردم . چطور میشه جاهایی که از این دو ویژگی استفاده شده و به یه فایل خارجی ارتباط داده شده رو پیدا ولینک رو اصلاح کرد؟
ممنون وسپاس
- سامان چراغی ۷ اردیبهشت ۱۴۰۲ / ۸:۴۷ ب٫ظ
  
  پاسخ
  
  متوجه نمیشم که چه لینک هایی در این دو ابزار قرار میدید ولی با روش های بالا میتونید تقریبا همه لینک ها رو پیدا کنید.
  - محمد ۲۸ اردیبهشت ۱۴۰۲ / ۹:۰۱ ق٫ظ
    
    پاسخ
    
    دیتایی رو ازفایلهای دیگه کپی کردم و کاندیشنال فرمتینگها و دیتا ولیدیشن های و… اونها به فایل جدید منتقل شده
محمد ۱۷ فروردین ۱۴۰۲ / ۱۰:۱۷ ق٫ظ

پاسخ

مشخص شد که در زمان کپی
در کد خط:
rowNo = ۱ ‘Header row
بجای عدد۱ کلامت سوال ؟
paste میشه درمحیط ماکرو
ممنون از سایتتون و زحماتتون
محمد ۱۷ فروردین ۱۴۰۲ / ۱۰:۱۱ ق٫ظ

پاسخ

سلام. کد ماکروی دوم گویا موقع اجرا ارور میده و ناقص ران میشه!
- سامان چراغی ۷ اردیبهشت ۱۴۰۲ / ۸:۴۱ ب٫ظ
  
  پاسخ
  
  درود
  احتمالا در کپی و پیست کردن کدها از سایت کارکترها بهم ریخته، باید کدها بررسی بشه و ببینیم چه خطایی میگیرید.
اسماعيلي ۲۱ آبان ۱۴۰۱ / ۹:۰۵ ب٫ظ

پاسخ

با سلام و درود
مثل همیشه عالی
محمد ۴ آبان ۱۴۰۱ / ۵:۴۱ ب٫ظ

پاسخ

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

مدیریت لینک ها در اکسل