Aquiring Directory names

[aking1987]

Good afternoon all,

Would like to know how to...

1. Aquire a list of folder names
2. Add the list to worksheets("").range("")
3. Add the list from the range in the worksheet to a combobox

EG:

Press a button it looks in EXAMPLE location: "X:/EXAMPLE/EXAMPLE/"
Then returns the list of directories within this location. The list i
then added to a worksheet, and then to a combobox.

Thank you in advance

 

[Bob Phillips]

Here is some code to get a list of all folders into a range. To add it to a
combobox, link the cb to the range.

Dim FSO As Object
Dim cnt As Long
Dim arfiles

Sub Folders()
Dim i As Long
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

arfiles = Array()
cnt = 0

sFolder = "C:\myTest"
ReDim arfiles(1)
If sFolder <> "" Then
SelectFiles sFolder
Worksheets.Add.Name = "Folders"
With ActiveSheet
For i = LBound(arfiles) To UBound(arfiles)
.Cells(i + 1, 1) = arfiles(i)
Next
.Columns("A:Z").EntireColumn.AutoFit
End With
End If

End Sub

'-----------------------------------------------------------------------
Sub SelectFiles(Optional sPath As String)
'-----------------------------------------------------------------------
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

ReDim Preserve arfiles(cnt)
arfiles(cnt) = sPath
cnt = cnt + 1

Set Folder = FSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
SelectFiles fldr.Path
Next

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)