How To Write A Macro To List All Files In A Folder – Macro Mondays

Written by Barbara in Blog,Macro Mondays,Macros,VBA

Hello, Excellers I am excited to share with you some cool VBA Macro fun.  I find this a really useful macro which uses the Excel For Loop to list all files in a folder or directory.  Similarly, we can also extract extra attributes about the files we are interested in such as the size, the date last modified and by who if we so wish.  Similarly, if you have multiple files and folders you can use this method to carry out some housekeeping on folders and subfolders.  Use it to see details of how many files and sometimes duplicating or older files you have.  It is just simply a great way to list all files in a directory.

If you prefer to use a non-VBA or Excel Macro version of this process then you can check out my #formulafriday blog post here which uses the FILES function to extract a list of files in a folder.  So, this is where we have a bit of fun with VBA so let’s get back to some awesome code and learn to use the Excel For Loop.

How Does The Macro Work?.

The Macro uses the Excel For Loop which is probably one of the most powerful programming techniques. The For Loop is typically used to move sequentially through a list of items or numbers.

First of all, we start this Macro by opening the Excel Visual Basic Editor.  You can either do this by hitting ALT+F11 or by selecting the Developer Tab | Visual Basic.  This allows us to Insert a new Module to store our VBA code.   We have two choices as to where to store the code.  If we want to only use this code in this particular workbook then select to insert a Module within this workbook.  While, in contrast, if you want to be able to reuse the code then insert the new Module into the Personal Macro Workbook.  The Excel VBA code stored in your Personal Macro workbook will be made available in any active Excel session that you open.  As a result, it is really easy to keep all off your Excel VBA Macro’s together in one place.

Excel For Loop

More Information About Your Personal Macro Workbook

So, feel free to read more about using your Personal Macro Workbook in my recommended blog posts below.

Why Is My Personal Macro Workbook Not Loading Automatically?

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Create A Shortcut To Your Personal Excel Macro Workbook

Starting The Macro

Next, type Sub then the name of your Macro, in this example, I am naming it ListAllFiles

[stextbox id=’info’]Sub ListAllFiles

End Sub

[/stextbox]

Notice Excel will insert the End Sub automatically for us to end the Sub Routine – or Macro

Declaring Variables

First of all, we need to declare a number of variables.  This action simply creates a memory container in Excel for these values.

[stextbox id=’info’]

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

[/stextbox]

Add A New Worksheet To The Current Workbook.

Excel adds a new worksheet object to store the list of files detailed in the directory.  This is added to the active workbook.

[stextbox id=’info’]

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set ws = Worksheets.Add
[/stextbox]

Retrieve The Folder Object Associated With The Directory

This is where we set the file path which we will use to list all files in a directory.   In this example, we are setting it to the C:\ directory.  This can be changed as the user requires.

[stextbox id=’info’]

Set objFolder = objFSO.GetFolder(“C:\”)
ws.Cells(1, 1).Value = “The files found in “ & objFolder.Name & “are:”

[/stextbox]

Loop Through All Of The Files In The Folder – Using the Excel For Loop

So, now we instruct Excel to loop through all files in the folder.  We insert the name of the files into the newly created worksheet.

[stextbox id=’info’]

For Each objFile In objFolder.Files
ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
Next[/stextbox]

 

A Final Clean Up

Finally, this code now releases these values from Excel’s memory

[stextbox id=’info’]

Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
[/stextbox]

The End Of The Routine.

Consequently, the routine now ends once Excel has looped through all files in the specified directory.

[stextbox id=’info’]

End Sub
[/stextbox]

 

Putting All Of The VBA Code Together And Running The Macro

So, this is my favourite part of the whole process.  Getting to test the code.  Select the Macro from the drop-down list in the Macro Group in the Developer Tab.  You can now use the Excel For Loop to list all files in a directory.

Excel VBA Macro

 

Copy The VBA Macro Code.

Here is all of the code in one place.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

1If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.

 

Learn Excel With Our Excel Online Courses

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  So, do you need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.

 

ExcelRescue.net

Udemy.com Home page 125x125

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts