☰

Как заставить Excel использовать относительные пути во внешних ссылках на книги

tag-icon • tag-icon microsoft-excel dropbox onedrive
Как заставить Excel использовать относительные пути во внешних ссылках на книги

В нашей компании мы используем ряд рабочих книг Excel, которые связаны с другими рабочими книгами Excel.

С нашим сервисом все в порядке, поскольку все имеют доступ к общему сетевому диску, а пути ко всем связанным файлам остаются прежними.

Однако теперь мы хотим использовать службы облачного хранения, например Dropbox, но столкнулись с проблемами. Во всех протестированных нами службах путь к файлам отличается для каждой машины. Например, одна и та же папка на двух разных машинах будет иметь расположение файлов, как показано ниже:

Машина 1 Dropbox = C:[машина 1]\Dropbox\Папка Excel\Файл Excel

Машина 2 Dropbox = C:[машина 2]\Dropbox\Папка Excel\Файл Excel

Когда это происходит, вам приходится выбирать источник ссылок каждый раз, когда вы открываете файл с другой машины. Если вы создаете файл со ссылками на другие файлы на машине 1, а затем переходите на машину 2, машина 2 не знает, где хранятся файлы с «машиной 1» в пути. Это огромное препятствие для нас, использующих этот тип хранилища, поскольку каждому пользователю пришлось бы изменять каждый файл каждый раз, когда он его открывает.

Это становится особенно актуально при использовании PowerPivot и связывании с другими файлами в модели данных, поскольку все источники данных модели данных также должны быть обновлены.

Я понимаю, почему это происходит, но есть ли способ обойти это и связать файлы таким образом, чтобы ссылки были относительными, а не абсолютными?

Мы используем сочетание Office 2013 и 2016.

решение1

В определенных обстоятельствах внешние ссылки, созданные в Excel, будут использовать относительные пути, а не абсолютные. Это зависит от расположения электронных таблиц. Самый простой способ — хранить документы Excel в одной папке.

Более подробную информацию см. по этому URL-адресу (фрагмент ниже):https://support.microsoft.com/en-us/kb/328440

  • Если связанный файл и исходный файл данных находятся не на одном диске, буква диска сохраняется вместе с путем к файлу и именем файла.
  • Если связанный файл и исходный файл данных находятся в одной папке, сохраняется только имя файла.
  • Если исходный файл данных находится в папке, которая вложена в ту же корневую папку, что и связанный файл, сохраняется свойство, указывающее корневую папку. Все части пути, которые являются общими, не сохраняются. Например, если связанный файл C:\Mydir\Linked.xls зависит от C:\Mydir\Files\Source.xls, сохраняется только часть пути \Files\Source.xls.
  • Если исходный файл данных находится на одну папку ниже связанного файла, сохраняется свойство, указывающее на это. Например, связанный файл — C:\Mydir\Files\Myfile\Linked.xls, а исходный файл данных — C:\Mydir\Files\Source.xls. Excel сохраняет только \MyDir\Files\ .. \Source.xls. Примечание. Это позволяет сохранить ссылку, когда связанный файл копируется в дополнительную подпапку папки, в которой находится исходный файл. Например, связанный файл — C:\Mydir\Files\Myfiles1\Linked.xls, а исходный файл данных — C:\Mydir\Files\Source.xls, связанный файл Linked.xls копируется из папки C:\Mydir\Files\Myfiles1 в папку с именем C:\Mydir\Files\Myfiles2, а ссылка на C:\Mydir\Files\Source.xls сохраняется.

решение2

Не знаю, поможет ли это вам в вашей ситуации, но никто пока не упомянул, что есть настройка, которая позволяет Excel использовать относительные пути, а не абсолютные. И если вы действительно хотите использовать относительные пути, к сожалению, Microsoft постарается помочь и сделает их относительными для этого пользователя, поэтому ссылки будут работать только в том случае, если вы сможете сделать так, чтобы назначения были точно такими же относительными путями для каждого пользователя. Это было чье-то предложение, за которое проголосовали против.Скриншот настроек для конфигурации относительного URL

решение3

Таким образом, можно указать относительный путь вместо абсолютного, и ваши источники данных продолжат работать до тех пор, пока вы храните файлы источников данных в том же месте, что и рабочий лист, который их использует.

Однако пользовательский интерфейс Excel, похоже, не позволяет изменить путь.

Но вы можете редактировать XLSX другим способом, чем с помощью пользовательского интерфейса. Если вы переименуете ваш файл XLSX в .zip, вы сможете извлечь его "как будто" это zip-файл (на самом деле рабочая книга Excel - это сжатый файл). Затем в извлеченном zip-файле откройте каталог xl, а в нем файл "connections.xml"

Там вы найдете «sourceFile», вы можете изменить его и сделать так, чтобы он ссылался только на имя файла.

Затем переместите «connections.xml» обратно в «сжатый» файл Excel и переименуйте его обратно в XLSX.

Обратите внимание, что простое архивирование каталога и переименование его в XLSX не сработает, поскольку любая команда zip может использовать вариант алгоритма сжатия и параметров, которые не всегда совместимы с Microsoft Excel...

Поэтому лучше всего переместить файл «connections.xml» обратно в уже существующий zip-файл (или, что еще лучше, отредактировать файл непосредственно в zip-файле, если ваш обработчик zip-файлов позволяет это).

Файл ExcelFile.xlsx\xl\connections.xml будет выглядеть примерно так:

<?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>

где C:\Dropbox и т. д. — это путь к моему источнику данных, который является CSV-файлом.

Вы можете удалить весь путь и оставить только «datasourcefilename.csv».

Надеюсь, это поможет! ^^

решение4

Рабочее решение можно найти здесь:

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

Связанный контент