Remkomplekty.ru

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Hyperlinks vba excel

Что такое гиперссылка?

Гиперссылка — это не просто отображение адреса на ресурс — это активная ссылка, кликнув на которую
-либо откроется страница интернет в браузере по умолчанию
-либо будет открыт файл, на который ведет ссылка
-либо будет открыта директория(папка), к которой ведет ссылка.

Установить гиперссылку можно двумя способами.
ПЕРВЫЙ СПОСОБ
и самый распространенный: правый клик мыши по ячейке-Гиперссылка (Hyperlink) . Откроется окно добавления гиперссылки.

Там можно выбрать вид ссылки:

  • на файл или веб-страницу. Если на веб-страницу, то необходимо в поле «Адрес» указать адрес веб-страницы. Если ссылка на файл — просто выбрать файл в диспетчере файлов.
  • местом в документе. Выбирается необходимый лист и адрес ячейки, на которую необходимо переместиться по нажатии гиперссылки.
  • новым документом. Создается новый документ и сразу ссылка на него. При нажатии гиперссылки будет открыт этот файл.
  • электронной почтой. Указывается адрес электронной почты и тема письма по умолчанию. По нажатии ссылки будет создано письмо на указанный адрес электронной почты и с указанной темой.

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

Создание гиперссылки кодом VBA выглядит следующим образом:

ActiveSheet.Hyperlinks.Add Anchor:=Range(«A1″), _ Address:=»/», _ TextToDisplay:=»/»

