Sign in Subscribe

Excel changing link paths when opened from UNC vs Mapped Drive

Tim Wappat

• 5 min read

Behaviour of links in excel is described in this good document.

Description of link management and storage in Excel

Looking at an excel cell when document opened via UNC path vs Looking at excel cell when document opened via mapped drive path:

=+VLOOKUP(C10,'\\domain.net\folds\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)
=+VLOOKUP(C10,'N:\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)

The actual UNC path should be as below, the “files” folder has been left out by Excel, thus the links are broken when the Excel file is opened via UNC vs Mapped Drive.

\\domain.net\files\folds\USERS\design\bill\

For preservation of content, here is the relevant parts, but check the link for latest version:

How link paths are handled when a file opens

When Excel opens a file that contains links (linked workbook), it combines the portions of the links stored in the file with the necessary portions of the current path of the linked workbook.

How link paths are stored

When Excel stores the path to a linked file, it uses the following rules to determine what to store.
Note Moving up a path indicates that you are referring to folders moving away from the root drive or share. Moving down a path indicates that you are moving closer to the root drive or share.

  • If the linked file and the source data file are not on the same drive, the drive letter is stored with a path to the file and file name.

  • If the linked file and the source data file are in the same folder, only the file name is stored.

  • If the source data file is located in a folder that is nested in the same root folder as the linked file, a property is stored to indicate the root folder. All portions of the path that are shared are not stored.
    For example, if the linked file C:\Mydir\Linked.xls is dependent on C:\Mydir\Files\Source.xls, the only portion of the path that is stored is \Files\Source.xls.

  • If the source data file is one folder down from the linked file, a property is stored to indicate this.
    For example, the linked file is C:\Mydir\Files\Myfile\Linked.xls and the source data file is C:\Mydir\Files\Source.xls. Excel stores only \MyDir\Files\ .. \Source.xls.
    Note This allows a link to be maintained when the linked file is copied to an additional sub folder of the folder that the source file is located in.
    For example, the linked file is C:\Mydir\Files\Myfiles1\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, the linked file, Linked.xls, is copied from the folder C:\Mydir\Files\Myfiles1 to a folder named C:\Mydir\Files\Myfiles2, and the link to C:\Mydir\Files\Source.xls is maintained.

  • If the source data file is located in the XLStart, Alternate Startup File Location, or the Library folder, a property is written to indicate one of these folders, and only the file name is stored.
    Note 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 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

    The XLStart folder that is in the user's profile is the XLStart folder that will be stored as a property for the 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 is Office, Office10, Office11 or Office12, depending on the version of Office that you are running. This folder name change causes 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.

It is also important to note that what appears in the formula bar is not necessarily what is stored. For example, if the source data file is closed, you see a full path to the file, although only the file name may be stored.

Links to external workbooks are created in a relative manner whenever possible. This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook. With this method, you can move the workbooks without breaking the links between them. The links remain intact, however, only if the workbooks remain in the same location relative to each other. For example, if the linked file is C:\Mydir\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, you can move the files to the D drive as long as the source file is still located in a subfolder called "files".
Relative links may cause problems if you move the linked file to different computers and the source is in a central location.

Mapped drives vs. UNC

When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.
The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.

Scenarios that may cause links to not work as expected

There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.
Scenario 1:

  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
  3. You open the file by a UNC path.
  4. As a consequence the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the 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 \\Server\Folder1. In other words, the Share name is eliminated from the path.
Scenario 2:

  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.
  3. As a consequence, the link will be broken.

If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the 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 \\Server\Folder1.