Excel VBA: Display File Name as Hyperlink
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)
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