Listing the contents of a directory in a spreadsheet
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Listing the contents of a directory in a spreadsheet
 

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

"Angelikoula" wrote in
message ...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

Listing the contents of a directory in a spreadsheet
 

AFAIK worksheets do not have an author but workbooks do.

Sub getauthor()
MsgBox ActiveWorkbook.Author
End Sub

"Angelikoula" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/