Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Filename list into Excel
Hi,
I would welcome help with the following problem: I need to generate a spreadsheet that contains the filename, date created and date modified (at least) for all files in a folder. I'm not interested in the content of theses files, I just need the file properties in a spreadsheet. Any suggestions as to how to achieve this will be greatly appreciated. Regards, Fred |
#2
|
|||
|
|||
I do this all the time
Go to DOS prompt, Dir full-path\*.*c:\filelist.txt Use Excel to open this file Text to column -- Regards PY & Associates "Fred" wrote in message ... Hi, I would welcome help with the following problem: I need to generate a spreadsheet that contains the filename, date created and date modified (at least) for all files in a folder. I'm not interested in the content of theses files, I just need the file properties in a spreadsheet. Any suggestions as to how to achieve this will be greatly appreciated. Regards, Fred |
#3
|
|||
|
|||
Tricky! The Dir command in a command prompt will list your files with a date
& time, but by default this is the last modified date (I think). You can direct the output to a file with Dir drive:\path\*.*c:\filenames.txt /on The /on switch exports the data in alphabetical order, then use Excel's data import facility (fixed width). You can also add the /tc switch to show the creation date or the /tw switch to show the last written date. Unfortunately it is not possible bybthis method to show both together. One way round this would be to use one switch and import the data, then use the other and import that starting on the same row, but at a different column. As long as the /on switch is used, the filenames will be in the same order in both output files. You can then delete the column with duplicate data. -- Ian -- "Fred" wrote in message ... Hi, I would welcome help with the following problem: I need to generate a spreadsheet that contains the filename, date created and date modified (at least) for all files in a folder. I'm not interested in the content of theses files, I just need the file properties in a spreadsheet. Any suggestions as to how to achieve this will be greatly appreciated. Regards, Fred |
#4
|
|||
|
|||
Fred,
Here is one way Option Explicit Private cnt As Long Private arfiles Private level As Long Sub Folders() Dim i As Long Dim sFolder As String Dim iStart As Long Dim iEnd As Long Dim fOutline As Boolean arfiles = Array() cnt = -1 level = 1 sFolder = "C:\myTest" ReDim arfiles(4, 0) If sFolder < "" Then SelectFiles sFolder Application.DisplayAlerts = False On Error Resume Next Worksheets("Files").Delete On Error GoTo 0 Application.DisplayAlerts = True Worksheets.Add.Name = "Files" With ActiveSheet For i = LBound(arfiles, 2) To UBound(arfiles, 2) If arfiles(0, i) = "" Then If fOutline Then Rows(iStart + 1 & ":" & iEnd).Rows.Group End If With .Cells(i + 1, arfiles(4, i)) .Value = arfiles(1, i) .Font.Bold = True End With iStart = i + 1 iEnd = iStart fOutline = False Else .Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(4, i)), _ Address:=arfiles(0, i), _ TextToDisplay:=arfiles(1, i) .Cells(i + 1, arfiles(4, i) + 1) = arfiles(2, i) .Cells(i + 1, arfiles(4, i) + 2) = arfiles(3, i) iEnd = iEnd + 1 fOutline = True End If Next .Columns("A:Z").AutoFit End With End If 'just in case there is another set to group If fOutline Then Rows(iStart + 1 & ":" & iEnd).Rows.Group End If ActiveSheet.Outline.ShowLevels RowLevels:=1 ActiveWindow.DisplayGridlines = False End Sub '----------------------------------------------------------------------- Sub SelectFiles(Optional sPath As String) '----------------------------------------------------------------------- Static FSO As Object Dim oSubFolder As Object Dim oFolder As Object Dim oFile As Object Dim oFiles As Object Dim arPath If FSO Is Nothing Then Set FSO = CreateObject("SCripting.FileSystemObject") End If If sPath = "" Then sPath = CurDir End If arPath = Split(sPath, "\") cnt = cnt + 1 ReDim Preserve arfiles(4, cnt) arfiles(0, cnt) = "" arfiles(1, cnt) = arPath(level - 1) arfiles(4, cnt) = level Set oFolder = FSO.GetFolder(sPath) Set oFiles = oFolder.Files For Each oFile In oFiles cnt = cnt + 1 ReDim Preserve arfiles(4, cnt) arfiles(0, cnt) = oFolder.Path & "\" & oFile.Name arfiles(1, cnt) = oFile.Name arfiles(2, cnt) = oFile.DateCreated arfiles(3, cnt) = oFile.DateLastModified arfiles(4, cnt) = level + 1 Next oFile level = level + 1 For Each oSubFolder In oFolder.Subfolders SelectFiles oSubFolder.Path Next level = level - 1 End Sub #If VBA6 Then #Else '-----------------------------**-----------------------------*-*------ Function Split(Text As String, _ Optional Delimiter As String = ",") As Variant '-----------------------------**-----------------------------*-*------ Dim i As Long Dim sFormula As String Dim aryEval Dim aryValues If Delimiter = vbNullChar Then Delimiter = Chr(7) Text = Replace(Text, vbNullChar, Delimiter) End If sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & """}" aryEval = Evaluate(sFormula) ReDim aryValues(0 To UBound(aryEval) - 1) For i = 0 To UBound(aryValues) aryValues(i) = aryEval(i + 1) Next Split = aryValues End Function '--------------------------------------------------------------------------- Public Function InStrRev(stringcheck As String, _ ByVal stringmatch As String, _ Optional ByVal start As Long = -1) '--------------------------------------------------------------------------- Dim iStart As Long Dim iLen As Long Dim i As Long If iStart = -1 Then iStart = Len(stringcheck) Else iStart = start End If iLen = Len(stringmatch) For i = iStart To 1 Step -1 If Mid(stringcheck, i, iLen) = stringmatch Then InStrRev = i Exit Function End If Next i InStrRev = 0 End Function '----------------------------------------------------------------- #End If -- HTH Bob Phillips "Fred" wrote in message ... Hi, I would welcome help with the following problem: I need to generate a spreadsheet that contains the filename, date created and date modified (at least) for all files in a folder. I'm not interested in the content of theses files, I just need the file properties in a spreadsheet. Any suggestions as to how to achieve this will be greatly appreciated. Regards, Fred |
#5
|
|||
|
|||
"Fred" wrote in message ... Hi, I would welcome help with the following problem: I need to generate a spreadsheet that contains the filename, date created and date modified (at least) for all files in a folder. I'm not interested in the content of theses files, I just need the file properties in a spreadsheet. Any suggestions as to how to achieve this will be greatly appreciated. Regards, Fred If you have Outlook installed. Navigate to the folder that you want to garner the information from. Customize the current view to include the fields that you desire then copy and paste into Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't publish Excel list to Sharepoint | Excel Discussion (Misc queries) | |||
How to create a Sub validation list in excel? | Excel Discussion (Misc queries) | |||
Excel List range, filter arrows disappeared | Excel Discussion (Misc queries) | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) | |||
convert excel list to pivot table | Excel Discussion (Misc queries) |