List File Attributes of Directory and Sub-directories

[BJMcDonald]

This post is concerning a VBA excel macro submitted by Brettdj entitled "List file attributes of directory and sub-directories (http://vbaexpress.com/kb/getarticle.php?kb_id=405)".

I've run this macro in Excel 2007 and it exhibits the following behavior:

1. It will traverse the directories and collect data as expected.
2. However, when there is no further directory and file information it continues on and fills in the all other rows of the spreadsheet up to row 983054 with the text "#N/A"

I'm not sure why this is happening and I haven't yet tried the same macro in and older version of office.

Any ideas?

[mdmackillop]

I've reinstated the sample file. Give it a try. If you still have problems let us know.

[BJMcDonald]

Actually, I made a modification to the code. Excel 2007 increased the maximum rows per worksheet to 1048576. I did a replace of 65536 (the old value of max rows for previous Excel versions) with the new Excel 2007 value. It now does not put in the #N/A values.

Now, however I'm noticing some mismatches on the actual property values that it pulls from the files examined. I think these may have changed as well.

[mdmackillop]

FYI we tend to use Rows.Count instead of an exact number. This keeps code flexible for either version. As you noted, the practise is not universal!

[brettdj]

Actually, I made a modification to the code. Excel 2007 increased the maximum rows per worksheet to 1048576. I did a replace of 65536 (the old value of max rows for previous Excel versions) with the new Excel 2007 value. It now does not put in the #N/A values.

Now, however I'm noticing some mismatches on the actual property values that it pulls from the files examined. I think these may have changed as well.
Hi,

What files are you running it over? For example, MP3 files have different properties under Vista and XP. This link contains property differences between XP and Windows 2000.

http://classicasp.aspfaq.com/files/directories-fso/how-do-i-find-the-owner-author-and-other-properties-of-a-file.html

Regards
Dave

[juanca]

Some properties have changed their "number" in Vista, so I was checking and found that this code works. I apologize for my ignorance since I did not know how to use the "Rows.Count". As you notice, this excerpt just presents the changes in the original code of Brettdj. The labels "Kind +/-", "Perceived Type", and "-rated-" ar just my best gess... and "Status-pend" means I have not find status:

----------------------code:
ReDim X(1 To 104576, 1 To 40)

Set objShell = CreateObject("Shell.Application")
TimeLimit = Application.InputBox("Please enter the maximum time that you wish this code to run for in minutes" & vbNewLine & vbNewLine & _
"Leave this at zero for unlimited runtime", "Time Check box", 0)
StartTime = Timer

Application.ScreenUpdating = False
MainFolderName = BrowseForFolder()
Set NewSht = ThisWorkbook.Sheets.Add

X(1, 1) = "Path"
X(1, 2) = "File Name"
X(1, 3) = "Last Accessed"
X(1, 4) = "Last Modified"
X(1, 5) = "Created"
X(1, 6) = "Type"
X(1, 7) = "Size"
X(1, 8) = "Owner"
X(1, 9) = "Author"
X(1, 10) = "Title"
X(1, 11) = "Comments"
X(1, 12) = "Tag"
X(1, 13) = "Categories"
X(1, 14) = "Program"
X(1, 15) = "Kind +/-"
X(1, 16) = "Perceived Type"
X(1, 17) = "-rated-"
X(1, 18) = "Status-pend"

i = 1

Set FSO = CreateObject("scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(MainFolderName)
'error handling to stop the obscure error that occurs at time when retrieving DateLastAccessed
On Error Resume Next
For Each Fil In oFolder.Files
Set objFolder = objShell.Namespace(oFolder.Path)
Set objFolderItem = objFolder.ParseName(Fil.Name)
i = i + 1
If i Mod 20 = 0 And TimeLimit <> 0 And Timer > (TimeLimit * 60 + StartTime) Then
GoTo FastExit
End If
If i Mod 50 = 0 Then
Application.StatusBar = "Processing File " & i
DoEvents
End If
X(i, 1) = oFolder.Path
X(i, 2) = Fil.Name
X(i, 3) = Fil.DateLastAccessed
X(i, 4) = Fil.DateLastModified
X(i, 5) = Fil.DateCreated
X(i, 6) = Fil.Type
X(i, 7) = Fil.Size
X(i, 8) = objFolder.GetDetailsOf(objFolderItem, 10)
X(i, 9) = objFolder.GetDetailsOf(objFolderItem, 20)
X(i, 10) = objFolder.GetDetailsOf(objFolderItem, 21)
X(i, 11) = objFolder.GetDetailsOf(objFolderItem, 24)
X(i, 12) = objFolder.GetDetailsOf(objFolderItem, 18)
X(i, 13) = objFolder.GetDetailsOf(objFolderItem, 23)
X(i, 14) = objFolder.GetDetailsOf(objFolderItem, 37)
X(i, 15) = objFolder.GetDetailsOf(objFolderItem, 11)
X(i, 16) = objFolder.GetDetailsOf(objFolderItem, 9)
X(i, 17) = objFolder.GetDetailsOf(objFolderItem, 19)
X(i, 18) = objFolder.GetDetailsOf(objFolderItem, 100)
Next
-------------------

By the way, does any one know how to change this setings (for several files at once)

Thanks!

[Michael G]

How do we get a list of the various items we can pull such as tracked changes, author(s), modified by, etc? Since I see "FolderItem 100" in X(i,18) above, I am presuming there is a whole linrary of items we could extract. My quest is to really grab any and all metadata within the documents in a folder and subfolders. Cheers