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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>