Loop through Folder and Place Folder Names in Excel

[Mantle51]

Hi,

I have the following that places all file names in a folder into a sheet, however I want the folder names within a folder placed into the sheet. Is this possible?

NextRow = 2
With Application.FileSearch
.NewSearch
.LookIn = "W:\Compliance2\2 - Account Approval Process\2 - CORPORATE CLIENTS"
.Filename = "*.*"
.Execute

FileToProcess = .FoundFiles.Count

For i = 1 To .FoundFiles.Count
ThisEntry = .FoundFiles(i)
Cells(NextRow, 1).Value = ThisEntry

NextRow = NextRow + 1

Next i

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/1fddd555-caa0-4f77-851d-0a2d3082e13d.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Mantle51,
I would stick with the [tt]FileSearch[/tt] object since it's faster.

If you want to search sub-folders in the [tt]LookIn[/tt] path just add the following to your routine:

With Application.FileSearch
...
  .SearchSubFolders = True
...

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

I thought the OP wanted subfolder names, not names in subfolders ...

 

[CautionMP]

PHV,
I'm not sure from the post since the [tt]FileSearch[/tt] object returns the complete path and name of a file.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)