VBA help: need folder names
 

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.

VBA help: need folder names
 

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)


wrote in message
...
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.

VBA help: need folder names
 

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?

VBA help: need folder names
 

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

On Sat, 16 Feb 2008 11:51:05 -0800 (PST), wrote:

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?

VBA help: need folder names
 

it works perfectly, many thanks!! =D

VBA help: need folder names
 

it works perfectly, many thanks!! =D