Как массово сделать гиперссылки в Excel
Привет, корешки. Допустим, у нас есть куча урлов в Excel. И нам надо преобразовать их в ссылки, чтобы прокликать каждую. Вот наша куча урлов:
Нам нужно выделить ячейку например рядом с первым урлом, перейти во вкладку «Формулы» — «Ссылки и массивы» — «ГИПЕРССЫЛКА».
Затем щелкаем на ячейку с первым урлом и жмем ОК.
Ну а дальше нам остается только протянуть ячейки с формулой.
Все, корешки, теперь у нас есть список ссылок.
Массовое изменение адреса гиперссылок excel?
В книге excel было более 5000 гиперссылок на документы.
Путь был примерно такой:
C:\Users\r67\Doc\UPD\
Файлы были перенесены в папки по месяцам, а значит гиперссылки перестали работать. Теперь это будет выглядеть так:
C:\Users\r67\Doc\UPD\2020\1\
C:\Users\r67\Doc\UPD\2020\2\
C:\Users\r67\Doc\UPD\2020\3\ . \12. Как можно массово изменить адрес гиперссылок с учетом того, что все было в 1 папке, а теперь разложены по 12?
- Вопрос задан более трёх лет назад
- 229 просмотров
4 комментария
Простой 4 комментария
А как формула или макрос поймет, какой теперь нужен год и месяц?
Dimonchik @dimonchik2013
Как массово изменить гиперссылки в excel
Всем привет! Как-то на работе столкнулся на работе с интересной задачей. Во время работе Excel выдал ошибку и закрыл окно. После попытки открыть файла оказалось, что все гиперссылки в документе изменили часть своего пути.
- Открываем VBA (Visual Basic for Applications) через Alt+F11
- В окне Project-VBAProject (левой верхнее) правой кнопкой мыши делаем Insert — Module
- В появившемся большом окне вставляем код макроса вида с нашими ссылками (первая ссылка — что нужно поменять, вторая — на что нужно поменять)
Sub Hyper() For i = 1 To ActiveSheet.Hyperlinks.Count ActiveSheet.Hyperlinks(i).Address = Replace(ActiveSheet.Hyperlinks(i).Address, "../../../AppData/Roaming/Microsoft/Excel/", "\Users\User\Desktop\Фирма\Договора\") Next End Sub
4. Закрываем VBA.
5. Через Сервис — Макрос — Макросы ( или по Alt+F8) выбираем макрос и нажимаем «Выполнить».
Пути гиперссылкок будут заменены. Проверено — работает!
Спасибо за помощь http://forum.ixbt.com/topic.cgi?id=23:33826
- Не обновляется содержимое папок в Проводнике Windows
- Как снять защиту с файла Excel
- Excel. Импорт .csv файлов в UTF-8 кодировке
- Как изменить цвет фона и шрифта командной строки Windows
- Notepad++. Как изменить синтаксис по умолчанию
Posted in WINDOWS and tagged excel.
One Comment
Пользователь скопировал к себе с сервера на комп файл, при этом адрес множества гиперссылок приняли вид «..AppDataRoamingMicrosoftExcelВходящие,%20исходящие305293131975136761ЭЛЕКТРОННЫЙ%20АРХИВИсходящие%202016%20гИмя_файла». Текст ячеек имеет вид «ЭЛЕКТРОННЫЙ АРХИВИсходящие 2016 гИмя_файла». Используя макрос, указываю диапазон проблемных ячеек. В поле «Что меняем» вставляю «..AppDataRoamingMicrosoftExcelВходящие,%20исходящие305293131975136761ЭЛЕКТРОННЫЙ%20АРХИВИсходящие%202016%20г», в поле «На что меняем» указываю путь «\имя_сервераимя_сетевой_шарыЭЛЕКТРОННЫЙ АРХИВИсходящие 2016 г», но эффекта 0. Пробовал как диапазон так и 1 ячейку. В этом же файле создаю произвольную гиперссылку \тесттест и макрос её меняет на \что_угодночто_угодно. Буду рад помощи ��
Добавить комментарий Отменить ответ
Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.
Как массово изменить гиперссылки в excel
Доброе время суток!
У меня такая проблема:
Есть два файла, один основной(1), другой второстепенный(2). Оба находятся в одной папке. Каждая ячейка в файле 1 имеет гиперссылку на определенную ячейку в файле 2 и также обратно, для возвращения в основной файл.
В файле 1 гиперссылки не имели полного адреса расположения файла 2 в сетевой папке, что давало возможность копировать папку с обоими файлами, допустим, на флешку, и не ломая гиперссылки. Фактически адрес ссылки выглядел так: «Файл 2.xlsm#’Имя Листа’!Номер ячейки»
Сейчас на сетевом ресурсе что-то произошло, и в гиперссылках появились лишние адреса. В итоге всё начало выглядить так: /../../../Папка/Папка/»Файл 2.xlsm#’Имя Листа’!Номер ячейки». Можно ли при помощи какого-либо макроса удалить часть адреса гиперссылки, ну или заменить на пустоту сразу во всех гиперссылках документа?Их больше тысячи. Хэлп ми.
Пользователь
Сообщений: 3 Регистрация: 19.06.2014
19.06.2014 14:30:53
Ссылки все на одном листе?
Sub FixHyperlinks() Dim wks As Worksheet Dim hl As Hyperlink Dim sOld As String Dim sNew As String Set wks = ActiveSheet sOld = "c:\" sNew = "S:\Network\" For Each hl In wks.Hyperlinks hl.Address = Replace(hl.Address, sOld, sNew) Next hl End Sub
Изменить нужно то, что в кавычках.
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
19.06.2014 14:33:12
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
19.06.2014 14:36:13
нет, ссылки во всей книге. в итоге надо чтобы удалилась чать пути /../../../Папка/Папка/ и осталось только название файла и ячейки.
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
19.06.2014 14:43:57
The_Prist,это мне не помогло, дело в том, что каждая гиперссылка в файле 1 имеет адрес разной ячейки второстепенного файла, поэтому мне необходимо только удалить первую часть адреса
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
19.06.2014 16:57:39
Т.е. попробовать лень? Код принимает в качестве аргумента не полный адрес — а часть. Как раз первую часть ссылок(одинаковую) указываете. Если надо удалить — в качестве заменяемого адреса пусто.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
19.06.2014 17:45:19
The_Prist, не думаю, что я ленивый человек. Пробовал я много чего. К примеру:
Sub test2() On Error Resume Next Dim hl As Hyperlink, s As String, sh As Worksheet s = "../../../../Maintenance/Транспортная%20сеть/Транспортная%20сеть/" ' часть гиперссылки, подлежащая удалению For Each sh In ActiveWorkbook.Worksheets For Each hl In sh.Hyperlinks If hl.Address Like s & "*" Then hl.Address = Replace(hl.Address, s, "" Next Next sh End Sub
комп думает три секунды и. ничего не меняется
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
19.06.2014 17:46:18
Кнопка для форматирования кода в сообщении
Пользователь
Сообщений: 424 Регистрация: 01.03.2014
19.06.2014 17:49:07
Вот таким кодом я вытаскивал весь адрес из гиперссылки. А доработать чтобы остались названия ячейки, не сложно
With ActiveSheet For i = 1 To .Hyperlinks.Count .Hyperlinks(i).Range.Offset(0, 1).Value = .Hyperlinks(i).Address Next i End With
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
19.06.2014 17:49:56
Sub test2() On Error Resume Next Dim hl As Hyperlink, s As String, sh As Worksheet s = "../../../../COMMON/TDMaintenance/Транспортная%20сеть/Транспортная%20сеть/" ' часть гиперссылки, подлежащая удалению For Each sh In ActiveWorkbook.Worksheets For Each hl In sh.Hyperlinks If hl.Address Like s & "*" Then hl.Address = Replace(hl.Address, s, "") Next Next sh End Sub
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
19.06.2014 17:53:10
Sub ЗаменаИспорченныхГиперссылок() On Error Resume Next Dim hl As Hyperlink, oldString As String, newString As String, sh As Worksheet ' часть гиперссылки, подлежащая замене oldString = "../../../../COMMON/TDMaintenance/Транспортная%20сеть/Транспортная%20сеть/" ' на что заменяем newString = "" For Each sh In ActiveWorkbook.Worksheets ' перебираем все листы в активной книге For Each hl In sh.Hyperlinks ' перебираем все гиперссылки на листе If hl.Address Like oldString & "*" Then hl.Address = Replace(hl.Address, oldString, newString) End If Next Next sh End Sub
не хочет работать и все тут. Я уже и в параметрах безопасности макросам все разрешил.
Пользователь
Сообщений: 424 Регистрация: 01.03.2014
19.06.2014 17:56:10
Приложите файл-пример
Пользователь
Сообщений: 3672 Регистрация: 23.12.2012
19.06.2014 18:55:04
Kazakoff , нет файла — нет помощи
Я же просил с вас файл с ОДНОЙ гиперссылкой
неужто пустой файл, содержащий ОДНУ проблемную гиперссылку, может быть секретным?
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
20.06.2014 09:28:56
я прошу прощения.
вот создал пример, там должен быть макрос test2. Может я чего не догоняю, но он не срабатывает.
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
20.06.2014 09:59:37
Попробовал заменить «..\..\..\..\COMMON\TDMaintenance\Транспортная сеть\Транспортная сеть\» на пусто своим кодом — все заменилось, никаких проблем. И код в книге работает.
Одно не пойму — зачем Вы знаки «%20» добавили в адреса для замены? Их ведь нет в гиперссылках изначально. Может поэтому не работает? А Вы все проблемы на нормальные коды валите?
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
20.06.2014 10:06:05
программа сама почему-то заменила все пробелы на «%20»
Пользователь
Сообщений: 14615 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
20.06.2014 10:10:02
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.
Пользователь
Сообщений: 9 Регистрация: 19.06.2014
20.06.2014 10:27:05
The_Prist,большущее Вам спасибо, поставили меня на правильный путь. %20 — это эксель так заменил все пробелы в ссылках. и я тупо копировал путь из ссылки в макрос с процентами, убрал и все получилось. Спасибо, что потратили на меня время!!
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
20.08.2014 20:05:03
Добрый вечер!
А вот в книге с кучей таких формул возможно поменять середину?
=ЕСЛИ((Extract_Value_ADO_Sh("\\udc10\Svodka\Начальник НГДУ\2014\08 июль\Добыча\";$K$3;"Доб.по ДНС";"B14"))="";"";(Extract_Value_ADO_Sh("\\udc10\Svodka\Начальник НГДУ\2014\08 июль\Добыча\";$K$3;"Доб.по ДНС";"B14")))
Например «июль» на «август».
Пробовал вводить код из статьи массовых изменений, но ругается на ячейку «$К$3»
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
20.08.2014 22:13:22
а через обычную замену Ctrl-H по части формулы — не работает?
F1 творит чудеса
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
21.08.2014 08:36:42
работает, но надо каждой ячейке нажимать ENTER, а ячеек слишком дохрена, это никак не вариант.
плюс, когда еще файла не существует, выходит запрос на поиск этого файла, и на этом всё прерывается.
короче, это бред, нужен нормальный код
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
21.08.2014 08:42:36
зачем жать Enter, если есть кнопка «заменить все»?
F1 творит чудеса
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
21.08.2014 08:52:38
как же Вы не понимаете, мне нужно указать диапазон, потому что в некоторых ячейках менять ту же самую часть формулы не надо
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
21.08.2014 09:05:31
как же мне было понять, если вы об этом не говорили?
А чем будет отличаться выбор диапазона на листе от выбора диапазона в макросе? Жесткой заданностью? или критерием каким-то?
Ну, каков файл примера — таково и решение.
F1 творит чудеса
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
21.08.2014 09:30:48
Вот, пожалста.
Мне надо допустим скопировать все августовские ячейки и воткнуть их в сентябрь , но так как все пути, для каждого днса будут разные, разные листы, разные ячейки на листах, то есть заново вручную их прописывать — это бред, то самый лучший вариант — это поменять месяц, а всё остальное будет вязаться от даты.
Прикрепленные файлы
- Копия ТПП.xls (74 КБ)
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
21.08.2014 10:03:46
4vaker, формируйте пути через сцепку текстовых констант с переменной частью. Конкретней не скажу. В примере не показано что есть, а что надо получить. Хотя прочтение правил сэкономило бы Ваше время и время помогающих. ДобРа.
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
21.08.2014 10:44:56
Нужно из столба «Е» сделать столб «Х».
Прикрепленные файлы
- Копия ТПП.xls (59 КБ)
Пользователь
Сообщений: 11833 Регистрация: 17.01.2014
ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64
21.08.2014 11:01:10
"\\usndc10\Svodka\Начальник НГДУ\2014\08 сентябрь\Добыча ТПП\"
"\\usndc10\Svodka\Начальник НГДУ\2014\08 "&строчн(ТЕКСТ(X$3;"ММММ"))&"\Добыча ТПП\"
Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
Пользователь
Сообщений: 4663 Регистрация: 11.06.2014
Microsoft MVP 2018-2022
21.08.2014 11:38:37
вообще-то там код подсвечен красным из-за несоблюдения синтаксиса. Если вам надо изменить строку, которая содержит кавычки, то ее надо тогда прописать так:
sWhatRep = InputBox("Что меняем?", "Ввод данных", """" & "\\usndc10\Svodka\Начальник НГДУ\2014\08 август\Добыча ТПП\" & """" & ";$K$3;" & """Доб.по ДНС""" & ";" & """B14""") sRep = InputBox("На что меняем?", "Ввод данных", """" & "\\usndc10\Svodka\Начальник НГДУ\2014\08 декабрь\Добыча ТПП\" & """" & ";$K$3;" & """Доб.по ДНС""" & ";" & """B14""")
F1 творит чудеса
Пользователь
Сообщений: 26 Регистрация: 04.08.2014
21.08.2014 13:00:39
Кажется я не с того начал. Значит давайте сначала.
Самый простой способ казалось бы ctrl+H у меня НЕ РАБОТАЕТ, поэтому я к вам и обратился.
Допустим я копирую десять ячеек из одного столба в другой, выделяю скопированные ячейки, жму ctrl+h, делаю всё по инструкции, если жать «заменить», то каждую из десяти ячеек он мне предлагает заменить, соответственно необходимо нажимать 10 раз энтер — это фуфло если у меня 200 ячеек, понятно. Если я жму «ЗАМЕНИТЬ ВСЕ», то плевать, что я выделил эти 10 ячеек, он меняет ПОЛНОСТЬЮ ВСЕ ЗНАЧЕНИЯ НА ЛИСТЕ. А мне нужно, чтобы он менял только то, что мне надо. Вот и всё.
Я вам даже больше скажу. Просто заполнив некоторые ячейки словом «август», и прописывая код, указанный в статье по массовой замене, он у меня спрашивает по порядку, что хочу поменять, я ему пишу «август», он спрашивает на что хочу поменять, я пишу «сентябрь», он просит выделить диапазон, я выделяю диапазон со словами «август» и БАМ, ничего не происходит, как был август так и остался.