Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listing Folder Contents into a Column | Excel Worksheet Functions | |||
Listing the contents of a folder | Excel Discussion (Misc queries) | |||
Directory listing | Excel Discussion (Misc queries) | |||
Listing Directory Contents in Worksheet | Excel Discussion (Misc queries) | |||
Directory listing | Excel Discussion (Misc queries) |