PDA

Просмотр полной версии : Автозамена гиперссылок в Excel?



e-aleks
22.12.2004, 11:09
Товарисчи кто сталкивался с подобной проблемой и/или знает как ее решить.

Есть некий реестр в Excel содержащий гиперссылки на другие файлы (на сам файл, т.е. кликаешь по ссылке и тебе открывается этот файл).

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

Кто знает как решить эту проблему?

Заранее благодарен.

Kostik
22.12.2004, 16:37
у меня тоже есть такая проблема! как ее решить не знаю!
единственно до чего додумался это копировать такие ячейки, бросать в блокнот, а потом бросать тот же список обратно в эксель, но в тот же столбик нельзя, гиперссылки снова будут, а в другой столбик. Метод подходит, если данных не много и они все в одном столбике, но в идеале эту проблему надо решать по другому.

Игорь Акопян
22.12.2004, 17:33
Стартуем запись макроса, кликаем на гиперссылке правой кнопкой мыши, выполняем пункт "Гиперссылка/Изменить гиперссылку..." - в окне меняем значение на нужное, жмём ОК. Останавливаем запись макроса. Открываем VB - Alt-F11, открываем наш модуль и видим макрос вида:

Selection.Hyperlinks(1).Address = "c:\readme.txt"

Далее адаптируем к нашим требованиям... Если непонятно, попозже напишу подробнее

Игорь Акопян
22.12.2004, 17:39
Во! Даже лучше: открываем хелп по Hyperlinks и видим в примере то, что надо:


For Each h in Worksheets(1).Hyperlinks
If Instr(h.Name, "Microsoft") <> 0 Then h.Follow
Next

только в цикле пишем нужные преобразования

Naeel Maqsudov
22.12.2004, 17:49
Да, пакетная правка ссылок станартными функциями интерфейса не предусмотрена. Прийдется писать макрос.



Sub qwe()
Dim lnk As Hyperlink
For Each lnk In ActiveSheet.Hyperlinks
MsgBox lnk.Address
Next
End Sub


Этот макрос пролистывает все гиперссылки на текущем листе. Соответственно надо
lnk.Address = новый_адрес
где новый адрес вычисляется путем выкусывания и вставки части строки. Это уж сами напете, я надеюсь.

Kostik
22.12.2004, 17:52
Стартуем запись макроса...

макросы это ай-яй-яй! :)
а попроще нельзя ли?

Naeel Maqsudov
22.12.2004, 18:19
Нет

e-aleks
22.12.2004, 18:36
lnk.Address = новый_адрес
где новый адрес вычисляется путем выкусывания и вставки части строки. Это уж сами напете, я надеюсь.
Спасибо большое, только я вот с VBA дело на имел, только в свои времена с Qbasic.
Т.е., могли вы бы объяснить подробнее:
1. Лучше всего как эту ссылку вытянуть в столбец рядом, там бы я ее поменял на функцию "гиперссылка" и в дальнейшем не мучался.
или
2. Как изменить путь
путь был вида \\папка 1\папка2\n_file.xls
а нужно изменить на \\папка 1\папка2\подпапка_n\n_file.xls, где n куча разных имен.
В жизни это выглядит так, была сетевая директорий в которой скопом хранились документы вида "название контрагента дата документа".
потом в этой директории создали директории с именем контрагента, и туда переместили все файлы относящиеся к данному контрагенту.
Фу надеюсь понятно объяснил.
Если нет, пойду искать пособие по VBA:)

e-aleks
23.12.2004, 09:24
Стартуем запись макроса, кликаем на гиперссылке правой кнопкой мыши, выполняем пункт "Гиперссылка/Изменить гиперссылку..." - в окне меняем значение на нужное, жмём ОК. Останавливаем запись макроса. Открываем VB - Alt-F11, открываем наш модуль и видим макрос вида:

Selection.Hyperlinks(1).Address = "c:\readme.txt"

Далее адаптируем к нашим требованиям... Если непонятно, попозже напишу подробнее
Непонятно, как взять две части старого имени, и между ними вставить нужный кусок пути.
Т.е.
1. Оператор который содержит текущее имя гиперссылки какой?
2. Дальше, что то вроде substr()?
3. Как все это вместе сцеплять, через &?

