Copy Folder names into Excel

[bassman71]

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?

[VBA]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[/VBA]

[Erdin? E. Ka]

Hi bassman71

Yes, it is possible, try the code below;

[VBA]
Sub ListOfFolders()

Dim i As Integer
Dim SearchFolders As Variant

i = 1
LookInTheFolder = "C:\TheNameOfYourSearchingFolder" ' As you know; you should modificate this row.
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")

For Each SearchFolders In FileSystemObject.GetFolder(LookInTheFolder).SubFolders
Cells(i, 1) = SearchFolders
i = i + 1
Next SearchFolders

End Sub
[/VBA]

[mdmackillop]

And you can make this code recursive
[vba]
Option Explicit

Dim i As Long, j As Long
Dim searchfolders As Variant
Dim FileSystemObject

Sub ListOfFolders()
Dim LookInTheFolder As String

i = 1
LookInTheFolder = "C:\" ' As you know; you should modificate this row.
Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
For Each searchfolders In FileSystemObject.GetFolder(LookInTheFolder).SubFolders
Cells(i, 1) = searchfolders
i = i + 1
SearchWithin searchfolders
Next searchfolders

End Sub

Sub SearchWithin(searchfolders)
On Error GoTo exits
For Each searchfolders In FileSystemObject.GetFolder(searchfolders).SubFolders
j = UBound(Split(searchfolders, "\"))
Cells(i, j) = searchfolders
i = i + 1
SearchWithin searchfolders
Next searchfolders
exits:
End Sub



[/vba]

[Erdin? E. Ka]

Well done mdmackillop,

I love "Split Function".

[mdmackillop]

I love "Split Function".

Since I came across it, I've found lots of uses for it. It simplifies so many tasks.

[Erdin? E. Ka]

Certainly is it.
Split is very talented function.
I have some experriences about it, but i suppose that i should learn more about it.

[bassman71]

Really cool! Thanks

[Zack Barresse]

What if there is a subfolder in the subfolder? Or a subfolder in that? That's why I like the FileSearch method (although it is being shined away from in the upcoming releases of Office, which I am rather displeased with). You could use the Dir method as well though (prefferred).

[matthewspatrick]

If you need some further details on the files, check out my response to:

http://www.experts-exchange.com/Appl..._22011203.html

[Ken Puls]

What if there is a subfolder in the subfolder? Or a subfolder in that? That's why I like the FileSearch method (although it is being shined away from in the upcoming releases of Office, which I am rather displeased with). You could use the Dir method as well though (prefferred).

Dir has an issue that you should be aware of. If you are using Dir to test the existence of a directory, it returns an incorrect result if there are no files in it. In my experience, it could have 10 subfolders with files in them, but if there is no actual file in that directory, you'll still get back False.

I've documented that in my UDF to check if file/folder exists.

[MKC]

Dear mdmackillop and others,
your modification for Erdin? E. Ka's script looks very interesting. From what I understand, it is meant to search for subfolders within the folders returned by the first sub (ListOfFolders), isn't it? Unfortunately this does not seem to work for me. If I run ListOfFolders via the macro procedure menu, it will find the folders from the upper folder but not the respective subfolders.

Maybe this is, because I did not know exaxtly where to put the

Option Explicit

Dim i As Long, j As Long
Dim searchfolders As Variant
Dim FileSystemObject


Part. And what do mean with "demo purposes"?

All help is highly appreciated. :-)

[mdmackillop]

The limit is removed (post edited accordingly)
Paste the whole of the code in Post #3 into a Standard module.

[MKC]

It works! Which is really strange; the same code in another module works, just as you proposed (not that I had doubted that). In the former module I must have copied the declaration to the wrong position.

Thanks a lot! :-)