Create list of folders
 

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

Create list of folders
 

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