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

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

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

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

"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.