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

17127

17128

[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
17131



2. Title is set in the XML inside the workbook

17132


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




17133

[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

[Indigenous]

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

[Indigenous]

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.