تعیین اینکه آیا یک کتاب کار توسط کتاب کار دیگری ارجاع داده شده است در اکسل

Question

ماریوس دارای یک کتاب کار است که ممکن است توسط کتابهای دیگر به آن ارجاع داده شود (به آن پیوند داده شود). او می‌خواهد این کتاب کار را حذف کند، اما اگر به دیگران پیوند داده شده باشد، نمی‌تواند. مشکل این است که او نمی داند چگونه بفهمد که آیا این کتاب کار توسط کتاب های دیگر ارجاع داده شده است یا خیر.

اکسل پیوندهای خروجی را برای یک کتاب کار ردیابی می کند (فقط ویرایش پیوندها را از زبانه Data روبان انتخاب کنید تا ببینید چه پیوندهایی در کتاب کار فعلی وجود دارد)، اما برای اکثر نسخه های برنامه، پیوندهای ورودی را پیگیری نمی کند. تنها نسخه‌هایی که به شما امکان می‌دهند پیوندهای ورودی را ردیابی کنید، برخی از نسخه‌های سازمانی Excel هستند که در این سایت مایکروسافت توضیح داده شده است:

https://support.microsoft.com/en-us/office/see-links-between-workbooks-4e12fb30-afb7-4588-89ce-df3ebb39e744

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

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

Sub FindWbkReferences()
    Dim folderp As String
    Dim fname As String
    Dim sMsg As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cel As Range

    folderp = "C:DocumentsExcel"
    fname = Dir(folderp & "*.xlsx")
    sMsg = ""

    Do While fname <> ""
        Set wb = Workbooks.Open(folderp & fname) Open workbook
        For Each ws In wb.Worksheets    Check each worksheet
             Loop through all cells in the worksheet
            For Each cel In ws.UsedRange.Cells
                 Check if the cell contains a formula referencing
                 the specified file
                If InStr(cel.Formula, "WbkToDelete") > 0 Then
                    Save location of reference
                    sMsg = sMsg & "Workbook: " & "" & wb.Name & ""
                    sMsg = sMsg & " contains reference to this workbook in "
                    sMsg = sMsg & " worksheet: " & ws.Name & ", "
                    sMsg = sMsg & " cell: " & cel.Address & vbCr
                End If
            Next cel
        Next ws
        wb.Save
        wb.Close
        fname = Dir()
    Loop
    If sMsg = "" Then sMsg = "No inbound references located"
    MsgBox sMsg
End Sub

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

این فرآیند بسته به تعداد پوشه‌های موجود، ممکن است کمی طول بکشد. به علاوه، به سرعت آشکار می شود که این یک رویکرد بسیار قوی نیست. مشکل این است که ممکن است لازم باشد کتاب‌های کار را در چندین پوشه بررسی کنید، یا ممکن است پیوندهایی از اشیاء غیر از فرمول‌ها یا حتی از ماکروها وجود داشته باشد. یا ممکن است پیوندهای ورودی از برنامه های دیگر در مجموعه آفیس مانند Word یا PowerPoint وجود داشته باشد.

آیا می توانید ماکرو خود را برای بررسی هر منبع احتمالی پیوند ورودی تنظیم کنید؟ قابل تصور است که می‌توانستید، اما به سرعت به کاری سخت تبدیل می‌شد که باز کردن و بررسی دستی هر منبع احتمالی.

در صورت تمایل می توانید یک روش نیمه دستی را برای جستجوی لینک های ورودی امتحان کنید. بیایید بگوییم که می خواهید پیوندهای MyBook.xlsx را بررسی کنید. اکسل را ببندید و سپس پنجره Windows Explorer را باز کنید. در کادر جستجو (گوشه سمت راست بالا)، نام کتاب کار (MyBook.xlsx) را وارد کنید و اجازه دهید ویندوز جستجوی خود را انجام دهد. از آنجایی که ویندوز درون هر فایل متن شما را جستجو می‌کند، به زودی فهرستی از فایل‌های حاوی "MyBook.xlsx" را خواهید دید.

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