VBA: How to Get a Spreadsheet of File Names

VBA Header

I recently found a really cool article from Software Solutions Online that shows you how to get a list of files in a folder or a list of subfolders in a folder. All the things, basically.

This weekend, I’m adapting that slightly to get a complete list of files within a folder including everything in its subfolders. I know. This is the sort of thing that’s either really interesting or really not, depending on how much you use VBA.

Step 1: Get Some Input

The first thing I want to modify is adding a user input box so that a person can just give Excel the file path, and it’ll spit out all the files within it.

Dim inputVariable As Variant
inputVariable = InputBox(“What folder?“)

That pushes the user input into a variable creatively named inputVariable.

Step 2: Make All the Objects

Dim objFSO As Object
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
‘Imma be honest. No idea what this does, but it doesn’t work without it
Dim objFolder As Object
Set objFolder = objFSO.GetFolder(File Path)
‘This one makes a folder object so you can access its properties(like the files inside it)
Dim objFile As Object
‘This variable will hold the files so you can access their properties(like their names)

Step 3: Start the Loop

i = 1
For Each objFile In objFolder.Files
‘For each file in the object folder
Cells( i + 1, 1) = objFile.Name
‘Get the name and pop it in row i+1, column 1
Cells(i + 1, 2) = objFile.Path
‘Get the name and pop it in row i+1, column 2
i = i + 1

‘Add 1 and run the loop on the next file
Next objFile

Step 4: Add a Loop for Subfolders within that Folder

x = UsedRange.Rows.Count
‘Go to the last row
For Each Folder In objFolder.SubFolders
‘For each subfolder within the object folder
Set objFolder = objFSO.GetFolder(Folder)
‘Set the object folder equal to that subFolder
For Each objFile In objFolder.Files
‘For each file in that subfolder
Cells(x + 1, 1) = objFile.Name
‘Get the name and pop it in row i+1, column 1
Cells(x + 1, 2) = objFile.Path
‘Get the name and pop it in row i+1, column 2
x = x + 1
‘Add 1 and run the loop on the next file
Next objFile
‘Add 1 and run the loop on the next folder
Next Folder

Then alls you gotta do is code it into a button and run it.

Screenshot

And this should be the result:

Result

So here’s the problem. Now I have a button that will get all the files within a folder and its subfolders, but what about the subfolders within those subfolders? I’ve decided that’s next week’s problem.

Advertisements
Tagged , , , , ,

4 thoughts on “VBA: How to Get a Spreadsheet of File Names

  1. ARJ says:

    planning to learn more vba from here. keep it up 👌

    Liked by 1 person

  2. ARJ says:

    closing following updates ✋

    Like

  3. ARJ says:

    ok also goood

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: