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.
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’]
[/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’]
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’]
[/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’]
A Final Clean Up
Finally, this code now releases these values from Excel’s memory
[stextbox id=’info’]
The End Of The Routine.
Consequently, the routine now ends once Excel has looped through all files in the specified directory.
[stextbox id=’info’]
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.
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.
If 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.
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.
