Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
in excel, how do i list all folders and last modified dates??
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
in excel, how do i list all folders and last modified dates??
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 -- Rick (MVP - Excel) "HHH" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
in excel, how do i list all folders and last modified dates??
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
in excel, how do i list all folders and last modified dates??
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 -- Rick (MVP - Excel) "HHH" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
in excel, how do i list all folders and last modified dates??
Thanks Rick!
Bang On! Thanks so much for your time! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I create folders in explorer from an excel list | Excel Discussion (Misc queries) | |||
How do I find a list of dates a file has been modified? | Excel Discussion (Misc queries) | |||
Generate document folders for Excel list. | Excel Programming | |||
List Folders using Excel | Excel Programming | |||
Can anyone help me Create Excel list of files in windows folders | Excel Worksheet Functions |