VBA List Files in Folder / Create Hyperlink Menu

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on March 14, 2024

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.

VBA hyperlink intro

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

VBA hyperlink locations

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.

VBA hyperlink 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.

VBA Buttons FormControl

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.

VBA hyperlink assign macro

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

VBA hyperlink select macro

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

Click the button to run the macro.

VBA hyperlink show button

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! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples