Работа с гиперссылками в Excel?
1. Мне много приходится работать с ссылками открывать и просматривать. специальная программа формирует excel файл таким образом, что гиперссылки идут как текст и чтобы сделать каждую активной нужно дважды кликать мышью по каждой — долго и утомительно. Внимание вопрос: как сделать весь столбец с активными ссылками?
2. Как открыть в браузере сразу несколько ссылок столбца разом, а не поочередно нажимая на каждую? такое возможно?
EducatedFool:
1. Запустите этот макрос:
Sub test()
Dim cell As Range, ra As Range: Application.ScreenUpdating = False
Set ra = Range([c2], Range("c" & Rows.Count).End(xlUp))
For Each cell In ra.Cells
If Len(cell) Then cell.Hyperlinks.Add cell, cell
Next cell
End Sub2. Как открыть в браузере сразу несколько ссылок в столбце разом
Теперь выделяете диапазон ячеек — и запускаете макрос.
Откроются все гиперссылки только из видимых ячеек.
Sub test3()
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeVisible)
ThisWorkbook.FollowHyperlink cell
Next cell
End SubПохожие статьи
- Примечание в ячейки листа со значением из другой ячейки
- Как сделать, чтобы значение предлагалось ввести пользователю?
- Как макросом брать данные с word документов в excel?
- Разделить файл на несколько частей с помощью макроса в excel
- Как определить путь к файлу (текущую папку) в excel?
- Как сделать, чтобы заполненные ячейки в excel считались с условием?
- Как скопировать с одного столбца через одну строку в другой столбец данные
- Создание выпадающего списка средствами VBA
- Как сделать, чтобы выводило сообщение «данные пользователем не заполнены», если все ячейки столбца G пустые
- Как вернуть из функции 2 и более аргумента в VBA?
Динамическая гиперссылка
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент: Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности. В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка — Имя — Присвоить (Insert — Name — Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) : =ПСТР(ЯЧЕЙКА(«имяфайла»;Клиенты!$A$1); ПОИСК(«[«;ЯЧЕЙКА(«имяфайла»;Клиенты!$A$1)) ;256) &»!» =MID(CELL(«filename»,$A$1),FIND(«[«,CELL(«filename»,$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, то можно получить вот такие симпатичные значки гиперссылок:
Ссылки по теме
- Использование функции ВПР (VLOOKUP) для подстановки значений
- Создание писем с помощью функции ГИПЕРССЫЛКА
3 простых способа пакетного открытия нескольких гиперссылок в вашем Excel
Если вы хотите пакетно открыть несколько гиперссылок в Excel, вы можете обратиться к этой статье. Здесь мы представим вам 3 удобных метода. У каждого из них есть свои плюсы и минусы. Вы можете выбрать любой в соответствии с вашими потребностями.
В некоторых случаях при просмотре файла Excel, состоящего из множества гиперссылок, может потребоваться пакетное открытие нескольких гиперссылок. Если нужно открыть только три ссылки или около того, вы можете просто использовать метод 1 ниже. Тем не менее, если вы хотите пакетно открыть все ссылки в желаемых диапазонах, вы можете вместо этого использовать метод 2. Конечно, иногда может потребоваться пакетное открытие всех ссылок, разбросанных по всей книге. Тогда можно использовать последний способ.
Способ 1: вручную открывать гиперссылки одну за другой
Чтобы открыть гиперссылку в ячейке вручную, вы можете напрямую щелкнуть ячейку. Следовательно, если не слишком много гиперссылок, которые нужно открыть, вы можете щелкнуть ячейки, чтобы открыть гиперссылки одну за другой.
Способ 2: пакетное открытие гиперссылок в выбранных диапазонах
Тем не менее, иногда вы хотите открыть все гиперссылки в выбранных диапазонах. В этом случае вы можете использовать следующий код VBA.
- Прежде всего, запустите редактор VBA в текущем файле Excel в соответствии с «Как запустить код VBA в вашем Excel».
- Затем поместите следующий код в модуль или проект.
Sub BatchOpenHyperLinks_SelectedRanges() Dim objSelectedRange As Excel.Range Dim objHyperlink As Excel.Hyperlink 'Получить выбранные диапазоны Set objSelectedRange = Excel.Application.Selection For Each objHyperlink In objSelectedRange.Hyperlinks objHyperlink.Follow Next End Sub
- После этого добавьте этот макрос на панель быстрого доступа или на ленту.
- Наконец, вы можете выбрать нужные диапазоны и нажать кнопку макроса.
- Сразу все гиперссылки в выбранных диапазонах будут открыты в интернет-браузере по умолчанию.
Способ 3: пакетное открытие всех гиперссылок в книге
Иногда вам может понадобиться открыть все гиперссылки в книге, независимо от того, на каком листе. В этой ситуации вы можете вместо этого использовать код VBA.
- Сначала поместите следующий код в модуль или проект в редакторе VBA.
Sub BatchOpenHyperLinks_Workbook() Dim objWorksheet As Excel.Worksheet Dim objSelectedRange As Excel.Range Dim objHyperlink As Excel.Hyperlink 'Обрабатывать каждый рабочий лист Для каждого objWorksheet в ThisWorkbook.Sheets For Every objHyperlink In objWorksheet.Hyperlinks objHyperlink.Follow Next Next End Sub
- Затем нажмите «F5», чтобы запустить этот макрос прямо сейчас.
- Немедленно будут открыты все гиперссылки на каждом листе.
Исправление проблем с Excel
Как и документы Word, книги Excel очень уязвимы. Другими словами, файлы Excel могут быть повреждены по нескольким причинам, включая заражение вирусами, человеческие ошибки, сбои Excel и так далее. Следовательно, вы должны подготовить мощный инструмент исправления Excel, такой как DataNumen Excel Repair, Которые могут восстановить Excel данные, не вспотев.
Об авторе:
Ширли Чжан — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая восстановление мдф и программные продукты для ремонта Outlook. Для получения дополнительной информации посетите www.datanumen.com
Поделись сейчас:
3 ответа на «3 простых способа пакетного открытия нескольких гиперссылок в вашем Excel»
канада фармацевтика онлайн говорит:
ох, этот блог просто фантастический, мне нравится читать ваши статьи. Продолжайте хорошую работу! Понимаете, многие люди ищут эту информацию, вы могли бы им очень помочь.
Как открыть сразу несколько гиперссылок в excel
1. Мне много приходится работать с ссылками открывать и просматривать. специальная программа формирует excel файл таким образом, что гиперссылки идут как текст и чтобы сделать каждую активной нужно дважды кликать мышью по каждой — долго и утомительно. Внимание вопрос: как сделать весь столбец с активными ссылками?
2. Как открыть в браузере сразу несколько ссылок столбца разом, а не поочередно нажимая на каждую? такое возможно?
Последний раз редактировалось Apos_med; 05.07.2011 в 11:57 .
Программист VBA
СуперМодератор
Регистрация: 13.07.2008
Сообщений: 6,856
| как сделать весь столбец с активными ссылками? |
Запустите этот макрос:
Sub test() Dim cell As Range, ra As Range: Application.ScreenUpdating = False Set ra = Range([c2], Range("c" & Rows.Count).End(xlUp)) For Each cell In ra.Cells If Len(cell) Then cell.Hyperlinks.Add cell, cell Next cell End Sub| Как открыть в браузере сразу несколько ссылок в столбце разом |
Тоже разве что макросом:
Sub test2() Dim cell As Range, ra As Range: Application.ScreenUpdating = False Set ra = Range([c2], Range("c" & Rows.Count).End(xlUp)) i = 0: On Error Resume Next For Each cell In ra.Cells ThisWorkbook.FollowHyperlink cell i = i + 1: If i Mod 15 = 0 Then Stop Next cell End SubЭтот макрос будет открывать по 15 гиперссылок, и останавливаться до следующего нажатия F5 в редакторе VBA
(F5 — продолжение работы макроса)
Можете, конечно, и так попробовать:
Sub test3() Dim cell As Range, ra As Range: Application.ScreenUpdating = False Set ra = Range([c2], Range("c" & Rows.Count).End(xlUp)) On Error Resume Next For Each cell In ra.Cells ThisWorkbook.FollowHyperlink cell Next cell End SubНо вряд ли ваш браузер обрадуется открытию сотен гиперссылок за один раз.
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
Регистрация: 05.07.2011
Сообщений: 5
спасибо большое
Регистрация: 05.07.2011
Сообщений: 5
На счет второго, который открывает 15 ссылок. Макрос начинает их открывать с 1-й строчки и далее, даже если строчка скрыта фильтром. Не могли бы дать код чтобы он(макрос) начинал открывать ссылки не с верху, а снизу, например — с последней строчки или лучше начинал с указанной мной ячейки в редакторе VB
Программист VBA
СуперМодератор
Регистрация: 13.07.2008
Сообщений: 6,856
Попробуйте этот макрос:
Sub test3() On Error Resume Next For Each cell In Selection.SpecialCells(xlCellTypeVisible) ThisWorkbook.FollowHyperlink cell Next cell End SubТеперь выделяете диапазон ячеек — и запускаете макрос.
Откроются все гиперссылки только из видимых ячеек.
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
Регистрация: 05.07.2011
Сообщений: 5
спасибо! отлично работает. весь офис рад)
Регистрация: 05.07.2011
Сообщений: 5
Хотел бы еще узнать, а можно ли сделать так, чтобы макрос встречая битую ссылку не подвешивал мне весь excel, так что его приходится перезапускать, а, например выжидал 5-7 секунд и переходил к обработке следующей ссылке.
Проблема в том что если макросу дать открыть 10 ссылок и он на пути следования натыкается на ссылку сайт которой недоступен или неотвечает, то он разом подвешивает всю систему и требуется перезапуск.
Последний раз редактировалось Apos_med; 14.07.2011 в 10:46 .
Регистрация: 21.06.2016
Сообщений: 1
Последний макрос чудесно работал до вчерашнего дня. У меня в эксель ссылки на профили вконтакте. Я так понял возникновение проблемы связано с рестайлингом ВК. Теперь открывает все ссылки одинаково по адресу: https://new.vk.com/badbrowser.php
Пишет какую-то хрень про устаревшие браузеры (хотя все новые), про ява и кукис, хотя всё по-прежнему включено.
И это независимо через Хром я открываю или через Оперу.
Ссылки в Эксель такого вида http://vk.com/id35779264
если вручную открывать из эксель — такая же фигня. Если копи пастить — открывается нормально.
Что делать? помогите
Регистрация: 09.01.2008
Сообщений: 26,238
а если так попробовать?
Sub FollowHyperlinkAlt(ByVal URL As String) Shell "CMD.EXE /C START """" """ & URL & """" End Sub Sub test3() On Error Resume Next For Each cell In Selection.SpecialCells(xlCellTypeVisible) 'стандартная процедура не работает: ThisWorkbook.FollowHyperlink cell FollowHyperlinkAlt (cell) Next cell End Sub
| Serge_Bliznykov |
| Посмотреть профиль |
| Найти ещё сообщения от Serge_Bliznykov |
Регистрация: 01.04.2012
Сообщений: 2
Сообщение от vadimko4
Последний макрос чудесно работал до вчерашнего дня. У меня в эксель ссылки на профили вконтакте. Я так понял возникновение проблемы связано с рестайлингом ВК. Теперь открывает все ссылки одинаково по адресу: https://new.vk.com/badbrowser.php
Пишет какую-то хрень про устаревшие браузеры (хотя все новые), про ява и кукис, хотя всё по-прежнему включено.
И это независимо через Хром я открываю или через Оперу.
Ссылки в Эксель такого вида http://vk.com/id35779264
если вручную открывать из эксель — такая же фигня. Если копи пастить — открывается нормально.
Что делать? помогите
Тоже самое, что у тебя получилось открыть через openoffice calc. Хотя на работе на компе тот же firefox, office и java стоит и там все открывается нормально.
| ilia.belyaev |
| Посмотреть профиль |
| Найти ещё сообщения от ilia.belyaev |