VBA List Files in Folder / Create Hyperlink Menu

In this tutorial, you will learn how to get names of all files in a folder and put them into a Worksheet. You will also learn how to create a menu of links to open the files.

Instead, if you want to learn how to check if a file exists, you can click on this link: VBA File Exists 

Using the FileSystemObject to Get the List of Files in a Folder

VBA allows you to list all files from a folder, using the FileSystemObject.

We will show how to get a list of files in the folder C:\VBA Folder and put it into the first column of the Worksheet. This folder consists of 5 files, as shown in Image 1:

Image 1. Files in folder C:\VBA Folder

Here is the code:

Sub LoopThroughFiles ()

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name

    i = i + 1

Next oFile

End Sub

In the example, first create an object of the class Scripting.FileSystemObject:

Set oFSO = CreateObject("Scripting.FileSystemObject")

Then set the folder using the method GetFolder:

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

Next loop through each file in oFolder, using oFile.Name to get the name of every file in the folder and write it in the next empty row:

For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name
    i = i + 1

Next oFile

Image 2. Worksheet with the list of files in the folder

As you can see in Image 2, all 5 files from the C:\VBA Folder are listed in the first column.

Create a Hyperlink Menu of All Files in Folder

This macro can be a great time saver if you have a lot of files in a particular folder that you access regularly.  You can open your Excel file, and click on the hyperlink to open any of the individual files in the folder.

1. First, type the name of the folder to access in a location in your worksheet.

2. Then, type the following macro into a module in the worksheet in the VBE Editor.

Sub HyperlinkMenuAllFiles()
  Dim fileName As Variant
  Dim strLocation As String
  Dim x As Integer

  strLocation = Range("C2")
  fileName = Dir(strLocation)
  x = 4

While fileName <> ""
   Sheets("Sheet1").Cells(x, 3).Select
   ActiveSheet.Hyperlinks.Add _
   Anchor:=Selection, Address:=strLocation & fileName, SubAddress:= _
   "", TextToDisplay:=fileName
   x = x + 1
   fileName = Dir
Wend

End Sub

This code will loop through all the files in a folder, creating a hyperlink to each file.

3. Now, return to the Excel window, and, in the Ribbon, select Developer > Code > Macros and then select the macro and click Run.

4. A quicker way to run the macro would be to create a button for the macro in your worksheet.

In the Ribbon, select Developer > Insert > Form Controls > Button.

Click and drag in the worksheet to create a button.  As soon as you release the mouse button, the assign macro dialog box will appear.

(1) Select the macro you wish to assign to the button, and then, 2) click OK.

Type an appropriate name for the button and then click off the button.

Click the button to run the macro.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!