Спасибо за помошь.

Naeel Maqsudov
23.12.2004, 11:09
Спасибо большое, только я вот с VBA дело на имел, только в свои времена с Qbasic.

Отлично! Используем функции Mid$, Left$, Right$, Len, InStr, ну и сцепление строк, разумеется ("йцук" & "енг")


1. Лучше всего как эту ссылку вытянуть в столбец рядом, там бы я ее поменял на функцию "гиперссылка" и в дальнейшем не мучался.


О! классная идея, как это мне самому в голову не пришло! :)




Sub qwe()
Dim lnk As Hyperlink
For Each lnk In ActiveSheet.Hyperlinks
lnk.Range.Next.Value = lnk.Address
Next
End Sub

Sub unqwe()
Dim lnk As Hyperlink
For Each lnk In ActiveSheet.Hyperlinks
lnk.Address = lnk.Range.Next.Value
Next
End Sub



Осторожно, если в ячейкках справа от гтперссылок что-то есть, то значения пострадают.
Можно также использовать отдельный лист для правки адресов. Тогда вместо lnk.Range.Next.Value надо писать WorkSheets("Отдельный лист").Cells(i,1)
переменную i до цикла установить в 1 и увеличивать перед Next.

Но в этом случае между вызовами qwe и unqwe НЕЛЬЗЯ править ссылки на листе, иначе они перемешаются!

e-aleks
23.12.2004, 11:52
Спасибо большое.
Все прекрасно работает.

Naeel Maqsudov
23.12.2004, 14:22
Тут еще выяснилась интересная штука, если ячейку с гиперссылкой скопировать, то
ActiveSheet.Hyperlinks.Count не изменяется. Т.е. в документе остается одна гиперссылка которая отображается в двух ячейках. (Естественно при исправление этой ссылки через ActiveSheet.Hyperlinks(i).Address=... влияет на обе ячейки)

Причем, прикол в том, что
ПерваяЯчейка.Hyperlinks.Count = 1 и ВтораяЯчейка.Hyperlinks.Count =1
Т.е. в каждой ячейке есть по ссылке, но общее количество прежнее

Теперь если отредактировоать гиперссылку во второй ячейке, то

ActiveSheet.Hyperlinks.Count увеличится; ПерваяЯчейка.Hyperlinks.Count = 1; а вот ВтораяЯчейка.Hyperlinks.Count = 2

Причем
ВтораяЯчейка.Hyperlinks(1) это старая гиперссылка, и изменение ни на что не влияет, а ВтораяЯчейка.Hyperlinks(2) это новая ссылка, которую и надо править.

Вот такая свистопляска с гиперссылками...

PS
Это проверено пока в EXCEL97

e-aleks, спасибо за вопрос. Очень познавательно :)

Naeel Maqsudov
23.12.2004, 14:26
Теперь если отредактировоать гиперссылку во второй ячейке, то

ActiveSheet.Hyperlinks.Count увеличится; ПерваяЯчейка.Hyperlinks.Count = 1; а вот ВтораяЯчейка.Hyperlinks.Count = 2

При дальнейших правках ВтораяЯчейка.Hyperlinks.Count остается равным 2....

Мистика....

Игорь Акопян
23.12.2004, 16:39
Специально проверил в 2000.
При копировании ActiveSheet.Hyperlinks.Count увеличивается.
Selection.Hyperlinks.Count показывает реальное кол-во гиперссылок в выделенном диапазоне, как при создании, так и после копирования

e-aleks
21.01.2005, 15:50
А можно добавить в код пару строчек, чтобы в ячейке рядом отображалось еще название страницы.
Заранее спасибо.

Naeel Maqsudov
01.02.2005, 22:02
Элементарно. Достаточно одной строки.



Sub qwe()
Dim lnk As Hyperlink
For Each lnk In ActiveSheet.Hyperlinks
lnk.Range.Next.Value = lnk.Address
lnk.Range.Next.Next.Value = lnk.Range.WorkSheet.Name
Next
End Sub



... некрасиво получилось... часто втречается lnk надо with использовать...

e-aleks
02.02.2005, 07:15
Спасибо, большое.