Listing the contents of a directory in a spreadsheet

[Angelikoula]

Hi.

I am using the code below to list the contents of a particular
directory. I would like to add a column, where the AUTHOR of the file
is listed as well. Can someone, please, tell me what the attribute of
the FileItem class is for this ? I tried .CreatedBy and .Author, but
none of them worked and my help files are corrupted. Please, help me.

Here is the code:

Sub TestListFilesInFolder()
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("A:H").Clear
With Range("A1")
Formula = "Folder contents:"
Font.Bold = True
Font.Size = 12
End With
Range("A3").Formula = "File Name and Path:"
Range("B3").Formula = "Author:"
Range("C3").Formula = "Date Created:"
Range("D3").Formula = "Version / Last Updated:"
Range("E3").Formula = "Status (Active / Info only):" 'Not filled
automatically
Range("F3").Formula = "Brief Description:" 'Not filled
automatically
Range("G3").Formula = "File Type:"
Range("H3").Formula = "If restricted access, please indicate:" 'Not
filled automatically
Range("A3:H3").Font.Bold = True

ListFilesInFolder "F:\", True ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As
Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Path & FileItem.Name
Cells(r, 2).Formula = FileItem. *** THIS IS WHERE I WANT TO PUT
THE AUTHOR OF THE FILE ***
Cells(r, 3).Formula = FileItem.DateCreated
Cells(r, 4).Formula = FileItem.DateLastModified
Cells(r, 7).Formula = FileItem.Type
' Cells(r, 7).Formula = FileItem.Attributes
' Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("B:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub

Mny thanks,
Angelikoula

 

[Bernie Deitrick]

Angelikoula,

"Author" is not a standard property of files. For MS Office files
such as Excel Spreadsheets, you can access the file property author,
using code like:

ActiveWorkbook.BuiltinDocumentProperties("Author")

I believe the file must be open to read that property, so you would
need to open each file, read the property, then close it.

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

AFAIK worksheets do not have an author but workbooks do.

Sub getauthor()
MsgBox ActiveWorkbook.Author
End Sub