PDA

View Full Version : Copy Folder names into Excel



bassman71
10-02-2006, 12:39 PM
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

Erdin? E. Ka
10-02-2006, 01:09 PM
Hi bassman71 :hi:

Yes, it is possible, try the code below;


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

mdmackillop
10-02-2006, 01:43 PM
And you can make this code recursive

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

Erdin? E. Ka
10-02-2006, 02:01 PM
Well done mdmackillop, :thumb

I love "Split Function". :whistle:

mdmackillop
10-02-2006, 02:24 PM
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
10-02-2006, 03:39 PM
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
10-03-2006, 07:46 AM
Really cool! Thanks

Zack Barresse
10-03-2006, 09:41 AM
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
10-03-2006, 07:32 PM
If you need some further details on the files, check out my response to:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_22011203.html

Ken Puls
10-04-2006, 09:19 AM
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 (http://www.excelguru.ca/node/30).

:)

MKC
07-30-2009, 04:52 AM
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
07-30-2009, 05:09 AM
The limit is removed (post edited accordingly)
Paste the whole of the code in Post #3 into a Standard module.

MKC
07-30-2009, 05:38 AM
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! :-)