excel - VBA从excel写入文件标签

Question

我有一些代码可以列出文件夹中的文件并获取它们的标签:

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

现在我想知道如何将它们写入列表中的那些文件。我基本上是在尝试从 excel 中编写标签。
我在 A 列中有完整的文件名我试图作为每个文件的标签写入的字符串在列 B 中.
文件夹的地址在文本框的值中: UserForm_Tag.FolderLocation_TextBox.value .

Answer 1

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

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

出色地...
我很确定是 不可能通过标准 VBA 方法更改扩展文件属性。我见过 ActiveX 对象，它可以做到这一点，例如:在对问题 How can I change extended file properties using vba 的回答中, 用户 jac建议使用 dsofile.dll .
注:此库仅限于 32 位 WinOS，请参阅: 64 Bit Application Cannot Use DSOfile .更多详情 dsofile.dll你会在这里找到: How to set file details using VBA .最重要的信息是:

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

正如我在对问题的评论中提到的，如果您想更改基本(最常用)扩展文件属性 Excel/Word 文件 ，我建议使用 BuiltinDocumentProperties .一些内置属性对应 extended file properties .例如:


内置文档属性
扩展属性(property)(EP)
EP指数


标题
标题
10

主题
主题
11

作者
作者
9

注释
注释
14

创建日期
创建日期
4

类别
类别
12

公司
公司
30

等等...



枚举所有内置属性:

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

设置内置属性:

Sub SetBuiltinProperties()

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

End Sub

所以...如果您想更改特定工作簿的内置属性，您必须:

打开它，

chage/set 内置属性，

保存它，

并关闭它。