List folders but not sub folders

[kaiser]

Hello all

I am trying to list the folders in a path but not the sub folders.
For example, if i have c:\test\ with folders F1, F2, F3 in it, and in
folder F3 is folder F3.1 i want the output (in excel worksheet) to be

c:\test\f1
c:\test\f2
c:\test\f3

and not have ctest\f3.1 listed as a folder

I have used the code below from this forum - any advice how to amend
it?

Thanks

Set a reference (in VBA, Tools menu, References item) to
Microsoft Scripting Library and use code like the following:


Sub Start()
Dim FSO As Scripting.FileSystemObject
Dim TopFolder As Scripting.Folder
Set FSO = New Scripting.FileSystemObject
Set TopFolder = FSO.GetFolder("C:\Temp")
DoOneFolder TopFolder
End Sub


Sub DoOneFolder(F As Scripting.Folder)
Dim OneFolder As Scripting.Folder
'
' do something with F
Debug.Print F.Path


For Each OneFolder In F.SubFolders
DoOneFolder OneFolder
Next OneFolder
End Sub

 

[Joel]

just eliminate the recursive call

Delete these 3 lines
For Each OneFolder In F.SubFolders
DoOneFolder OneFolder
Next OneFolder

 

[Arvi Laanemets]

Hi

Here is an UDF I use to get n-th subfolder from a specified folder. (The UDF
is meant to use as Excel worksheet function, the parameter MyTime allows to
turn the funtion volatile at will.) Probably

Public Function GetSubfolder(MyFolder As String, FolderNum As Integer,
Optional MyTime As Date)
Dim fs, f, f1, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function