Microsoft Excel – How to Use Relative Paths in External Workbook Links

dropboxmicrosoft excelonedrive

Within our company we use a number of Excel workbooks that link to other Excel workbooks.

With our service this is fine as everyone accesses a shared network drive and the file paths for all the linked files remain the same.

We now however wish to use Cloud Storage services e.g. Dropbox but have run into issues. With all of the services we have tested the file path for files is different for every machine.For example the same folder on two different machines would have file locations as below:

Machine 1 Dropbox = C:[machine 1]\Dropbox\Excel Folder\Excel File

Machine 2 Dropbox = C:[machine 2]\Dropbox\Excel Folder\Excel File

When this happens you have to choose the source for the links every single time you open the file from a different machine. If you create a file with links to other files on machine 1 and then move to machine 2, machine 2 no knows where the files with 'machine 1' in the path as stored. This is a huge barrier for us using this kind of storage as every user would have to change each file each time they open it.

It becomes particularly acute when using PowerPivot and linking to other files in the data model as all of the data model data sources have to be updated also.

I understand why this happens, but is there a way to get around this and have files link together in such a way that the links are relative rather than absolute.

We are using a mix of Office 2013 and 2016

Best Answer

In certain circumstances, external links created in Excel will use relative paths rather than absolute paths. This depends on the locations of the spreadsheets. The easiest way is to store the Excel documents in the same folder.

See this URL for more information (excerpt below): https://support.microsoft.com/en-us/kb/328440

  • 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.
Related Question