Range( «A1» ) — это ячейка, в которой будет создана гиперссылка.
Address( http://www.excel-vba.ru/ ) — адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейку.
TextToDisplay( http://www.excel-vba.ru/ ) — это текст, который будет отображаться в ячейке. Может отличаться от адреса самой ссылки.

ВТОРОЙ СПОСОБ
через функцию ГИПЕРССЫЛКА (HYPERLINK) . Этот способ хорош, если Вам надо создать много гиперссылок на листе:
=ГИПЕРССЫЛКА(» www.excel-vba.ru «;» переход на сайт «)
www.excel-vba.ru — адрес страницы(или путь к файлу или директории), который будет открыт по клике на ячейку.
переход на сайт — текст, который будет отображаться в ячейке. Может отличаться от текста самой ссылки.
Сама функция расположена в категории Ссылки и массивы диспетчера функций.
При применении такого способа создания гиперссылки Вы можете далее указать помимо основного адреса еще и указание на конкретную страницу через ссылку на ячейку:
=ГИПЕРССЫЛКА(«http://www.excel-vba.ru/»& B1 ;»переход на сайт»)
в ячейке B1 — chto-umeet-excel/ . Теперь при клике на ячейку в браузере по умолчанию откроется страница http://www.excel-vba.ru/chto-umeet-excel/
Нетрудно догадаться, что таких ссылок можно создать множество простым копированием формулы в другие ячейки и что немаловажно — в столбце В можно записывать разные окончания ссылок. Если вдруг сайт поменял адрес, но система ссылок осталась прежней — Вам надо будет лишь заменить основной адрес и все. То же самое можно отнести и к файлам на диске: если поменялась лишь корневая директория — то просто меняем её в формуле.

Так же можно создать гиперссылку, переходящую на определенную ячейку на листе:
=ГИПЕРССЫЛКА(«#Лист2!D4″;»Перейти в ячейку D4 Лист2»)
Решетка перед именем листа указывает функции, что переход будет осуществляться в указанную точку открытого документа и поэтому путь документа для открытия не нужен.

Ссылку на ячейку можно указывать динамически, ссылаясь на определенную ячейку, не вписывая её адрес(на примере D2):
=ГИПЕРССЫЛКА(«#Лист2!»&ЯЧЕЙКА(«адрес»; D4 );»Перейти в ячейку «&ЯЧЕЙКА(«адрес»; D4 )&» Лист2″)

Можно указать ссылку на ячейку листа в другой книге:
=ГИПЕРССЫЛКА(«#'[Другая книга.xls]Лист2′!»&ЯЧЕЙКА(«адрес»; D4 );»Перейти в ячейку «&ЯЧЕЙКА(«адрес»; D4 )&» Лист2 Другая книга.xls»)

Или указать путь к файлу, который необходимо открыть по нажатии ячейки:
=ГИПЕРССЫЛКА(«C:UsersДмитрийDesktopКнига 1.xls»;»Открыть книгу Книга 1.xls»)
При использовании гиперссылки из функции для открытия книги нельзя применить так же переход на нужный лист и ячейку. Это связано с ограничением именно самой функции. Т.е. нельзя указать что-то вроде: «C:UsersДмитрийDesktopКнига1.xls#Лист2!D4» .

Важно: Книга, на которую ссылается функция ГИПЕРРСЫЛКА должна быть открыта. Если в имени книги или листа содержатся пробелы(или иные знаки препинания, кроме точки и нижнего подчеркивания), то обязательно в начале имени книги (перед [ ) и после имени листа ставить апостроф(‘). Я специально в одной из последних функций привел пример именно с пробелом — для наглядности:
=ГИПЕРССЫЛКА(«# [Другая книга.xls]Лист2 !»&ЯЧЕЙКА(«адрес»; D4 );»Перейти в ячейку «&ЯЧЕЙКА(«адрес»; D4 )&» Лист2 Другая книга.xls»)
Поэтому если не уверены в том, будет ли имя листа и книги содержать такие символы, лучше в формуле проставлять апострофы всегда. Ошибкой это не будет и гиперссылка будет работать, даже если таких символов в имени не окажется.

Статья помогла? Поделись ссылкой с друзьями!

VBA Hyperlinks

Hyperlinks are URL attached to a value which is seen when we hover mouse over it and when we click on it the URL is opened, in VBA we have an inbuilt property to create hyperlinks in VBA and to use this property we use Add method together with the hyperlink statement to insert a hyperlink in a cell.

Hyperlinks in Excel VBA

Even though we have Page Up & Page Down shortcut key in excel to move between from one sheet to another. But it becomes complex when we have to move between 10 to more worksheets. This is where the beauty of “Hyperlinks in Excel” comes in to picture. The hyperlink is a predetermined URL which takes you to the respective cell or worksheet as assigned.

We all know how to create hyperlinks in the worksheet to quickly move from one sheet to another sheet and you can go to any other sheet as well. But in today’s article, we will show you how to create hyperlinks by using VBA coding.

Formula of VBA Hyperlinks

Let’s look at the formula of the hyperlinks in Excel VBA.

  • Anchor: In which cell you would like to create a hyperlink.
  • Address: What is the URL to the hyperlink to navigate?
  • [Sub Address]: What is the location of the page?
  • [Screen Tip]: What is the value to be showed when you place a mouse pointer on the hyperlink name or cell?
  • [Text to Display]: What is the test to be displayed in the cell? For example Worksheet Name.
Читать еще:  Vba excel if not is nothing

How to Create Hyperlinks in Excel VBA?

Assume you want to create a VBA hyperlink to the sheet named as “Main Sheet” from the other sheet “Example 1”.

In the worksheet “Example 1” and in the cell A1, I am going to create the hyperlink using Code in VBA.

Step 1: First select the cell A1 of worksheet Example 1.

Code:

Step 2: Now by using Active Cell object open hyperlinks.add method.

Code:

Step 3: First argument is “Anchor” i.e. in which cell we would link to create the VBA hyperlink. In this case cell A1 and since we have already selected cell A1 to mention it as “Selection”.

Code:

Step 4: We are not creating any address here, so ignore Address as of now.

Code:

Step 5: Next is Sub Address. Here we need to mention which sheet we are referring to and the first cell of that sheet.

Code:

I have mentioned the sheet name as “Main Sheet” and in that sheet cell address is “A1”.

Step 6: Ignore Screen Tip as well. For Text to display mention the sheet name.

Code:

Ok, done run this code using F5 key or manually then, it will create a hyperlink in the cell A1 in the sheet “Example 1”.

When you click on Hyperlink “Main Sheet ” it redirects to the main sheet.

Hyperlinks of Multiple Sheets with Loops

We have seen creating a VBA hyperlink for one sheet. When we have many sheets it is difficult to create a VBA hyperlink for each sheet with the same line of code for each sheet.

Assume you have 11 worksheets as shown in the below image.

You want to create a hyperlink for each sheet in the Index sheet using VBA Code.

Step 1: Define the variable as a worksheet.

Code:

Step 2: First thing is to select the worksheet Index and select the cell A1.

Code:

Step 3: Now open For Each Loop.

Code:

Step 4: Since we have already selected the cell A1 it is now an active cell. So start the hyperlink with the active cell.

Code:

Step 5: Anchor is a hyperlink cell. So it is the active cell.

Code:

Step 6: Address is nothing mention it as “”.

Code:

Step 7: Subaddress is when we loop through the sheet it should be the sheet name. To refer the sheet name we need a single quote “” with sheet name and “! Cell Address” and close the sheet name with a single quote “”.

Code:

Step 8: Ignore Screen tip and for Text to display you can enter the worksheet name.

Code:

Step 9: To store the hyperlink of each sheet in a different cell every time hyperlink created for one sheet we need to move down one cell from the active cell.

Code:

This will create a hyperlink of all the sheets in the Index sheet. This code is dynamic, whenever there is any addition or deletion of sheets we just need to run this code to have an updated hyperlink.

Recommended Articles

This has been a guide to VBA Hyperlinks. Here we learn how to create hyperlinks in Worksheet Using VBA Code to quickly move from one sheet to another sheet along with some simple to advanced examples . Below are some useful excel articles related to VBA –

Extract URL from a hyperlink in Excel with VBA

The VBA Tutorials Blog

Use this VBA function to extract the URL from a cell in Excel. This versatile VBA function works whether the hyperlink is entered in Excel using the “=HYPERLINK()” function or the “Insert > Hyperlink” menu. To top it off, the function can be called from another VBA macro or it can be entered as a formula directly into another cell in Excel. That’s what makes this function different from other solutions you may find online.

Extracting a URL from an existing hyperlink is great when you want to interactively open the URL in your default browser or use VBA to scrape data from a website.

This helpful macro was submitted by a member of the wellsrPRO community, Mitch! Thank you, Mitch!

Macro to extract the URL from a cell

Created by Mitch

Make powerful macros with our free VBA Developer Kit

There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier.

How to use the LinkLocation Macro

You can call the LinkLocation function both from a cell in Excel and from your VBA editor. Before we demonstrate both methods, let’s pretend you have a spreadsheet with hyperlinks embedded in it, like this:

These hyperlinks point to different URLs on the VBA Tutorials Blog and can be added using the “Insert > Hyperlink” menu, by right-clicking the cell and selecting “Hyperlink” or by using the native Excel =HYPERLINK() function, like this:

It doesn’t matter how your user inserts the hyperlink, the LinkLocation VBA function will be able to extract it.

It’s important to point out the LinkLocation function accepts one argument and that argument should be a single cell. Trying to extract hyperlinks from a range with multiple cells by using this function will generate an error. If the cell you’re trying to extract the URL from doesn’t contain a hyperlink, the function will return an empty an string.

Читать еще:  Что такое диаграмма в excel

Calling the LinkLocation function using VBA

To call the LinkLocation function from another VBA subroutine, simply reference the function by name and set it equal to a string variable, like we do in this demo:

When you run this macro, the string https://wellsr.com/vba/vba-cheat-sheets/ is stored in the variable strURL . You can now do whatever you want with the URL, like using ShellExecute to open the URL in your default browser.

Notice how we only passed the LinkLocation function a single cell, rather than the entire range of cells C1:C4. We did this to avoid an error. You could easily modify the macro to extract all URLs in the range and store them to an array if you’d like.

Calling the LinkLocation function from Excel

If all you wanted to do was to find out where a cell hyperlinks to, you could call the LinkLocation function by placing a formula like this into a cell:

In our example above, this would return the URL https://wellsr.com/vba/add-ins/ . This could be helpful if your spreadsheet had a bunch of hyperlinks and you wanted to avoid opening each one individually just to see where the hyperlinks point to.

Here’s what our extract URLs would look like if we placed our LinkLocation formulas into column D:

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

Полный адрес из .Hyperlinks

Столкнулся тут вот с какой проблемой:

Добавляю я в ячейку листа Excel — Hyperlinks (гиперссылку):

в переменной strFileName указан полный путь к файлу.

Когда же потом обращаюсь, чтобы считать этот адрес, то получаю не полный путь к файлу, а какой-то относительный (обычно относительно файла xls, а если файл лежт изначально в другой директории и показать его путь относительно файла xls невозможно, то тогда он пишет так «..Мои документы1.doc»)

Считываю я адрес так:

т.е. через свойство Address. Облазил все доступные свойства и нифига не нашел где он хранит полный путь и куда его девает. Причем когда наводишь курсор на ячейку с гиперссылкой, то он показывает полный путь, типа — «file:///C: ну и т.д.»

ВОПРОС: кто знает как достать обратно полный адрес из гиперссылки

P.S. пока я не начал мудрить с этими относительными путями. не нравится мне просто такой подход

20 ответов

Originally posted by SergeySV
Столкнулся тут вот с какой проблемой:

Добавляю я в ячейку листа Excel — Hyperlinks (гиперссылку):

в переменной strFileName указан полный путь к файлу.

Когда же потом обращаюсь, чтобы считать этот адрес, то получаю не полный путь к файлу, а какой-то относительный (обычно относительно файла xls, а если файл лежт изначально в другой директории и показать его путь относительно файла xls невозможно, то тогда он пишет так «..Мои документы1.doc»)

Считываю я адрес так:

т.е. через свойство Address. Облазил все доступные свойства и нифига не нашел где он хранит полный путь и куда его девает. Причем когда наводишь курсор на ячейку с гиперссылкой, то он показывает полный путь, типа — «file:///C: ну и т.д.»

ВОПРОС: кто знает как достать обратно полный адрес из гиперссылки

P.S. пока я не начал мудрить с этими относительными путями. не нравится мне просто такой подход

А .Value что возвращает?

Кстати, у меня код:
Range(«A2»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=»\D:Ìîè äîêóìåíòû» _
, TextToDisplay:=»Ìîè äîêóìåíòû»
Range(«A3»).Select
‘ Range(«A3»).Text = Range(«A2»).Hyperlinks.Item(1).SubAddress
Range(«A4»).Value = Range(«A2»).Hyperlinks.Item(1).Address
Range(«A5»).Value = Range(«A2»).Hyperlinks.Item(1).Name
Range(«A6»).Value = Range(«A2»).Hyperlinks.Item(1).ScreenTip
Range(«A7»).Value = Range(«A2»).Value
Range(«A8»).Value = Range(«A2»).Value2
вернул всё, как надо (Office 2000).

Серёж, ты удивишься, но у меня все работает:

Range(«B2»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=»CodeCopySheets.xls»

Range(«B3»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
«C:SergeyCodenetCodeToWord.xls»
End Sub

Sub M2()
MsgBox Range(«B2»).Hyperlinks(1).Address
MsgBox Range(«B3»).Hyperlinks(1).Address
End Sub

Одна возвращает полный путь, вторая — относительный.

Originally posted by Cutty Sark
Серёж, ты удивишься, но у меня все работает:

Range(«B2»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=»CodeCopySheets.xls»

Range(«B3»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
«C:SergeyCodenetCodeToWord.xls»
End Sub

Sub M2()
MsgBox Range(«B2»).Hyperlinks(1).Address
MsgBox Range(«B3»).Hyperlinks(1).Address
End Sub

Одна возвращает полный путь, вторая — относительный.

что-то я не догоняю.

хмм, надеюсь полный путь возвращает ячейка B3.

ща еще у себя поэкспериментирую.

.Value = мне возвращает, то что я забил в TextToDisplay

Originally posted by Cutty Sark
Серёж, ты удивишься, но у меня все работает:

Range(«B2»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=»CodeCopySheets.xls»

Range(«B3»).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
«C:SergeyCodenetCodeToWord.xls»
End Sub

Sub M2()
MsgBox Range(«B2»).Hyperlinks(1).Address
MsgBox Range(«B3»).Hyperlinks(1).Address
End Sub

Одна возвращает полный путь, вторая — относительный.

Кажется я понял в чем прикол:
Твой пример у меня тоже полный путь возвращает, НО попробуй добавить в метод Add следующее переменную — TextToDisplay:=»просто текст» — вот тут чудеса и начинаются.

Т.е. мое подозрение, что Address хорошо работает токо при совпадении TextToDisplay и Address, а вот при их различии начинаются выкрутасы

что-то я не догоняю.

хмм, надеюсь полный путь возвращает ячейка B3.

Читать еще:  Не открывается лист excel

ща еще у себя поэкспериментирую.

И та, и другая возвращают в точности то, что я «зафигачил» им в Address.

(разумеется, всё это на 97-м. :> )

И та, и другая возвращают в точности то, что я «зафигачил» им в Address.

(разумеется, всё это на 97-м. :> )

А в 97 перемн. TextToDisplay в методе Add есть?

Все я убежал, у отца сеня день рождения, так что продолжим завтра, ок?!

А в 97 перемн. TextToDisplay в методе Add есть?

Все я убежал, у отца сеня день рождения, так что продолжим завтра, ок?!

Значит ла-ла говоришь, надо будет попробовать

Бред какой-то, добавление гиперссылки через код в самом Excel у меня тоже работает, а вот то что я добавлял из Outlook связываясь с листом Excel? получается вот так.

посмотри файл, там эти хитрые ссылки, чтобы посмотреть адрес надо два раз щелкнуть на соседней ячейке с текстом — «In the Queue» (сама ссылка в столб. — I ). Чуть ниже висит кнопка для добавления гиперссылки через код из самого Excel. так сказать нижмите и увидите разницу..

Бред какой-то, добавление гиперссылки через код в самом Excel у меня тоже работает, а вот то что я добавлял из Outlook связываясь с листом Excel? получается вот так.

посмотри файл, там эти хитрые ссылки, чтобы посмотреть адрес надо два раз щелкнуть на соседней ячейке с текстом — «In the Queue» (сама ссылка в столб. — I ). Чуть ниже висит кнопка для добавления гиперссылки через код из самого Excel. так сказать нижмите и увидите разницу..

Ну я-то вижу уже результат работы твоих макросов. Внешне это выглядит, как будто ты добавлял относительную ссылку, а не абсолютную. А почему у тебя там косая черта «не в ту сторону»?

Ну я-то вижу уже результат работы твоих макросов. Внешне это выглядит, как будто ты добавлял относительную ссылку, а не абсолютную. А почему у тебя там косая черта «не в ту сторону»?

Я понимаю, что работу, но как вообще теоретически возможно добавление ссылки на файл, чтобы он хранил полный путь (неизвестно там где у себя, но показывает его при наведении на ячейку), а через свойство адрес возвращал токо относительный путь?

Кстати добавление гиперссылки с совпадающим адресом в свойстве TextToDisplay все равно возвращает в Address относит. путь

Видимо пора начинать бегать с бубном вокруг компьютера. 🙁

Пока вывернулся так, просто сделал два столбца: в одном имя файла, а в другом гиперссылка на этот файл и в свойстве TextToDisplay забивается полный путь и считывается уже путь тупо через Cells().Value

Insert Hyperlinks from Text (VBA)

by melih · Published March 25, 2015 · Updated February 23, 2018

When you need to insert a series of hyperlinks into an excel sheet from a list of links, this small macro named “insert hyperlinks from text”can save you a lot of time.

I’ve coded this small macro to help a colleague who messed up a list of thousands of entries with pdf links in each row, by sorting the list partially. His product codes and hyperlinks were not matching and since there is so many rows, it was almost impossible to correct the list row by row.

Here is an example in a smaller scale:

We are preparing a list of home appliances that includes item group, sub group, item code price and a hyperlink to the specifications document of related item.

Assuming specifications documents are in the same folder and named with the item code, we need to insert folder address into any cell (I chose cell G1 to avoid formulas being displayed over each other).

Your Name” part of the address is going to be your windows user name.

Now write the formula below into cell G3 to construct a hyperlink from this folder address and the item code:

When you press enter, your cell should show this text:

C:UsersYour NameDocumentsHome AppliancesRX123.pdf

Copy this formula down until the end of the list and now you have your list of document links ready:

Now we can insert them into corresponding cells in column E.

Here is our code block:

‘ Inserts Hyperlinks into cells in column E from corresponding cells in column H

For Each i In Range(“E:E”) ‘ code will run for all the cells in column E

If i.Offset(0, 2).Value <> “” Then ‘ checks whether the 2nd cell to the right is not empty

‘ This block selects the cell if the condition applies, inserts a hyperlink into selected cell and

‘ sets address as the value of the 2nd cell to the right

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _

Address:=ActiveCell.Offset(0, 2).Value, _

ActiveCell = i.Offset(1, 0)

End If ‘ End of the condition block

Next i ‘ Proceeds with the loop

All the necessary information to understand this code is written as comments into the block. So I will not get into the details.

To run the code you need developer tools to be enabled. Click to the Visual Basic button located to the left side of DEVELOPER Tab, or simply press Alt+F11.

It will open an empty VBA window.

Double-click on the Sheet1(Sheet1) inside the left top window inside the VBA interface. İt will open code window for sheet1. This is a blank white sheet where you can write or paste code into.

Paste the code from above into this blank sheet.

Press Run button or F5 (shortcut) to run the code.

This code may have been a bit more efficient if it would stop after first empty row. But since it runs fast enough already, I did not bother.

Ссылка на основную публикацию
Adblock
detector