objFolder.GetDetailsOf not working

[Indigenous]

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).

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

[snb]

I'd use: (XP; office 2010)

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

[Indigenous]

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.

[snb]

Title

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

[Indigenous]

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.

[Paul_Hossler]

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

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

[Indigenous]

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

[Paul_Hossler]

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

Capture.JPG

Capture1.JPG

[Indigenous]

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.

[Paul_Hossler]

you're using win 7?

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

[Indigenous]

Attached.
Screenshots inside.

[Kenneth Hobs]

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.

[Indigenous]

This one has no data.
Yes Windows 7.

[Kenneth Hobs]

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

[Paul_Hossler]

1. My macro works with Book2.xlsx
Capture.JPG



2. Title is set in the XML inside the workbook

Capture2.JPG


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

Put this in Book2.xlsx and see

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

[Paul_Hossler]

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

 '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

[Indigenous]

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

[Paul_Hossler]

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

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

Capture.JPG

[Indigenous]

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

Put this in Book2.xlsx and see

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

Yes it does show the Title.

[Kenneth Hobs]

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