Excel can be used for many tasks outside it’s mainstream use as a database, calculations, data manipulation etc. It can also be used for a large amount of general tasks that maybe you wouldn’t normally think to use Excel for.
This example is useful in Excel projects where you need to know the number of files in a folder, or list them in some way for another process to manage. It can also be used to simply create a list that has no other purpose in Excel other than just being a list that you needed or used as a conduit to help you achieve another objective somewhere else.
This particular code snippet is used in several datanology projects where folder contents are required when importing data from Microsoft Word, or particular text files, where each file that falls into a specific parameter (perhaps by name or file size) needs to be imported, analysed and reported before moving on to the next applicable file.
The VBA can be assigned to a button or added to the VB Modules (ALT + F11) but this example uses a fixed folder C:\My Documents as it’s folder to list files from. Change this according to the folder you need to analyse.
It also relies on a tab being named ‘Data’ so if you don’t have a tab named ‘Data’ you can create one or change the code below accordingly. As a bonus this example also gives you the file size next to the file name, and at the end of the process the length of time run.
Feel free to amend the code as you require, and if you want to ask us a question, use the form below or leave a comment and we’ll respond for all to see and learn from.
Public Sub List_filenames_in_folder()
On Error Resume Next
myDirectory = "C:\My Documents"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = FSO.GetFolder(myDirectory)
'Set SourceFolder = CreateObject("Scripting.Folder")
startTime = Timer
For Each FileItem In SourceFolder.Files
Counter = Counter + 1
Sheets("Data").Cells(Counter, 1) = "C:\My Documents\" & FileItem.Name
Sheets("Data").Cells(Counter, 2) = FileItem.Size
endTime = Timer
runTime = endTime - startTime
Sheets("Data").Cells(Counter + 1, 1) = runTime
Set FSO = Nothing