Insert Hyperlinks from Text (VBA)

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.

The list:

Insert Hyperlink Macro 1

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.

Insert Hyperlink Macro 2

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

=$G$1&”\”&C3&”.pdf”

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

C:\Users\Your Name\Documents\Home Appliances\RX123.pdf

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

Insert Hyperlink Macro 3

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

Here is our code block:

Sub Insert_Hyperlink()

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

Dim i As Range

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

i.Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _

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

TextToDisplay:=”PDF”

ActiveCell.Offset(1, 0).Select

ActiveCell = i.Offset(1, 0)

End If    ‘ End of the condition block

Next i      ‘ Proceeds with the loop

End Sub

 

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.

Insert Hyperlink Macro 4

It will open an empty VBA window.

Insert Hyperlink Macro 5

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.

Insert Hyperlink Macro 6

Paste the code from above into this blank sheet.

Insert Hyperlink Macro 7

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

Insert Hyperlink Macro 8

The result:

Insert Hyperlink Macro 9

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.

 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *