In this article we will show you how to display file name as a hyperlink to a file in Excel. We first introduced this concept in this article but will expand upon it here. For this we will use the Hyperlinks.Add Method. Let’s have a look at the syntax first.

Hyperlinks.Add (Anchor , Address , SubAddress , ScreenTip , TextToDisplay)

where

Anchor: is the anchor for the hyperlink. Can be either a Range or Shape object (required)

Address: Address of the hyperlink (Path to the file in our case; can also be a URL or email hyperlink) (required)

SubAddress: Sub-address of the hyperlink (optional)

ScreenTip: The screen tip to be displayed when the mouse pointer is hovered over the hyperlink (optional)

TextToDisplay (string): The text (file name in our case) to be displayed for the hyperlink. (optional)

The method returns a Hyperlink object that represents the new hyperlink

So, let us look at an example on how to implement this. Say, you want to create one hyperlink each for all the files in a folder. First, we will need to create a FileSystemObject and then get the folder object for the folder whose files we need to list.

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object
Set objFolder = objFSO.GetFolder("E:\FolderName")

Then we loop through each of the files in the folder and assign a hyperlink to it. Clicking on the hyperlink will open the file.

i = 1

'Loop through each file in the folder
For Each objFile In objFolder.Files
 
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=objFile.Path, TextToDisplay:=objFile.Name, ScreenTip:=objFile.Path
 i = i + 1

Next objFile

The hyperlinks will be created in a new row each in column 1 — specified by Cells(i, 1)

Address contains the absolute path to the file — specified by objFile.Path

TextToDisplay is the name of the file — specified by objFile.Name

ScreenTip is set to the file path, so, you can view the path when you mouse over the filename (i.e. the hyperlink)

Here is all the code put together:

Option Explicit

Sub addHyperlinks()

Dim objFSO As Object
 Dim objFolder As Object
 Dim objFile As Object
 Dim i As Integer
 Dim dataSheet As Worksheet
 
 Set dataSheet = Sheet1
 
 'Create an instance of the FileSystemObject
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 
 'Get the folder object
 Set objFolder = objFSO.GetFolder("E:\FolderName")
 
 i = 1
 
 'Loop through each file in the folder
 For Each objFile In objFolder.Files
 
 dataSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:=objFile.Path, TextToDisplay:=objFile.Name, ScreenTip:=objFile.Path
 i = i + 1
 
 Next objFile

End Sub

So, if the contents of the folder are as shown below:

The output will be like this:

Let us look at a couple of other examples of how to add other types of hyperlinks.

Example 1: Add a web address as a hyperlink

 With Sheet1
.Hyperlinks.Add Anchor:=.Range("A1"), _
Address:="http://google.com", _
ScreenTip:="Google Home Page", _
TextToDisplay:="Click for Google Search"
 End With

The hyperlink will be displayed like this

Clicking on it will take you to Google Search

Example 2: Add an email hyperlink

With Sheet1
.Hyperlinks.Add Anchor:=.Range("A1")
Address:="mailto:[email protected]?subject=Thank You"
ScreenTip:="Email us today"
TextToDisplay:="Contact Us"
End With

The hyperlink will be displayed like this

Clicking on the hyperlink will open your default mail client with the recipient and subject populated.

You can also edit the hyperlinks by right clicking on it > Edit Hyperlink. This is how the mail hyperlink dialog box looks.

Example 3: Add hyperlink to a picture

With Sheet1
.Hyperlinks.Add Anchor:=.Shapes("Picture1"), _
Address:="http://google.com", _
ScreenTip:="Google Web Site"
End With

So, a hyperlink will be assigned to the shape with name Picture1, clicking on it will take you to the specified web address

Example 4: Loop through all the hyperlinks in the sheet

You can also loop through all the hyperlinks in the sheet and get their properties, like, Name, Address, EmailSubject, ScreenTip, TextToDisplay and SubName among others.

The following code displays the name of all the hyperlinks one by one and then deletes them.

With Sheet1
 For Each hLink In .Hyperlinks
    MsgBox hLink.Name
    hLink.Delete 
 Next
End With

You can also follow the hyperlink using hLink.Follow