Determining If a Workbook is Referenced by Another Workbook

Marius has a workbook that may be referenced (linked to) by other workbooks. He would like to delete this workbook, but cannot if it is linked to others. The problem is that he doesn't know how to figure out if this workbook is referenced by other workbooks or not.

Excel keeps track of outbound links for a workbook (just choose Edit Links from the Data tab of the ribbon to see what links there are within the current workbook), but for most versions of the program does not keep track of inbound links. The only versions that do allow you to track inbound links are some enterprise versions of Excel, as described at this Microsoft site:

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

For the rest of us non-enterprise users, we are left to our own devices. This means that in order to see if there is a link targeting the current workbook, you actually need to open every other workbook on your system and check its links to see if any of them reference the current workbook.

This process is tedious at best but could best be classified as daunting. This makes it a perfect candidate for a macro. After all, a macro could open a bunch of other workbooks and check for a link to a specific workbook. Here's a simple macro that can check all the workbooks in a specific folder:

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:\Documents\Excel\"
    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

The macro checks every formula in every used cell of every worksheet of every workbook in a given folder. The folder to check is specified in the folderp variable, and you'll note that it also specifies workbooks of a certain type. (In this case, it is XLSX workbooks.)

This process can take some time to accomplish, depending on how many folders there are. Plus, it becomes quickly apparent that this isn't a very robust approach. The problem is that you may need to check workbooks in multiple folders, or there could be links from objects other than formulas or even from macros. Or, there could be inbound links from other programs in the Office suite, such as Word or PowerPoint.

Could you adjust your macro to check for every possible source of an inbound link? Conceivably you could, but it would quickly become a task almost as arduous as opening and checking each potential source manually.

You could, if you desire, try a semi-manual way to look for inbound links. Let's say that you want to check for links to MyBook.xlsx. Close Excel and then open a Windows Explorer window. In the Search box (upper-right corner), enter the workbook name (MyBook.xlsx) and let Windows do its searching. Since Windows searches within each file for your text, you should soon see a list of any files that contain "MyBook.xlsx".

Perhaps the best approach, however, is to simply rename the workbook you are thinking of deleting. This would break any inbound links because the target of the link is no longer existent—you've renamed it. You can then continue to use your system as normal over the next few weeks or few months. If you get no notifications of broken links, then you can safely delete the workbook. A variation on this is to move the workbook instead of renaming it. Simply move it to an external device, such as a flash drive, and then go about your work. If you discover down the road you still need it, then you can always move it back, as needs dictate.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7639) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.