Printable View
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]
Hi bassman71 :hi:
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]
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]
Well done mdmackillop, :thumb
I love "Split Function". :whistle:
Since I came across it, I've found lots of uses for it. It simplifies so many tasks.Quote:
Originally Posted by 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.:)
Really cool! Thanks
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).
If you need some further details on the files, check out my response to:
http://www.experts-exchange.com/Appl..._22011203.html
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.Quote:
Originally Posted by firefytr
I've documented that in my UDF to check if file/folder exists.
:)
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. :-)
The limit is removed (post edited accordingly)
Paste the whole of the code in Post #3 into a Standard module.
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! :-)