7

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

4 Answers 4

2

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.
2
  • 3
    This doesn't work in OSX. Copying a folder that contains all the files, does not result in the files containing links, having the links relative to the new local directory, they will almost randomly point to the old directory.
    – Joe
    May 7, 2020 at 6:31
  • 1
    This is not working with Excel 2019 on Windows 10. Always a absolute path is stored, even, if all files are in the same folder.
    – testo
    Feb 17, 2021 at 14:01
0

I don't know if it will help you in your situation, but nobody's mentioned yet that there's a setting to tell Excel to use relative vs. absolute paths. And if you really want to use relative paths, unfortunately, Microsoft is going to try to be helpful and will make them relative to that user, so the links will only work if you can get the destinations to be exactly the same relative path for every user. That was someone else's downvoted suggestion. Settings Screenshot for relative URL configuration

-1

So it is possible to have a relative path instead of an absolute path, so your data sources continue to work as long as you keep the data source files in the same location as the worksheet that uses them.

The Excel user interface does not seem to have any way for changing the path though.

But you can edit the XLSX in a different way than with the UI. If you rename your XLSX file to .zip, you can extract it "as-if" it is a zip file (actually the excel workbook is a compressed file). Then in the extracted zip file, open the xl directory, and therein the file "connections.xml"

There you'll find the "sourceFile", you can change this and have it refer to the file name only.

Then, move the "connections.xml" back into the "zipped" excel file, and rename it back to XLSX.

Note just zipping the directory back and renaming it to XLSX won't do, because any zip command may use a variation of compression algorithms and options that won't always be compatible with microsoft excel...

So best is to move the "connections.xml" back into the already existing zip file (or even better, edit the file directly in the zip file, if your zip file handler allows it).

The ExcelFile.xlsx\xl\connections.xml file will look something like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr16" xmlns:xr16="http://schemas.microsoft.com/office/spreadsheetml/2017/revision16"><connection id="1" xr16:uid="{F9606253-9C57-4B65-839A-8DEF5AAEA9F7}" keepAlive="1" name="ThisWorkbookDataModel" description="Data Model" type="5" refreshedVersion="6" minRefreshableVersion="5" background="1"><dbPr connection="Data Model Connection" command="Model" commandType="1"/><olapPr sendLocale="1" rowDrillCount="1000"/><extLst><ext uri="{DE250136-89BD-433C-8126-D09CA5730AF9}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:connection id="" model="1"/></ext></extLst></connection><connection id="2" xr16:uid="{7FE915B8-2095-40EC-B6DC-A589A5A2D08D}" name="TimeTrackingUserEntryLog" type="103" refreshedVersion="6" minRefreshableVersion="5" refreshOnLoad="1" saveData="1"><extLst><ext uri="{DE250136-89BD-433C-8126-D09CA5730AF9}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:connection id="TimeTrackingUserEntryLog" autoDelete="1"><x15:textPr prompt="0" sourceFile="C:\Dropbox\Wonderful\LifeorLive\TimeTrackingUserEntryLog.csv" tab="0" comma="1"><textFields count="5"><textField type="YMD"/><textField/><textField type="text"/><textField type="text"/><textField type="text"/></textFields></x15:textPr><x15:modelTextPr headers="1"/></x15:connection></ext></extLst></connection></connections>

where the C:\Dropbox etc is the path to my datasource which happens to be a csv file.

You can remove the entire path and just leave "datasourcefilename.csv" there.

Hope that helps! ^^

-1

Working solution can be found here:

https://excel.tv/how-to-create-a-relative-file-path-in-power-query/

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .