Как заменить путь к файлу в эксель

Обновлено: 07.07.2024

В Microsoft Excel вы можете связать ячейку книги с другой книгой, используя формулу, которая ссылается на внешнюю книгу. При создании этой ссылки может использоваться относительный путь. С помощью относительных ссылок можно перемещать книги, не нарушая связь. В этой статье рассказывается о том, как с помощью Excel хранить ссылки на связанные книги в разных обстоятельствах.

Дополнительная информация

Как обрабатываются пути ссылок при открытии файла

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

Как сохраняются пути ссылок

Если путь к связанному файлу сохраняется в Excel, для определения того, что нужно сохранить, используются следующие правила:Примечание. Перемещение вверх по пути указывает на то, что вы ссылаетесь на папки, расположенные вне корневого диска или общего доступа. Если переместиться вниз по пути, это означает, что вы переходите ближе к корневому диску или к общему доступу.

Если связанный файл и исходный файл данных находятся на разных дисках, буква диска сохраняется с путем к файлу и имени файла.

Если связанный файл и исходный файл данных находятся в одной папке, сохраняется только имя файла.

Если файл исходных данных находится в папке, вложенной в ту же корневую папку, что и связанный файл, свойство хранится для указания корневой папки. Все части пути, к которым предоставлен общий доступ, не сохраняются. Например, если связанный файл 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, связанный файл с именем Link. xls копируется из папки C:\Mydir\Files\Myfiles1 в папку C:\Mydir\Files\Myfiles2, а ссылка на C:\Mydir\Files\Source.xls сохраняется.

Если исходный файл данных находится в папке XLStart, в разделе " Каталог автозагрузки" или " Библиотека ", свойству записывается свойство, которое указывает на одну из этих папок, и сохраняется только имя файла.Примечание. В Excel распознаются две стандартные папки XLStart, из которых автоматически открываются файлы при запуске. Ниже указаны две папки.

Папка XLStart в папке установки Office, например C:\Program Files\Microsoft Office \Office\XLStart

