VBA - loop through all files *and folders* in a directory

Phil11

New Member
Joined
Jul 8, 2005
Messages
48
Hi everyone!

I'd like to output this:

Please enter a folder!
> C:\MYFILES\RECIPES

Contents of C:\MYFILES\RECIPES
I found a file and its name is chicken1.doc
I found a file and its name is chickensalad.doc
I found a folder and its name is OTHER RECIPES
I found a file and its name is fried cabbage juice.doc
I found a folder and its name is SENT TO MOM
I found a file and its name is yucky meat sauce bad horrible gross.doc

etc.

Order is irrelevant.

The key is, how do you tell whether an item is a file or a folder?

Thanks!
-Phil11
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Phill11

Where would you like this output?
 
Upvote 0
Phil,

I'm a little confused as to what your final outcome is supposed to be here. Would you like for the user to Browse for a folder? Are you looking for a report back or actions to be taken on the file(s)/folder(s) found?
 
Upvote 0
Actually, I think I found the code I'm looking for on J. Walkenbach's site.

The key is to use the DIR() function with an argument of 16, to get it to include subdirectories as well as files.

Thanks!
 
Upvote 0
I personally prefer the Application.FileSearch method. It has a syntax that is inherent (and Boolean - True or False) to include subdirectories in a search. Plus I think it's about the fastest there is for looping through files/folders.
 
Upvote 0
Zack

I think perhaps the File System Object might be useful here.

Application.FileSearch would only return filenames so I think further processing would be needed to identify directories/sub-directories.

With the File System Object you can return folders and files.
 
Upvote 0
Right-o. Just depends on what the OP wants to actually do here, hasn't been too clear. But that's a great distinction to make. Thanks for the clarification Norie. :) (again)
 
Upvote 0
firefytr said:
Just depends on what the OP wants to actually do here, hasn't been too clear.
That's what I'd like to know too, as usual.:)

Actually I think I've got some code that gets all the files in a directory, including sub-directories, then puts them on a sheet.

It then splits out the path into seperate columns.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top