Hi all

I am trying to create a list of folders but not sub folders in excel.

For example, If i have a folder with the path c:\test\

and in test there are three folders ("Folder1, Folder2, Folder3") and
Folder1 has a folder in in called "Folder1.1" i want the output to be

c:\test\folder
c:\test\folder1
c:\test\folder2
c:\test\folder3

I have the following code from this forum. HOw do i ammend it to not
include sub folders?

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

That look like some of my code. If you just want the immediate subfolders
of a given folder without listing the nest subfolders, use code like the
following:

Sub AAA()
Dim FSO As Scripting.FileSystemObject
Dim Rng As Range
Dim FolderName As String
Dim SubF As Scripting.Folder

FolderName = InputBox("Enter a folder name.")
If FolderName = vbNullString Then
Exit Sub
End If
If Dir(FolderName, vbDirectory) = vbNullString Then
MsgBox "Folder not found."
Exit Sub
End If
Set Rng = Range("A1")
Set FSO = New Scripting.FileSystemObject

For Each SubF In FSO.GetFolder(FolderName).SubFolders
Rng.Value = SubF.Path
Set Rng = Rng(2, 1)
Next SubF
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"kaiser" wrote in message
oups.com...
Hi all

I am trying to create a list of folders but not sub folders in excel.

For example, If i have a folder with the path c:\test\

and in test there are three folders ("Folder1, Folder2, Folder3") and
Folder1 has a folder in in called "Folder1.1" i want the output to be

c:\test\folder
c:\test\folder1
c:\test\folder2
c:\test\folder3

I have the following code from this forum. HOw do i ammend it to not
include sub folders?

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