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.
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:\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:
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
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
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.