in excel, how do i list all folders and last modified dates??

[HHH]

How can i list all folders and last modified dates in a particular location?
for example if the location was c:\temp, i would expect a list of folders
(not files) and the last date modified....?? Does this involve using excel
vba script?

 

[Rick Rothstein]

Yes, as far as I know, you would need a VBA macro to do this. You didn't
tell us where you wanted the list to be, so I had to make a guess. Assuming
your "location" (the c:\tenp you mentioned) is in a cell, select that cell
and this macro will put the directory names in the same column underneath it
and the last modified dates for them will be in the column next to it...

Dim Count As Long
Dim FileName As String
Dim LocationPath As String
LocationPath = ActiveCell.Value
If Right(LocationPath, 1) <> "/" Then LocationPath = LocationPath & "\"
FileName = Dir(LocationPath & "*", vbDirectory)
Count = ActiveCell.Row
Do While Len(FileName)
If (GetAttr(LocationPath & FileName) And vbDirectory) = vbDirectory Then
If FileName <> "." And FileName <> ".." Then
Count = Count + 1
Cells(Count, ActiveCell.Column).Value = FileName
Cells(Count, ActiveCell.Column + 1).Value = _
FileDateTime(LocationPath & FileName)
End If
End If
FileName = Dir()
Loop

 

[HHH]

Hi Rick,

Thanks for your reply.
i copy your text into the vb editor (ALT + F11) in excel press F5 and the
following happens:
A box appears asking the macro name, so i type in a name "userdata", then
press enter.
Then an error message appears (compile error - Invalid outside procedure),
and i can only click OK or help. I click on OK and then "ActiveCell" (4th row
down) is highlighted. Can you help??

I would like colomn A to list folder names and column b to list the last
date modified.
Thanks for your help!

 

[Rick Rothstein]

Sorry, I didn't put the macro wrapper on the code I posted. Below is the
code you should use... it is modified to put the output in Columns A and B,
but you still need to put the "location" (the c:\tenp you mentioned) in cell
C1. Also, you should install your macros into a Module (click Insert/Module
on the VB editor's menu bar).

Sub ListFolders()
Dim Count As Long
Dim FileName As String
Dim LocationPath As String
LocationPath = ActiveSheet.Range("C1").Value
If Right(LocationPath, 1) <> "/" Then LocationPath = LocationPath & "\"
FileName = Dir(LocationPath & "*", vbDirectory)
Count = 0
Do While Len(FileName)
If (GetAttr(LocationPath & FileName) And _
vbDirectory) = vbDirectory Then
If FileName <> "." And FileName <> ".." Then
Count = Count + 1
ActiveSheet.Cells(Count, "A").Value = FileName
ActiveSheet.Cells(Count, "B").Value = _
FileDateTime(LocationPath & FileName)
End If
End If
FileName = Dir()
Loop
End Sub

 

[HHH]

Thanks Rick!
Bang On! Thanks so much for your time!