Help - Read a Disk Directory to into a Speadsheet?

[Blake]

How can I read a directory from my hard disk directly into a
spreadsheet in Excel (showing me parent directory, filenames, dates,
times, sizes, etc. - like a dos "DIR").

I'd like to do this without any interim steps for making a directory
listing that the user would have to perform (i.e. DOS's "DIR *.*
>FILENAME.TXT").

I can program in VB, but I've never done VBA at all. I could probably
modify a VBA program if it was close, though.

Is this possible? Can someone point me in the right direction?

Any help would be appreciated.

Blake

Note: If replying my email remove the 454 from my address
(SPAM protection).
blak...@bellatlantic.net

[Thomas Ogilvy]

Blake
Here is one approach:

Sub DirectorytoSheet()
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("DirList")
lstAttr = vbNormal + vbReadOnly + vbHidden
lstAttr = lstAttr + vbSystem + vbDirectory
lstAttr = lstAttr + vbArchive
myPath = "c:\" ' Set the path.
myName = Dir(myPath, lstAttr) ' Retrieve the first entry.
sh.Cells(1, 1) = "Path:"
sh.Cells(1, 2) = myPath
sh.Cells(2, 2) = "Name"
sh.Cells(2, 3) = "Date"
sh.Cells(2, 4) = "Time"
sh.Cells(2, 5) = "Size"
sh.Cells(2, 6) = "Attr"
rw = 3

Do While myName <> "" ' Start the loop.
' Ignore the current directory and
' the encompassing directory.
If myName <> "." And myName <> ".." Then
sh.Cells(rw, 2) = myName
sh.Cells(rw, 3) = _
Int(FileDateTime(myPath & myName))
sh.Cells(rw, 4) = _
FileDateTime(myPath & myName) - _
Int(FileDateTime(myPath & myName))
sh.Cells(rw, 5) = _
FileLen(myPath & myName)
fattr = GetAttr(myPath & myName)
strAttr = ""
If fattr <> vbNormal Then '(vbNormal = 0 )
If (fattr And vbReadOnly) Then
strAttr = strAttr & "R"
End If
If (fattr And vbHidden) Then
strAttr = strAttr & "H"
End If
If (fattr And vbSystem) Then
strAttr = strAttr & "S"
End If
If (fattr And vbDirectory) Then
strAttr = strAttr & "D"
End If
If (fattr And vbArchive) Then
strAttr = strAttr & "A"
End If
End If
sh.Cells(rw, 6) = strAttr
rw = rw + 1
End If
myName = Dir ' Get next entry.
Loop
Intersect(sh.Range("A1").CurrentRegion, _
sh.Columns("C:C")).Offset(2, 0).NumberFormat = _
"mm/dd/yy"
Intersect(sh.Range("A1").CurrentRegion, _
sh.Columns("D:D")).Offset(2, 0).NumberFormat = _
"h:mm AM/PM"
End Sub

HTH,
Tom Ogilvy

Blake wrote in message <35cb3ede...@msnews.microsoft.com>...