objFolder.GetDetailsOf not working

The following code lists the files in a selected folder.
It also lists the attribute "Title" by using "objFolder.GetDetailsOf(objFolderItem, 21)".
The code works fine for some formats such as PDF, JPEG, DOC and XLS but not for others such as DOCX, XLSX and DWG.
I am using Windows 7.
"objFolder.GetDetailsOf(objFolderItem, 21)" gives the "Title" attribute in Windows 7 apparently but it differs for Windows xp (10 instead of 21).

Code:

---

Sub TestListFilesInFolder()
    Dim sFolder As FileDialog
        Set sFolder = Application.FileDialog(msoFileDialogFolderPicker)
    If sFolder.Show = -1 Then
        ListFilesInFolder sFolder.SelectedItems(1), True
    End If
End Sub
Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)
  Dim FSO As Object
  Dim SourceFolder As Object
  Dim SubFolder As Object
  Dim FileItem As Object
  Dim r As Long
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
      r = ActiveCell.Row
      For Each FileItem In SourceFolder.Files
        Cells(r, 1).Formula = FileItem.Name
        Cells(r, 2).Formula = GetFileOwner(SourceFolder.Path, FileItem.Name)
        r = r + 1
        X = SourceFolder.Path
      Next FileItem
      If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
          ListFilesInFolder SubFolder.Path, True
        Next SubFolder
      End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub
Function GetFileOwner(ByVal FilePath As String, ByVal FileName As String)
  Dim objFolder As Object
  Dim objFolderItem As Object
  Dim objShell As Object
    FileName = StrConv(FileName, vbUnicode)
    FilePath = StrConv(FilePath, vbUnicode)
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(StrConv(FilePath, vbFromUnicode))
      If Not objFolder Is Nothing Then
        Set objFolderItem = objFolder.ParseName(StrConv(FileName, vbFromUnicode))
      End If
      If Not objFolderItem Is Nothing Then
        GetFileOwner = objFolder.GetDetailsOf(objFolderItem, 21)
      Else
        GetFileOwner = ""
      End If
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Function

---

I'd use: (XP; office 2010)

Code:

---

Sub M_snb()
  Set df = CreateObject("Shell.Application")
   
  With Application.FileDialog(4)
    If .Show = -1 Then
      c00 = .SelectedItems(1)
      For Each it In CreateObject("Scripting.FileSystemObject").GetFolder(c00).Files
        With df.Namespace(c00)
          If .GetDetailsOf(.ParseName(it.Name), 9) <> "" Then c01 = c01 & vbLf & it.Path & "_" & .GetDetailsOf(.ParseName(it.Name), 9)
        End With
      Next
    End If
  End With
   
  MsgBox c01
End Sub

---

I am using Windows 7 with Office 2007.
Changing the value from 9 to 21 does give the Title attribute.
But it still misses the files with formats XLXS, DOCX, DWG, etc.

Title

Code:

---

Sub M_title()
    Set df = CreateObject("Shell.Application")
   
    With Application.FileDialog(4)
      If .Show = -1 Then
            c00 = .SelectedItems(1)
            For Each it In CreateObject("Scripting.FileSystemObject").GetFolder(c00).Files
              With df.Namespace(c00)
                  If .GetDetailsOf(.ParseName(it.Name), 10) <> "" Then c01 = c01 & vbLf & it.Path & "_" & .GetDetailsOf(.ParseName(it.Name), 10)
                End With
            Next
      End If
    End With
   
    MsgBox c01
End Sub

---

This time, value of 10 outputs the Owner.
It still misses the files with formats XLXS, DOCX, DWG, etc.
Also the values need to written into cells rather than into a message box.

The mapping of Property to Index is dependent on the OS, and sometimes the release of the OS (Win 10 just changed some)

I use the property name to find the index and the index with the file to get the value


This returns a MS Word doc as a type.

Writing to cells is left as an exercise to the reader

Code:

---

Option Explicit
Sub test()
   
'    MsgBox FieldNumber("Type")
'    MsgBox FieldNumber("Item Type")
    MsgBox FileProperty(Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop", "test.docx", "Type")
    MsgBox FileProperty(Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop", "test.docx", "Item Type")
End Sub
Function FileProperty(FilePath As String, FileName As String, PropName As String) As String
    Dim objFolder As Object
    Dim objFolderItem As Object
    Dim objShell As Object
    Dim i As Long
   
    FileProperty = vbNullString
    i = FieldNumber(PropName)
    If i = -1 Then Exit Function
   
       
    FileName = StrConv(FileName, vbUnicode)
    FilePath = StrConv(FilePath, vbUnicode)
   
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(StrConv(FilePath, vbFromUnicode))
   
    If Not objFolder Is Nothing Then
        Set objFolderItem = objFolder.ParseName(StrConv(FileName, vbFromUnicode))
    End If
    If Not objFolderItem Is Nothing Then
        FileProperty = objFolder.GetDetailsOf(objFolderItem, i)
    Else
        FileProperty = vbNullString
    End If
   
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Function
 
Private Function FieldNumber(s) As Long
    Dim oFolder As Object
    Dim n As Long
    Dim sDesktop As Variant
   
    s = UCase(s)
   
    sDesktop = CreateObject("wscript.shell").specialfolders(10) & Application.PathSeparator
    Set oFolder = CreateObject("shell.application").Namespace(sDesktop)
   
    n = 0
    On Error GoTo Oops
   
    Do While True
        If s Like UCase(oFolder.GetDetailsOf(oFolder.Items, n)) Then
            FieldNumber = n
            Exit Function
        Else
            n = n + 1
        End If
       
        If n > 1000 Then Exit Do
       
    Loop
Oops:
    Set oFolder = Nothing
    FieldNumber = -1
   
End Function

'list all properties
Private Sub GetDetailsOfFields()
    Dim objShell
    Dim objFolder
    Dim n As Long
   
    Set objShell = CreateObject("shell.application")
    Set objFolder = objShell.Namespace("C:\Users\Daddy\Desktop\")
   
    On Error GoTo Oops
    For n = 0 To 1000
        Worksheets("FileProperties").Cells(n + 1, 1).Value = n
        Worksheets("FileProperties").Cells(n + 1, 2).Value = objFolder.GetDetailsOf(objFolder.Items, n)
    Next n
Oops:
   
    Set objFolder = Nothing
    Set objShell = Nothing
   
End Sub

---

Sorry but it misses the property "Title" for formats XLXS, DOCX, DWG, etc. It does work for PDF.

Are you sure that there is a title on the xlsx? It works for me in Win10

Attachment 17127

Attachment 17128

Yes 100% sure as I have spent some time trying to resolve.
I notice that attribute "Title" shows up in Windows Explorer but if I use an alternative file browser, such as Q-Dir, the attribute does not show up.
In addition to "Title" I am also trying to show "Author" and "Subject". The same problem exists.
As mentioned it does show up for PDF, JPEG, DOC, XLS. Not for DOCX, XLSX, DWG.

you're using win 7?

post an empty (no data) xlsx where the title doesn't show and I'll try

Attached.
Screenshots inside.

Why would you expect a Windows property detail such as Title to always have a value? Some files do but many do not. The GetDetailsOf method is only getting a read-only property value. There is a DLL method that one can use to read and write to "some" Windows property details.

Some Applications like WordPerfect have properties that are not correlated to Windows properties. Sort of like Excel, WordPerfect has built-in application and custom properties.

Just to be PC, a Windows attribute is not a Windows property detail. I would be surprised if Q-Dir does not have an option to show Title like Windows Explorer has. Howsoever, even it does not show up, that has nothing to do with reading a value by GetDetailsOf method. You probably need to contact Q-Dir to report the problem. This is not a Q-Dir support forum.

For testing purposes, it would be best to manually add a value to a Title in Windows Explorer and then try the GetDetailsOf method.

Maybe Paul will do it but it might be good to make some routines to build an array that has the detail property names for the current operation system. One would then use WorksheetFunction.Match to find a match such as title. One can then use the index number plus one to get the integer for the GetDetailsOf method.

As Paul said, if you are 100% sure by manually setting a title value in Windows Explorer, then attach the file and we will test.

This one has no data.
Yes Windows 7.

Those have the title value, Title_e, in my Win7Pro and Win10Home.

1. My macro works with Book2.xlsx
Attachment 17131



2. Title is set in the XML inside the workbook

Attachment 17132


3. Maybe (just maybe) Win7 does not return built in doc properties the same way????

Put this in Book2.xlsx and see

Code:

---

Option Explicit
Sub test()
    MsgBox ThisWorkbook.BuiltinDocumentProperties("Title")
End Sub

---

Quote:

---

Maybe Paul will do it but it might be good to make some routines to build an array that has the detail property names for the current operation system. One would then use WorksheetFunction.Match to find a match such as title. One can then use the index number plus one to get the integer for the GetDetailsOf method.

---

Done in post #6

The attached WB has a macro to list the current OS indexes, and a worksheet with what I copied from the MS KB

Code:

---

'list all properties
Private Sub GetDetailsOfFields()
    Dim objShell
    Dim objFolder
    Dim n As Long
   
    Set objShell = CreateObject("shell.application")
    Set objFolder = objShell.Namespace("C:\Users\Daddy\Desktop\")
   
    On Error GoTo Oops
    For n = 0 To 1000
        Worksheets("FileProperties").Cells(n + 1, 1).Value = n
        Worksheets("FileProperties").Cells(n + 1, 2).Value = objFolder.GetDetailsOf(objFolder.Items, n)
    Next n
Oops:
   
    Set objFolder = Nothing
    Set objShell = Nothing
   
End Sub

---

I just tried manually adding using Windows Explorer. But no luck writing to Excel with either #1 or #6.

Quote:

---

I just tried manually adding using Windows Explorer. But no luck writing to Excel with either #1 or #6.

---

writing?

I believe that GetDetailsOf is ReadOnly


If you're trying to write to the Title (or other building properties) you'll need to do something like this

Code:

---

Option Explicit
Sub test()
    ThisWorkbook.BuiltinDocumentProperties("Title") = "This is new Title"
End Sub

---

Attachment 17133

Quote:

---

Originally Posted by Paul_Hossler

3. Maybe (just maybe) Win7 does not return built in doc properties the same way????

Put this in Book2.xlsx and see

Code:

---

Option Explicit
Sub test()
    MsgBox ThisWorkbook.BuiltinDocumentProperties("Title")
End Sub

---

---

Yes it does show the Title.

If your goal was to write to "some" properties, Paul's method works for Excel.

If you want general usage for all file types like I explained, you can use DSOfile.dll. See Chip's site for more information and code. http://www.cpearson.com/Excel/DocProp.aspx

OK thanks guys it will take me a while to take the above in and getting late in Australia, I will report back tomorrow.

Just to clarify, when I say "write" properties, this is into an Excel cell. Maybe I should use the term "read" from the point of view of the macro.
I can edit these properties through the application or if this is not possible using software such as File Metadata which is like a shell in Windows Explorer (http://filemeta.codeplex.com/). I can see the properties in Windows Explorer but for some reason, and this only applies to some file types such as XLSX and DOCX, some of the properties such as Title, Author and Subject cannot be read while others such as Size can be read. For PDF everything can be read.