Question posted 2011 · +11 upvotes
Trying to use Excel VBA to capture all the file attributes from files on disk, including extended attributes. Was able to get it to loop through the files and capture the basic attributes (that come from the file system):
- File Path
- File Name
- File Size
- Date Created
- Date Last Accessed
- Date Last Modified
- File Type
Would also like to capture the extended properties that come from the file itself:
- Author
- Keywords
- Comments
- Last Author
- Category
- Subject
And other properties which are visible when right clicking on the file.
The goal is to create a detailed list of all the files on a file server.
Accepted answer +15 upvotes
You say loop .. so if you want to do this for a dir instead of the current document;
Dim sFile As Variant
Dim oShell: Set oShell = CreateObject("Shell.Application")
Dim oDir: Set oDir = oShell.Namespace("c:foo")
For Each sFile In oDir.Items
Debug.Print oDir.GetDetailsOf(sFile, XXX)
Next
Where XXX is an attribure column index, 9 for Author for example. To list available indexes for your reference you can replace the for loop with;
for i = 0 To 40
debug.? i, oDir.GetDetailsOf(oDir.Items, i)
Next
2 code variants in this answer
- Variant 1 — 7 lines, starts with
Dim sFile As Variant - Variant 2 — 3 lines, starts with
for i = 0 To 40
Top vba Q&A (6)
- Difference between Visual Basic 6.0 and VBA +122 (2009)
- VBA – how to conditionally skip a for loop iteration +116 (2011)
- VBA: Test if string begins with a string? +53 (2013)
- html parsing of cricinfo scorecards +47 (2012)
- Code to loop through all records in MS Access +46 (2011)
- Access VBA | How to replace parts of a string with another string +44 (2011)
vba solutions on this site
.