VBA Write file tags from excel

user_15265213 0
Viewed 746

I have some code that can make a list of files in folder and get their tags:

Option explicit

'Declare variables
Dim ws As Worksheet
Dim i As Long
Dim FolderPath As String
Dim objShell, objFolder, objFolderItem As Object
Dim FSO, oFolder, oFile As Object
 
Application.ScreenUpdating = False
Set objShell = CreateObject("Shell.Application")

Set ws = ActiveWorkbook.Worksheets("Sheet1") 'Set sheet name

Worksheets("Sheet1").UsedRange.ClearContents
ws.Range("A1:D1").Value = Array("FileName", "Tags", "Subgroup", "Group")

Set FSO = CreateObject("scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(FolderLocation_TextBox.Value)

i = 2 'First row to print result

For Each oFile In oFolder.Files

'If any attribute is not retrievable ignore and continue
On Error Resume Next
    Set objFolder = objShell.Namespace(oFolder.Path)
    Set objFolderItem = objFolder.ParseName(oFile.Name)
    
    ws.Cells(i, 1) = oFile.Name
    ws.Cells(i, 2).Value = objFolder.GetDetailsOf(objFolderItem, 18) 'Tags
    ws.Cells(i, 5).Value = objFolder.GetDetailsOf(objFolderItem, 277)   'Description
    i = i + 1
On Error Resume Next
Next

And now I'm wondering how to write them to those files I get in the list. I am basically trying to write tags from excel.

I have a full filename in column A and a string I'm trying to write as a tag to each file is in column B.
The address of the folder is in the value of a textbox: UserForm_Tag.FolderLocation_TextBox.value.

tags vba excel
1 Answers

There's a set of Workbook.BuiltindocumentProperties you can change via VBA. You can also add custom property to CustomDocumentProperties. Is that what you want? Note: built-in properties are displayed in file properties (file explorer). – Maciej Los ... hours ago

Yes but how do I write the property from excel? – Eduards ... hours ago

Well...

I'm pretty sure it's impossible to change extended file properties via standard VBA methods. I have seen ActiveX object, which can do that, for example: in the answer to the question How can I change extended file properties using vba, user jac do recommend to use dsofile.dll.

Note: this library is limited to 32bit WinOS, see: 64 Bit Application Cannot Use DSOfile. More details about dsofile.dll you'll find here: How to set file details using VBA. The most important information is:

With VBA (DSOFile) you can only set basic file properties and only on NTFS. Microsoft has discontinued the practice of storing file properties in the secondary NTFS stream (introduced with Windows Vista) as properties saved on those streams do not travel with the file when the file is send as attachment or stored on USB disk that is FAT32 not NTFS.

As i mentioned in the comment to the question, if you want to change basic (most common used) extended file property for Excel/Word file, i'd suggest to use BuiltinDocumentProperties. Some built-in properties correspond to extended file properties. For example:

BuiltinDocumentProperty Extended Property (EP) EP Index
Title Title 10
Subject Subject 11
Author Author 9
Comments Comments 14
Creation Date Date created 4
Category Category 12
Company Company 30
and so on...

To enumerate all built-in properties:

Sub GetBuiltinProperties()
    Dim wsh As Worksheet, bdc As DocumentProperty
    Dim i As Long
    
    Set wsh = ThisWorkbook.Worksheets(2)
    On Error Resume Next
    i = 2
    For Each bdc In ThisWorkbook.BuiltinDocumentProperties
        wsh.Range("A" & i) = bdc.Name
        wsh.Range("B" & i) = bdc.Value
        i = i + 1
    Next
    Set wsh = Nothing

End Sub

To set built-in property:

Sub SetBuiltinProperties()

    With ThisWorkbook.BuiltinDocumentProperties
        .Item("Keywords") = "My custom tag"
        .Item("Comments") = "My custom description"
    End With

End Sub

So... If you want to change built-in property for specific workbook, you have to:

  • open it,
  • chage/set built-in property,
  • save it,
  • and close it.
user_21420280
Related