VBA help: need folder names

[mike.tsang]

what i'm looking for is a simple vba code to pull all the folder names
from somewhere on a disk (like C:\). i do not want subfolders listed.
i also want it to list the folder names exactly how it is in the
folder (i have them listed by date modified from oldest to newest).
i'm using excel 2003. thanks for your time.

 

[Chip Pearson]

Try code like the following. You'll need a reference to the Microsoft
Scripting RunTime (in VBA, Tools menu, References item, in that dialog
choose Microsoft Scripting RunTime).

Sub ListFolders()
Dim FSO As Scripting.FileSystemObject
Dim FF As Scripting.Folder
Dim R As Range

Set R = Range("A1") '<<<< CHANGE OUTPUT START CELL
Set FSO = New Scripting.FileSystemObject
For Each FF In FSO.GetFolder("C:\").SubFolders '<<< CHANGE DRIVE SPEC
R(1, 1).Value = FF.Name
R(1, 2).Value = FF.DateLastModified
Set R = R(2, 1)
Next FF
Set R = Range(Range("A1"), R).Resize(, 2)
R.Sort key1:=R(1, 2), order1:=xlAscending, header:=xlNo,
MatchCase:=False
R.Columns(2).Clear
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[mike.tsang]

thanks for your reply Chip. unfortunately, there's a syntax error; the
following is in red:

R.Sort key1:=R(1, 2), order1:=xlAscending, header:=xlNo,
MatchCase:=False

do you know how to fix it?

 

[Gord Dibben]

That is all one line. Add a line-continuation character.

Don't forget the <space> before the _

R.Sort key1:=R(1, 2), order1:=xlAscending, header:=xlNo, _
MatchCase:=False


Gord Dibben MS Excel MVP

 

[mike.tsang]

it works perfectly, many thanks!! =D

 

[mike.tsang]

it works perfectly, many thanks!! =D