Папка XLStart в профиле пользователя (например, C:\Documents and Settings \имя_пользователя\Application Data\Microsoft\Excel\XLStart

Папка XLStart в профиле пользователя — это Папка XLStart, которая будет храниться в качестве свойства ссылки. Если вы используете папку XLStart, которая находится в папке установки Office, эта папка XLStart будет обрабатываться так же, как и любая другая папка на жестком диске. Имя папки Office меняется между версиями Office. Например, имя папки Office — Office, Office10, Office11 или Office12 в зависимости от используемой версии Office. Изменение имени папки приводит к разрыву связей при переходе на компьютер, на котором установлена другая версия Excel, чем версия, в которой была установлена ссылка.

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

Относительные и абсолютные ссылки

Ссылки на внешние книги создаются в определенном порядке, если это возможно. Это означает, что полный путь к исходному файлу данных не записывается, а только часть пути, связанная с связанной книгой. С помощью этого метода вы можете перемещать книги, не нарушая связи между ними. Однако ссылки остаются неизменными, только если книги остаются в одном месте относительно друг друга. Например, если связанный файл — C:\Mydir\Linked.xls, а файл источника данных — C:\Mydir\Files\Source.xls, вы можете переместить файлы на диск D, пока исходный файл по-прежнему находится во вложенной папке "файлы". Относительные ссылки могут приводить к возникновению проблем при перемещении связанного файла на другой компьютер, когда источник находится в одном месте.

Сопоставленные диски и UNC-имя

При связывании книги с исходными данными связь устанавливается на основе способа открытия книги. Если книга была открыта на подключенном диске, она будет создана с помощью подключенного диска. Ссылка останется в том случае, если книга с исходными данными будет открыта в будущем. Если файл исходного файла данных открыт с помощью пути в формате UNC, ссылка не будет возвращена на подключенный диск, даже если соответствующий диск доступен. Если в одном и том же файле есть ссылки UNC и сопоставленные диски, а исходные файлы открыты одновременно с конечным файлом, только те ссылки, которые соответствуют тому, как был открыт этот файл, будут реагировать на гиперссылку. В частности, если вы откроете файл на подключенном диске и измените значения в исходном файле, только ссылки, созданные для подключенного диска, будут немедленно обновлены. Ссылка, отображаемая в Excel, может выглядеть по-разному в зависимости от того, как была открыта книга. Возможно, эта ссылка соответствует корневому общему ресурсу UNC или букве корневого диска, которая использовалась для открытия файла.

Сценарии, которые могут привести к неправильной работе ссылок

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

Вы можете сопоставить диск с корневым каталогом общего доступа. Например, вы можете подключить диск Z к \ \сервер\поделиться\Folder1.

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

Вы открываете файл по UNC-пути.

Вследствие этого ссылка будет разорвана.

Если вы закрыли файл, не сохраняя его, ссылки не будут изменены. Тем не менее, если сохранить файл перед его закрытием, вы сохранит ссылки с текущим разорванным расположением. Папки между корнем общего доступа и сопоставленной папкой будут оставлены вне пути. В приведенном выше примере ссылка изменится на \ \сервер\Folder1. Другими словами, имя общего доступа исключается из пути. Сценарий 2.

Вы открываете файл по UNC-пути или сопоставленному диску, подключенному к другой папке в общем доступе, например \ \сервер\поделиться\Folder2.

Как следствие, ссылка будет разорвана.

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

Существуют ситуации, когда на листе есть много гиперссылок(если еще на знакомы с гиперссылками - Что такое гиперссылка?) на различные папки или интернет ресурсы. И иногда случаются ситуации когда адреса этих гиперссылок надо поменять. Как правило это происходит если либо домен сменился, либо на сервере добавилась директория и эти изменения надо отразить в гиперссылках, либо все просто было перемещено в другую папку. Для примера возьмем такие исходные данные: надо заменить текст ссылки .excel_vba на текст excel-vba .
Прежде чем начать замену необходимо еще определить каким способом установлена гиперссылка. Если установлена через формулу ГИПЕРССЫЛКА, то все просто:

выделяем диапазон с гиперссылками;
жмем Ctrl+H.
- Найти: .excel_vba
- Заменить на: excel-vba
- Жмем кнопочку "Параметры" и устанавливаем Область поиска - Формулы и снимаем галочку "Ячейка целиком"
Жмем "Заменить все"

Теперь адреса ссылок должны поменяться.
Все гораздо сложнее, если гиперссылки были созданы через стандартное меню: правый клик мыши на ячейке - Гиперссылка. Тут фокус с заменой через Ctrl+H не пройдет. В таких случаях придется прибегнуть к помощи VBA(Visual Basic for Applications) или как чаще называют эти коды - макросы. Текст такого макроса:

Sub Replace_Hyperlink() Dim rCell As Range, rRange As Range, sWhatRep As String, sRep As String On Error Resume Next Set rRange = Application.InputBox("Укажите диапазон для замены", "Выбор данных", Type:=8) If rRange Is Nothing Then Exit Sub sWhatRep = InputBox("Что меняем?", "Ввод данных", ".excel_vba") sRep = InputBox("На что меняем?", "Ввод данных", "excel-vba") If sWhatRep = "" Then Exit Sub If sRep = "" Then If MsgBox("Хотите заменить " & sWhatRep & " на пусто?", vbCritical + vbYesNo, "Предупреждение") = vbNo Then Exit Sub End If Application.ScreenUpdating = 0 For Each rCell In rRange If rCell.Hyperlinks.Count > 0 Then If rCell.Hyperlinks(1).Address = rCell.Value Then rCell = Replace(rCell.Value, sWhatRep, sRep) End If If rCell.Hyperlinks(1).Address <> "" Then rCell.Hyperlinks(1).Address = Replace(rCell.Hyperlinks(1).Address, sWhatRep, sRep) End If If rCell.Hyperlinks(1).SubAddress <> "" Then rCell.Hyperlinks(1).SubAddress = Replace(rCell.Hyperlinks(1).SubAddress, sWhatRep, sRep) End If End If Next rCell Application.ScreenUpdating = 1 End Sub

Как все это использовать:

создаем стандартный модуль и помещаем в него код макроса выше
жмем Alt+F11 и выбираем макрос Replace_Hyperlink (или создаем кнопку для вызова макроса на листе)
в первом диалоговом окне указываем в каком диапазоне надо найти гиперссылки и заменить в них адрес
во втором диалоговом окне указываем какой текст заменить
в третьем диалоговом окне указываем на что заменить указанный в первом окне текст

Примерно так же можно заменить гиперссылки в объектах на листе(например, картинках и кнопках):

Данные код работает почти так же как и предыдущий:

создаем стандартный модуль и помещаем в него код макроса выше
жмем Alt+F11 и выбираем макрос Replace_Hyperlink_inShape (или создаем кнопку для вызова макроса на листе)
в первом диалоговом окне указываем какой текст заменить
во втором диалоговом окне на что заменить указанный в первом окне текст

Гиперссылки всех объектов на листе будут изменены. Если у объекта нет гиперссылки - объект будет пропущен.

Чтобы заменить гиперссылки только в выделенных объектах необходимо строку
For Each oSh In ActiveSheet.Shapes
заменить на такую:
For Each oSh In Selection.ShapeRange
тогда надо будет выделить объекты на листе, для которых необходимо заменить гиперссылки, и запустить макрос.

Пример замены гиперссылок.xls (58,0 KiB, 9 659 скачиваний)

Команда поможет вставить в ячейки имена всех выбранных файлов или одного конкретного, всех файлов в указанной папке и её подпапках, а так же создать список файлов и папок в виде активных гиперссылок. Плюс к этому команда может отбирать файлы на основании указанной маски(шаблона), что позволит отбирать либо файлы определенного типа, либо с определенными именами.
Для чего это может быть нужно? При использовании надстройки некоторые команды требуют указания полного пути к файлу или имен файлов(например команды: Пакетное переименование файлов, Отправка листа/книги по почте, Создание примечаний). С помощью команды Путь к файлу это можно сделать быстро и главное правильно.
Т.к. команда умеет создавать гиперссылки на файлы и папки, то можно создавать списки нужных файлов и папок для быстрого доступа к ним из одного листа.

Просматривать файлы:

только выбранные - после нажатия кнопки ОК появится диалоговое окно, в котором необходимо выбрать файлы для создания списка
все в папке - после нажатия кнопки ОК появится диалоговое окно выбора папки. Выбирается папка, файлы в которой необходимо просмотреть и создать список
включая подпапки - доступно для пункта все в папке. Если установлен, то файлы будут просматриваться не только в указанной папке, но и во всех вложенных в неё папках до самой глубоко вложенной. Если отключен, то файлы будут просматриваться исключительно в указанной папке.

Отбирать файлы

все - будет создан список со всеми файлами без исключений
на основании маски - в данном случае в поле указывается шаблон и будут отбираться файлы, отвечающие маске шаблона. Например, если необходимо отбирать только файлы Excel, то можно указать маску: *.xls* . Если надо отбирать только файлы Word, содержащие в имени слово "инструкция", то маску можно задать как: *инструкция*.doc* .

Записать

Записать как гиперссылки - независимо от того, какой вариант записи выбран(пути к файлам или папкам), в ячейках можно создать гиперссылки на исходные файлы/папки. По нажатии на такую гиперссылку будет открыт исходный файл или папка. Файлы в этом случае открываются в программе, установленной по умолчанию для просмотра данного типа файлов. Например, файлы .doc будут открываться в Word, файлы .xls в Excel.

При открытии файла Excel с такими формулами на другом компьютере, Excel начинает отображает формулы с указанием пути к файлу надстройки, в таком виде:

Кроме того, Excel может отобразить предупреждение о том, что автоматическое обновление ссылок отключено:

Разумеется, формулы после этого не работают, так как ссылаются на отсутствующий по данному пути файл.

Способ 1 применяется при использовании файла с формулами с разных компов.

Расположите файл надстройки FillDocuments.xla на всех компьютерах по одному пути (и убедитесь, что имя файла надстройки везде одинаково)
Например, можно расположить файл в корне диска С, чтобы путь к файлу был C:\FillDocuments.xla
Или можно разместить в общей сетевой папке, по пути типа \\server\надстройки\FillDocuments.xla

ВАЖНО: для перемещения файла надстройки, нужно выполнить следующее:

отключить автозапуск программы в О ПРОГРАММЕ - ОПЦИИ
закрыть Excel (или закрыть надстройку FillDocuments)
переместить файл в новое расположение
запустить надстройку из нового места
включить автозапуск программы в О ПРОГРАММЕ - ОПЦИИ (если это нужно)

После этого, выполните описанное в одном из других способов, для восстановления формул.

Способ 2: вручную исправляем ссылки, через поиск и замену

Нажимаем Ctrl + H для вывода диалогового окна «Найти и заменить», в поле «Найти» вставляем скопированный путь,
а поле «Заменить на» оставляем пустым.
Осталось нажать кнопку Заменить все, и формулы приведутся к исходному варианту написания: =Пропись_Число(D12)

Способ 3: исправляем ссылки через меню надстройки FillDocuments

Описанное в способе 2 может сделать и сама надстройка.
Для этого есть специальный пункт в подменю «Дополнительно», но сначала это подменю надо включить.

Нажимаем кнопку Настройки, идём на вкладку «Дополнительно», и включаем там опцию «Отображать подменю ДОПОЛНИТЕЛЬНО на панели инструментов программы»:

После этого, на панели инструментов появится новое подменю Дополнительно, в котором нам нужно выбрать пункт Восстановить формулы:

Способ 4: исправляем ссылки на файл штатными средствами Excel

Нажмём кнопку Включить содержимое на желтом предупреждении системы безопасности Excel, появляющемся при открытии файла.
При это появится диалоговое окно, где нужно нажать кнопку Изменить связи.

Нажимаем кнопку Изменить, и в диалоговом окне указываем, где теперь расположен файл надстройки FillDocuments.xla

После того, как файл выбран, в графе Состояние будет написано ОК:

Теперь отстаётся запустить надстройку FillDocuments (если она до этого не была запущена), и Excel автоматически внесёт изменения в формулы во всём файле.

Читайте также: