List Files in Folder Create Hyperlink

How to list all files in a folder and create hyperlink for each file using VBA. We have learnt how to loop through files in a folder and sub-folders using the DIR function. Today we learn how to list all files in a folder using the File System Object.

Watch the video below:

Here’s the complete VBA macro code to list all files in a folder and then apply an hyperlink to each of the files:

Option Explicit

Sub ListAllFilesInFolderCreateHyperlink()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Long

Set oFSO = CreateObject(“Scripting.Filesystemobject”)
Set oFolder = oFSO.getfolder(“C:\MyHyperlinkedFiles”)

i = 1

For Each oFile In oFolder.Files
Cells(i + 1, 2) = oFile.Name
Cells(i + 1, 3) = oFile.Path
Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=oFile.Path, TextToDisplay:=oFile.Name

i = i + 1

Next oFile

End Sub

Download a sample VBA tutorial file for practice: