vba from author - Using VBA to get extended file attributes

2 Answers

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) 

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)

Quickly for a single file/attribute:

Const PROP_COMPUTER As Long = 56

With CreateObject("Shell.Application").Namespace("C:\HOSTDIRECTORY")
    MsgBox .GetDetailsOf(.Items.Item("FILE.NAME"), PROP_COMPUTER)
End With
excel filesystemobject

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.

'Extended file stributes
'visual basic .net sample 

Dim sFile As Object
        Dim oShell = CreateObject("Shell.Application")
        Dim oDir = oShell.Namespace("c:\temp")

        For i = 0 To 34
            TextBox1.Text = TextBox1.Text & oDir.GetDetailsOf(oDir, i) & vbCrLf
            For Each sFile In oDir.Items
                TextBox1.Text = TextBox1.Text & oDir.GetDetailsOf(sFile, i) & vbCrLf
            TextBox1.Text = TextBox1.Text & vbCrLf