In Microsoft Excel, you can link a cell in a workbook to another workbook using a formula that references the external workbook. This is called a workbook link. When this workbook link is created, it may use a relative path, which can enable you to move the workbooks without breaking the link. This article discusses how workbook links are stored by Excel under different circumstances and can help when you are trying to fix a broken link.
When Excel opens a destination workbook that contains workbook links, it dynamically combines the portions of the workbook links stored in the workbook with the necessary portions of the current path of the source workbook to create an absolute path.
It is also important to note that what appears in the formula bar is not necessarily what is stored. For example, if the source workbook is closed, you see a full path to the file, although only the file name may be stored.
Workbooks links to source workbooks are created in a relative manner whenever possible. This means that the full path to the source workbook is not recorded, but rather the portion of the path as it relates to the destination workbook. With this method, you can move the workbooks without breaking the links between them. The workbook links remain intact, however, only if the workbooks remain in the same location relative to each other. For example, if the destination workbook is C:\Mydir\Destination.xlsx and the source workbook is C:\Mydir\Files\Source.xlsx, you can move the files to the D drive as long as the source workbook is still located in a subfolder called "Files". Relative links may cause problems if you move the destination workbook to different computers and the source workbook is in a central location.
The way workbook links are storied varies in the following ways:
Storage type 1: Same drive with the same folder or child folder
The source workbook is either in the same folder or a child folder as the destination workbook. In this case, we store the relative file path, for example, subfolder/source.xlsx and destination.xlsx.
This type works best for cloud-based workbooks and when both workbooks are moved.
Storage type 2: Same drive but with different sibling folders
The source and destination workbooks are on the same drive, but in different sibling folders. In this case, we store a server-relative path, for example, /root/parent/sibling1/source.xlsx and /root/parent/sibling2/destination.xlsx.
This type works best if the destination workbook is moved within the same drive, but the source workbook stays in the same location.
Storage type 3: Different drives
The source workbook is on a different drive from the destination workbook. For example, the destination workbook folder is on the C drive and the source workbook folder is on the H drive. In this case, we store the absolute path, for example, H:\folder\source.xlsx or https://tenant.sharepoint.com/teams/site/folder/source.xlsx.
This type works best if the destination workbook is moved, but the source workbook stays in the same location. This assumes that the destination workbook can still access the source workbook.
If the source workbook is located in the XLStart, Alternate Startup File Location, or Library folder, a property is written to indicate one of these folders, and only the file name is stored.
Excel recognizes two default XLStart folders from which to automatically open files on startup. The two folders are as follows:
The XLStart folder that is in the user's profile is the XLStart folder that is stored as a property for the workbook link. If you use the XLStart folder that is in the Office installation folder, that XLStart folder is treated like any other folder on the hard disk. The Office folder name changes between versions of Office. For example, the Office folder name can be, Office14, Office15 or Office16, depending on the version of Office that you are running. This folder name change causes workbook links to be broken if you move to a computer that is running a different version of Excel than the version in which the link was established.
-
The XLStart folder that is in the Office installation folder, such as C:\Program Files\Microsoft Office\<Office folder>\XLStart
-
The XLStart folder that is in the user's profile, such as C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
When a source workbook is linked, the workbook link is established based on the way that the source workbook was opened. If the workbook was opened over a mapped drive, the workbook link is created by using a mapped drive. The workbook link remains that way regardless of how the source workbook is opened in the future. If the source workbook is opened by a UNC path, the workbook link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive workbook links in the same file, and the source workbooks are open at the same time as the destination workbook, only those links that match the way the source workbook was opened will react as hyperlink. Specifically, if you open the source workbook through a mapped drive and change the values in the source workbook, only those links created to the mapped drive will update immediately. Also, the workbook link displayed in Excel may appear differently depending on how the workbook was opened. The workbook link may appear to match either the root UNC share or the root drive letter that was used to open the file.
There are several circumstances in which workbook links between workbooks can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.Scenario 1
-
You map a drive under the root of a share. For example, you map drive Z to \\MyServer\MyShare\MyFolder1.
-
You create workbook links to a source workbook that is stored at the mapped location after you open the destination workbook through that mapped drive.
-
You open the destination workbook by a UNC path.
-
As a consequence, the workbook link will be broken.
If you close the destination workbook without saving it, the workbook links will not be changed. However, if you save the destination workbook before you close it, you will save the workbook links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\MyServer\MyFolder1. In other words, the Share name is eliminated from the file path.Scenario 2
-
You map a drive under the root of a share. For example, you map drive Z to \\MyServer\MyShare\MyFolder1.
-
You open the file by a UNC path, or a mapped drive mapped to a different folder on the share, such as \\MyServer\MyShare\MyFolder2.
-
As a consequence, the workbook link will be broken.
If you close the destination workbook without saving it, the workbook links will not be changed. However, if you save the destination workbook before you close it, you will save the workbook links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\MyServer\MyFolder1. In other words, the Share name is eliminated from the file path.
