کشف کتابهای کار وابسته در اکسل

Question

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

اگر کتاب کار A و کتاب کار B دارید و کتاب کار B شامل پیوندی به کتاب کار A است، کتاب کار B به کتاب کار A وابسته است و کتاب کار A مقدمه ای برای کتاب کار B است.

در کتاب کار B به راحتی می توانید پیوندهای استفاده شده در کتاب کار را پیدا کنید. همانطور که بث گفت، او می داند که چگونه این اطلاعات را پیدا کند.

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

برخی از تغییراتی که در کتاب کار A ایجاد می‌کنید ممکن است بر کتاب کار B تأثیری نداشته باشند. برای مثال، باید بتوانید کاربرگ‌ها را اضافه کنید، محدوده‌های نام‌گذاری شده را اضافه کنید و احتمالاً ستون‌ها یا ردیف‌هایی را وارد کنید. در همه این موارد، اکسل ممکن است به طور طبیعی با تغییرات بدون تأثیرگذاری بر کتاب B تنظیم شود. مشکل این است که تا زمانی که کتاب B را باز نکنید، نمی‌دانید که آیا اثر منفی وجود دارد یا خیر. و حتی نمی‌دانید که Workbook B را باز کنید، مگر اینکه از قبل بدانید رابطه بین دو کتاب کار بود.

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

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

Sub DiscoverDependentFiles()
    Dim i As Integer
    Dim iFile As String
    Dim fLink As Variant
    Dim sLink As String
    Dim myFldr As String
    Dim curFile As String

    Change the string here to look
    for a different link / file name
    sLink = "[FileA.xlsm]"
    curFile = ThisWorkbook.Name
    Change the string here to look
    in a different folder
    myFldr = "C:UsersUsermySub"

    Look for both xlsx and xlsm extensions
    iFile = Dir(myFldr & "*.xls?", vbNormal)
    i = 1
    Loop through all of the files in the folder
    Do While iFile <> ""
        If iFile <> curFile Then
            Workbooks.Open Filename:=myFldr & iFile
            Set fLink = Cells.Find(What:=sLink, _
              After:=ActiveCell, LookIn:=xlFormulas, _
              LookAt:=xlPart, SearchOrder:=xlByRows, _
              SearchDirection:=xlNext, MatchCase:= _
              False, SearchFormat:=False)
            If UCase(TypeName(fLink)) <> UCase("Nothing") Then
                Windows(curFile).Activate
                Record names of dependent files
                in your open workbook
                Worksheets(1).Range("D" & (i)).Value = _
                  ActiveWorkbook.Name
                i = i + 1
            End If
            Workbooks(iFile).Close False
        End If
        iFile = Dir
    Loop
End Sub

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