Копирование гиперссылки в excel vba
Добавляет гиперссылку к указанному диапазону или фигуре.
Синтаксис
expression.Add (Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
выражение: переменная, представляющая объект Hyperlinks.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Anchor | Обязательный | Object | Привязка для гиперссылки. Может быть либо объектом Range, либо объектом Shape. |
Address | Обязательный | String | Адрес гиперссылки. |
SubAddress | Необязательный | Variant | Субадрес гиперссылки. |
ScreenTip | Необязательный | Variant | Всплывающая подсказка, отображаемая при наведении указателя мыши на гиперссылку. |
TextToDisplay | Необязательный | Variant | Текст, отображаемый для гиперссылки. |
Возвращаемое значение
Объект Hyperlink, представляющий новую гиперссылку.
Примечания
При указании аргумента TextToDisplay текст должен быть строкой.
Пример
В этом примере в ячейку A5 добавляется гиперссылка.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Это простая, но весьма полезная функция, позволяющая создать живую ссылку на:
- любую ячейку на любом листе в книге
- именованный диапазон
- фрагмент умной таблицы
- внешний файл Excel на диске или на сервере в сети
- веб-страницу
Кроме того, эта функция умеет создавать заполненную форму письма email, что сильно помогает при рассылке типовых оповещений.
Синтаксис функции прост:
=ГИПЕРССЫЛКА( Адрес ; Текст )
- Текст - это видимый текст гиперссылки в ячейке (обычно выделенный синим цветом и подчеркиванием, как все гиперссылки), по которому щелкает пользователь. Может быть любым, это роли не играет.
- Адрес - место, куда мы хотим сослаться (и это самое интересное).
Давайте рассмотрим все варианты подробнее.
Ссылка на веб-страницу
Это самый простой и очевидный вариант применения нашей функции ГИПЕРССЫЛКА (HYPERLINK) . Первым аргументом здесь будет полная ссылка на веб-страницу (URL):
Обратите внимание, что адрес должен быть полным, т.е. обязательно начинаться с протокола (обычно "http"). При щелчке по созданной ссылке заданный сайт откроется в браузере по умолчанию.
Ссылка на ячейку внутри книги
Если нужна ссылка на ячейку или диапазон, находящийся внутри текущей книги, то адрес нужно будет указать следующим образом:
Если имя листа содержит пробелы, то его обязательно заключать в апострофы - иначе ссылка не сработает:
Ссылка на ячейку во внешней книге
Можно легко создать ссылку на ячейку во внешней книге. Тогда в качестве первого аргумента нужен будет полный путь к файлу (с расширением!), имя листа и адрес ячейки:
Аналогично внутренней ссылке, если имя листа содержит пробелы, то его необходимо дополнительно заключить в апострофы.
Ссылка на файл на сервере
Также поддерживаются сетевые ссылки, если файл сохранен, например, на корпоративном сервере в общей папке - в этом случае используется путь в формате UNC, начинающийся с двух обратных дробей:
Ссылка на именованный диапазон
Если вы создали именованный диапазон (например, через Формулы - Диспетчер имен - Создать), то можно спокойно использовать это имя в ссылке:
Если нужно сослаться на именованный диапазон во внешнем файле, то потребуется уточнить его имя и путь к нему, как в предыдущем пункте. Имя листа указывать уже не нужно:
Ссылка на умную таблицу или ее элементы
Если вы знакомы с умными таблицами, то знаете какая это полезная штука. При желании, можно легко создать с помощью функции ГИПЕРССЫЛКА (HYPERLINK) ссылку на любой нужный нам фрагмент умной таблицы или на нее целиком. Для этого в первом аргументе нужно указать имя таблицы (например Таблица1) и кодовое обозначение ее элемента:
Поддерживаются следующие обозначения:
Формирование заполненной формы письма email
Это относительно экзотический вариант применения функции гиперссылка, позволяющий создавать заполненный бланк электронного письма:
При щелчке по такой ссылке мы увидим вот такую красоту:
Можно указывать несколько адресатов, получателей обычной и скрытой копий, форматировать текст письма и т.д. Очень удобно для служебных уведомлений и рассылок. Единственное, что нельзя таким образом сделать - прикрепить вложения. Подробнее про все возможности и параметры такого способа рассказано в отдельной статье.
Использование нестандартных символов
Иногда можно сочетать функцию ГИПЕРССЫЛКА с функцией вывода нестандартных знаков СИМВОЛ (CHAR), которую мы уже разбирали. Это позволяет заменить простой текст ссылки на что-то более симпатичное:
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:
Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности.
В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) :
=ПСТР(ЯЧЕЙКА("имяфайла";Клиенты!$A$1); ПОИСК("[";ЯЧЕЙКА("имяфайла";Клиенты!$A$1)) ;256) &"!"
Разберем эту конструкцию на составляющие для понятности:
- ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))
Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Как скопировать гиперссылку только из одной ячейки в другую в Excel?
Предположим, у меня есть список значений в столбце A, и каждая ячейка содержит другую гиперссылку, теперь я хочу скопировать только гиперссылки без текста в другой столбец E, как показано на следующем снимке экрана. Возможно, нет прямого способа решить эту задачу в Excel, но здесь я могу ввести код VBA для решения этой проблемы.
Скопируйте гиперссылку из одной ячейки в другую с кодом VBA
Чтобы скопировать только адреса гиперссылок без текста в другие ячейки, следующий код может оказать вам услугу, например:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: копировать гиперссылку только из одной ячейки в другую:
3, Затем нажмите F5 нажмите клавишу для запуска этого кода, появится диалоговое окно, напоминающее вам о выборе ячеек, в которые вы хотите скопировать только гиперссылки, см. снимок экрана:
4, Затем нажмите OK, затем выберите ячейки, в которые вы хотите вставить гиперссылки, только в другое диалоговое окно, см. снимок экрана:
5. И адреса гиперссылок были скопированы из исходных ячеек в указанные ячейки по мере необходимости, см. Снимок экрана:
Внимание: Этот код также может помочь вам скопировать гиперссылки с одного листа на другой по вашему желанию.
Как извлечь актуальные адреса из гиперссылок в Excel?
Предположим, у вас есть диапазон ячеек, которые содержат гиперссылки, и теперь вам нужно увидеть реальное назначение гиперссылок и извлечь их из гиперссылок, как показано на следующем снимке экрана. Есть ли простые способы быстро решить эту проблему? Эта статья поможет вам извлечь фактические адреса из нескольких гиперссылок.
With Kutools for Excel’s Convert Hyperlinks feature, you can quickly extract the real URL addresses from the hyperlinks, at the same time, you can also convert the URL text to clickable hyperlinks.
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!
Извлекайте фактические адреса из гиперссылок с помощью функции редактирования гиперссылки
Здесь Изменить гиперссылку может помочь вам извлечь базовый URL-адрес для каждой из этих гиперссылок и поместить этот фактический адрес в другую ячейку. Вы можете сделать так:
1. Выберите ячейку с помощью гиперссылки и щелкните правой кнопкой мыши, в контекстном меню выберите Изменить гиперссылку, см. снимок экрана:
2, И Изменить гиперссылку появится диалоговое окно, выберите и скопируйте (Ctrl + C) весь URL из Адрес поле диалогового окна.
3. Затем закройте диалоговое окно и вставьте фактический адрес в любую ячейку, которую хотите.
Внимание: С помощью этого метода вы можете каждый раз извлекать только один адрес гиперссылки, но если у вас есть несколько гиперссылок, которые необходимо извлечь, вам нужно применять эту функцию повторно.
Извлекайте фактические адреса из гиперссылок с кодом VBA
Для большого количества гиперссылок вышеуказанный метод будет утомительным и трудоемким, поэтому следующий код VBA может помочь вам быстро извлечь несколько адресов из гиперссылок одновременно.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
3, Затем нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно для выбора гиперссылок, по которым вы хотите извлечь реальные адреса, см. снимок экрана:
4. Затем нажмите OK, и содержимое выбранной ячейки было преобразовано в реальные адреса гиперссылок в исходном диапазоне. Смотрите скриншоты:
Извлекайте фактические адреса из гиперссылок с помощью функции определения пользователя
Следующая функция, определяемая пользователем, также может извлекать фактический URl из гиперссылок.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
3. Сохраните код и закройте окно, выберите пустую ячейку, чтобы ввести эту формулу = GetURL (A2) (A2 это ячейка, в которой находится гиперссылка), и нажмите Enter кнопка. Вы можете видеть, что реальный адрес гиперссылки извлечен.
Извлекайте фактические адреса из гиперссылок с помощью Kutools for Excel быстро и легко
Если вы установили Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон, содержащий гиперссылки, которые необходимо извлечь.
2. Нажмите Кутулс > Ссылка > Конвертировать гиперссылки, см. снимок экрана:
3. В Конвертировать гиперссылки диалоговое окно, выберите Адреса гиперссылок заменяют содержимое ячейки и нажмите кнопка из Диапазон результатов указать ячейку, в которую будет помещен результат.
4, Затем нажмите OK, фактические адреса были извлечены из гиперссылок. Смотрите скриншот:
Внимание: Если вы хотите поместить фактические адреса в исходный диапазон, проверьте Преобразовать исходный диапазон.
Нажмите Конвертировать гиперссылки чтобы узнать больше об этой функции.
Демо: извлечение фактических адресов из гиперссылок с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: