How to list all filenames in a folder and create hyperlinks for them in Excel?

For your daily work, you may need to import multiple filenames from a folder to a worksheet, and create hyperlinks for each files so that open the file quickly and easily. Maybe, you can copy and paste the filenames and create a hyperlink for it one by one manually, but, this will waste much time if there are hundreds files in the folder. Today, I will talk about some quick and interesting methods to deal with this job.

List all filenames in a folder and create hyperlinks with VBA code

List all filenames in a folder and create hyperlinks with Kutools for Excel

To list and create hyperlink with files from a folder, the following VBA code can do you a favor.

1. Activate a new worksheet to put the result.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA: List all filenames in a folder and create hyperlinks

Sub Example1()
'Updateby Extendoffice
    Dim xFSO As Object 
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

4. Then press F5 key to run this code, and choose the folder that you want to list the files from the Browse window, see screenshot:

doc list create hyperlinks 1

5. After specifying the folder, click OK button, and all the files in the specific folder have been listed into active worksheet with hyperlinks, see screenshot:

doc list create hyperlinks 2

Note: If there are subfolders in your specific folder, the files in the subfolders will not be listed.

To list all files both in a folder and its subfolders and created hyperlinks for each file, Kutools for Excel’s Filename List may do you a favor. With this feature, all files in folder and subfolders will be listed at once with some attributes, such as file name, file size, created time, containing folder and so on.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Go to Download
Free Trial 30 days Purchase
PayPal / MyCommerce

If you have installed Kutools for Excel, please do with following steps:

1. Click Kutools Plus > Import & Export > Filename List, see screenshot:

2. In the Filename List dialog box, please do the following operations:

(1.) Click doc list create hyperlinks 5 button to select the folder that you want to list the filenames;

(2.) Check Include files in subdirectories if you want to list filenames within subfolders;

(3.) Specify the file types that you want to find and list under the Files type section;

(4.) Select the file size unit that you want to display;

(5.) Check Create hyperlinks at the left bottom of the dialog box.

Download Kutools for Excel Now !

3. Then click Ok button, and all the files in the folder and subfolders have been listed in a new worksheet with the hyperlinks as you need, see screenshot:

doc list create hyperlinks 6

Click to know more details about the Filename List utility…

Download and free trial Kutools for Excel Now !

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

8 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Ellie · 1 years ago
How would I change this code to have it list folder names rather than file names? And how would I change it to check subfolders for files?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hi, Ellie,
  To list all folder and subfolder names from a directory, the below VBA code can help you, please try, hopt it can help you.
  
  Sub FolderNames()
  Application.ScreenUpdating = False
  Dim xPath As String
  Dim xWs As Worksheet
  Dim fso As Object, j As Long, folder1 As Object
  With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Choose the folder"
  .Show
  End With
  On Error Resume Next
  xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
  Application.Workbooks.Add
  Set xWs = Application.ActiveSheet
  xWs.Cells(1, 1).Value = xPath
  xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set folder1 = fso.getFolder(xPath)
  getSubFolder folder1
  xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
  xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
  Application.ScreenUpdating = True
  End Sub
  Sub getSubFolder(ByRef prntfld As Object)
  Dim SubFolder As Object
  Dim subfld As Object
  Dim xRow As Long
  For Each SubFolder In prntfld.SubFolders
  xRow = Range("A1").End(xlDown).Row + 1
  Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
  Next SubFolder
  For Each subfld In prntfld.SubFolders
  getSubFolder subfld
  Next subfld
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  yuri · 9 months ago
  
  How would you code this to make hyperlinks for path and directory
  
  Reply
  
  To post as a guest, your comment is unpublished.
  skyyang · 9 months ago
  
  Hi, yuri,
  To solve your problem, please try the following code:
  
  Sub FolderNames()
  Application.ScreenUpdating = False
  Dim xPath As String
  Dim xWs As Worksheet
  Dim xRg As Range
  Dim fso As Object, j As Long, folder1 As Object
  With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Choose the folder"
  .Show
  End With
  On Error Resume Next
  xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
  Application.Workbooks.Add
  Set xWs = Application.ActiveSheet
  Set xRg = xWs.Cells(1, 1)
  xRg.Value = xPath
  xWs.Hyperlinks.Add Anchor:=xRg, Address:=xPath, TextToDisplay:=xPath
  xWs.Cells(2, 1).Resize(1, 5).Value = Array("Path", "Dir", "Name", "Date Created", "Date Last Modified")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set folder1 = fso.GetFolder(xPath)
  getSubFolder folder1
  xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535
  xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit
  Application.ScreenUpdating = True
  End Sub
  Sub getSubFolder(ByRef prntfld As Object)
  Dim SubFolder As Object
  Dim subfld As Object
  Dim xRow As Long
  Dim xStr As String
  Dim xRg As Range
  For Each SubFolder In prntfld.SubFolders
  xRow = Range("A1").End(xlDown).Row + 1
  Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified)
  Set xRg = Cells(xRow, 1)
  xRg.Worksheet.Hyperlinks.Add Anchor:=xRg, Address:=xRg.Value, TextToDisplay:=xRg.Value
  Set xRg = Cells(xRow, 2)
  xRg.Worksheet.Hyperlinks.Add Anchor:=xRg, Address:=xRg.Value, TextToDisplay:=xRg.Value
  Next SubFolder
  For Each subfld In prntfld.SubFolders
  getSubFolder subfld
  Next subfld
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
jms · 1 years ago
"VBA: List all filenames in a folder and create hyperlinks" is great! It works perfectly! Thank you for posting! One question... Now that you told us how to add links from folder, is it possible to use those links to get information from say one or two different sheets in each of the files listed? They would be the same cells for each of the files we now have a list for. This would help create a comprehensive contents with active links to otherwise unintelligible file names.
- Reply
To post as a guest, your comment is unpublished.
alice · 2 years ago
Great thanks although can not view the files in the selected directory as barry said, it creates hyperlinks for all files in that directory anyway.
- Reply
To post as a guest, your comment is unpublished.
mubashirazizch@gmail.com · 2 years ago
Awesome ..thanks a lot. It really saved my time.
- Reply
To post as a guest, your comment is unpublished.
barry · 4 years ago
Running the VBA code listed above, shows the selected directory is empty, when it is full of Excel files.
- Reply