☰

Смещение Excel из двух разных книг, создание копии одной, не затрагивая другую

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
Смещение Excel из двух разных книг, создание копии одной, не затрагивая другую

Я использую две разные рабочие книги: первая [только данные Excel] создана как база данных, а вторая [шаблон счета-фактуры] создана для создания счета-фактуры.

При создании счета я использую смещение, например, для чтения списка местоположений клиентов.

=OFFSET('[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

И это работает правильно.

Проблема в том, что когда я хочу создать копию рабочей книги [Шаблон счета-фактуры], которая будет иметь другой путь, например, на рабочем столе, смещение меняется на

=OFFSET('C:\Users\anthony\Desktop\[Excel Data Only.xlsx]Channels'!$A$2,MATCH(Invoice!$C$1,'C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,0)-1,1,COUNTIF('C:\Users\anthony.boulos\Desktop\[Excel Data Only.xlsx]Channels'!$A$2:$A$198,Invoice!$C$1),1)

Таким образом, путь к рабочей книге [Только данные Excel] автоматически изменился и больше недоступен, поскольку я просто изменил путь к [Шаблону счета-фактуры], а не к другому.

Как мне управлять этой формулой, чтобы она не влияла на путь [Только данные Excel] при изменении пути [Шаблона счета-фактуры]?

Спасибо за помощь!

решение1

Когда вы открываете файл Excel с внешними ссылками (и позволяете ему выполнять обновления, когда вы получаете предупреждение безопасности), Excel, похоже, выполняет некий автоматизм для разрешения ссылок. Похоже, он оптимизирован для случаев, когда вы перемещаете и источник, и цель, особенно для изменения букв дисков (может быть, пережиток времен дискет?) .

К сожалению, это может привести к сбоям в работе, если вы перемещаете только целевой файл ( Invoice Template.xlsxв вашем примере).

Вероятно, самый быстрый способ исправить ситуацию — через edit linksдиалог, который вы можете найти в Data-Ribbon. Там у вас есть таблица всех внешних файлов, и вы можете изменить, куда они указывают ( change source). Когда вы исправите путь Excel Data Only.xlsxтуда, это должно обновить все ссылки на этот файл в вашей таблице одновременно.

решение2

Если вы столкнулись с проблемой на огромном массиве файлов, которые нужно обновить в пакетном режиме (или вы просто хотите приключений, и не забудьте сначала сделать резервную копию!), вы можете попробовать отредактировать необработанный файл *.xlsx. На самом деле это zip-архив.

Если вы откроете его с помощью проводника архивов (7-молниянапример), вы найдете внешние ссылки в подкаталоге \xl\externalLinks\_rels\. Вероятно, есть файл с именем externalLink1.xml.rels(номер может отличаться). Это XML-файл, содержащий (относительный) путь к файлу и имя. Вы можете обновить его там. Он будет напечатан как.../externalLinkPathпометка его как относительного расположения вашего файла. Изменение этого типа на http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissingтакже может решить проблему.

Ваш externalLink1.xml.rels может выглядеть так. Я не тестировал этот путь специально :

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
      <Relationship 
          Id="rId1" 
          Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing"
          Target="'C:\Users\anthony\Desktop\channels%20(BE%20VERY%20VERY%20PRECAUTIOUS%20WITH%20THIS%20FILE)!!!!\0000%20TEMPLATES\INVOICE%20template\New%20template\Excel%20Data%20Only.xlsx "
          TargetMode="External"
      />
 </Relationships>

PS: Я бы не рекомендовал использовать специальные символы (скобки, восклицательные знаки и т. д.) в файлах Excel или путях, так как это также может вызвать проблемы.

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