Как сделать относительную ссылку на другую книгу в Excel?

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

Моя папка имеет следующую структуру:

Folder
|-- prices.xlsx
|-- Fixed Components
|   |-- ComponentsA.xlsx
|   +-- ComponentsB.xlsx
|
+-- Variable Components
    |-- ComponentsC.xlsx
    +-- ComponentsD.xlsx

prices.xlsx является основным листом, который ссылается на другие листы. На моем компьютере он создает ссылки с абсолютным путем, поэтому, когда я копирую файлы, путь остается неизменным для моей машины, а не ссылки на файлы на другом ПК.

Есть ли способ сделать ссылки относительными, чтобы я мог поместить в основной лист что-то вроде ='\Variable Components\[ComponentsC.xlsx]Sheet1'!A1. Я не хотел бы использовать VBA для этого, так как люди не склонны доверять макросам, а потом жалуются, что функции не работают.

Ответы

Ответ 1

Единственные решения, которые я видел для организации внешних файлов в подпапках, потребовали использования VBA для разрешения полного пути к внешнему файлу в формулах. Вот ссылка на сайт с несколькими примерами, которые другие использовали:

http://www.teachexcel.com/excel-help/excel-how-to.php?i=415651

В качестве альтернативы, если вы можете поместить все файлы в одну и ту же папку, а не делить их на подпапки, то Excel разрешит внешние ссылки, не требуя использования VBA, даже если вы переместите файлы в сетевую папку. Затем ваши формулы становятся просто ='[ComponentsC.xlsx]Sheet1'!A1 без переименований папок.

Ответ 2

У меня была аналогичная проблема, которую я решил с помощью следующей последовательности:

  • используйте функцию CELL("filename"), чтобы получить полный путь к текущему листу текущего файла.

  • используйте функцию SEARCH(), чтобы найти начало строки [FileName] SheetName вашего текущего файла excel и листа.

  • используйте функцию LEFT, чтобы извлечь полный путь к каталогу, который содержит ваш текущий файл.

  • Конкатенация имени пути каталога, найденного на шаге №3, с именем файла, именем рабочего листа и ссылкой на ячейку, к которой вы хотите получить доступ.

  • используйте функцию INDIRECT() для доступа к CellPathName, созданного на шаге 4.

Примечание. Эти же шаги можно также использовать для доступа к ячейкам в файлах, имена которых создаются динамически. На шаге 4 используйте текстовую строку, которая динамически создается из содержимого ячеек, текущей даты или времени и т.д. И т.д.

Пример ссылки на ячейку (с каждой частью, собранной отдельно), которая включает в себя все эти шаги:

=INDIRECT("'" & LEFT(CELL("filename"),SEARCH("[MyFileName]MySheetName",CELL("filename")) - 1) & "[" & "OtherFileName" & "]" & "OtherSheetName" & "'!" & "$OtherColumn$OtherRow" & "'")

Обратите внимание, что LibreOffice использует немного отличный синтаксис CellPatnName, как в следующем примере:

=INDIRECT(LEFT(CELL("filename"),SEARCH("[MyFileName]MySheetName",CELL("filename")) - 1) & "OtherFileName" & "'#$" & "OtherSheetName" & "." & "$OtherColumn$OtherRow")

Ответ 3

проще и короче через косвенные: INDIRECT("'..\..\..\..\Supply\SU\SU.ods'#$Data.$A$2:$AC$200")

однако косвенный() имеет недостатки производительности, если количество ссылок в книге -

Я пропускаю конструкцию вроде: ['../Data.ods']#Sheet1.A1 в LibreOffice. Намерение здесь: если я создаю кучу мастер-книг и зависящих от учебников книг в ограниченном поддереве каталогов в исходной файловой системе, я могу закрепить весь поддерево каталога с полным пакетом книг и отправить его другому сотрудничающему лицу по электронной почте или так далее. Он будет сохранен в каком-то другом абсолютном значении в целевой системе, но привязка снова работает в новом абсолютном пути, потому что он был закодирован относительно корня поддерева.

Ответ 4

Использование функции: functionheetname() и = Indirect() и присвоение имен рабочим листам в родительском файле Excel с именем файла Excel с внешней ссылкой. Каждый файл excel с внешней ссылкой был в своих папках с тем же именем. Эти подпапки были предназначены только для большей ясности.

Я сделал следующее: -

| ---- Столбец B --------------- | ---- Столбец C ------------ |

R2) Родительская папка -------- > "C:\TEMP\Excel \"

R3) Имя подпапки --- >= имя файла()

R5) Полный путь -------------- >= "'" & C2 & C3 & "[" & C3 & ". xlsx] Sheet1'! $A $1"

R7) Косвенная функция ----- >= INDIRECT (C5, TRUE)

В основном файле, я сказал, 5 рабочих листов с надписью Ext-1, Ext-2, Ext-3, Ext-4, Ext-5. Скопируйте приведенные выше формулы на все пять листов. В фоновом режиме были открыты все файлы с именем Excel. По какой-то причине результаты не были автоматически вычислены, поэтому пришлось принудительно изменить, отредактировав любую ячейку. Volla, значение в ячейке A1 каждого файла Excel, ссылающегося на внешние ссылки, находилось в основном файле.

Ответ 5

Предположим, что вы подключаетесь к общему диску, например к диску S? Если это так, другие люди могут по-другому отобразить диск. Вероятно, вам нужно использовать "официальное" имя диска//euhkj002/прогнозы/bla bla. Вместо S//в вашей ссылке

Ответ 6

Я пытаюсь сослаться из одного основного Excel на несколько исключений, сохраненных в подпапках по одному и тому же пути. Мне нужно извлечь некоторые данные из этих исключений и использовать в основном Excel.

Я пытаюсь использовать этот код:

Public Function LecturaDatos(celda As String)
Dim pathExcelGran As String
Dim formula As Variant

pathExcelGran = Application.ActiveWorkbook.Path
formula = pathExcelGran & "/" & celda & "/[" & celda & ".xlsx]" & celda & "'!$I$9"

ActiveCell.FormulaR1C1 = "='" & pathExcelGran & "/" & celda & "/[" & celda & ".xlsx]" & celda & "'!$I$9"

End Function

где "celda" - это ячейка в главном Excel, содержащая имя, которое я использую для указания папки и имени Excel.

Он вернул мне # ЗНАЧЕНИЕ! и сообщение; Msgstr "Значение, используемое в формуле ОС неправильного типа данных."

Любая идея?

Заранее спасибо