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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Phill11

Where would you like this output?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 

Phil11

New Member
Joined
Jul 8, 2005
Messages
48
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!
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,646
Members
412,285
Latest member
Daibear
Top