VBA探险家。exe删除文件属性

I've been doing research on this for weeks now and can't find the solution. It's astounding how seemingly no one before had to remove or set metadata of an .mp4 file by using Excel VBA…

我已经在这方面做了几个星期的研究，但没有找到解决办法。令人惊讶的是，以前似乎没有人使用Excel VBA删除或设置.mp4文件的元数据……

I have an Excel sheet with filenames and file properties (tags, classification, commentary etc.) that I'd like to ascribe to the corresponding files. For instance:

我有一个包含文件名和文件属性(标记、分类、注释等)的Excel表，我想将其归因于相应的文件。例如:

Filename                   Tags               Classification
------------------------------------------------------------
Dad's birthday party.mp4   dad; me; my cat    1 star
Climbing Mt Everest.mp4    me; Jeff; Sam      5 stars

Scripting.FileSystemObject gives me access only to basic stuff like attributes: hidden, archive etc. or date of modification etc.

脚本。FileSystemObject只允许我访问基本内容，如属性:隐藏、存档等或修改日期等。

I figured out the easiest way to set these would be to use Windows Explorer, since it has this marvelous dialog box called 'Properties' — plus, it has an option to 'Remove Properties and Personal Information,' which I'd be keen on using too. Do you guys have any idea on how to hook up Excel with Explorer? Surely there should be a reference that you add to the VBA project?

我找到了一种最简单的方法来设置它们，那就是使用Windows资源管理器，因为它有一个名为“属性”的奇妙对话框，它可以选择“删除属性和个人信息”，我也很乐意使用它。你们知道怎么把Excel和Explorer连在一起吗?当然应该有一个您添加到VBA项目的引用?

Thanks in advance!!…

提前谢谢! !…

1 个解决方案

#1

This is only a partial answer about how to access the properties. It seems that there is no easy way to modify the properties without getting into the nitty-gritty of the specific file type. See this SO question for a discussion of some of the difficulties of setting properties.

这只是关于如何访问属性的部分答案。似乎没有一种简单的方法可以在不了解具体文件类型的情况下修改属性。有关设置属性的一些困难的讨论，请参阅SO问题。

The trick is to not go through the file system object but instead use a Scriptable Shell Object. As that webpage suggests, you need to include references to Microsoft Internet Controls and Microsoft Shell Controls and Automation. Then, the following code (based on this and this) shows how you can access the properties:

诀窍是不要遍历文件系统对象，而是使用可编写脚本的Shell对象。正如那个网页所显示的，你需要包括对微软互联网控制和微软的外壳控制和自动化的参考。然后，下面的代码(基于这个和这个)展示了如何访问属性:

Sub ExtendedFileDetails(FolderName As String, FileName As String)
    Dim myFolder As Folder
    Dim myItem As FolderItem
    Dim myShell As New Shell
    Dim i As Long
    Dim Headers(0 To 34) As Variant

    Set myFolder = myShell.Namespace(FolderName)
    Set myItem = myFolder.ParseName(FileName)

    For i = 0 To 34
        Headers(i) = myFolder.GetDetailsOf(myFolder.Items, i)
    Next i

    For i = 0 To 34
        Debug.Print i & vbTab & Headers(i) & vbTab & myFolder.GetDetailsOf(myItem, i)
    Next i
End Sub

(On edit: I just read somewhere that the properties now exceed 35 -- experiment a bit. In any event -- you probably only want a handful of properties and should be able to Google their actual indices)

(编辑:我刚在什么地方读到，属性现在超过35——再做一点实验。在任何情况下——您可能只想要一些属性，并且应该能够谷歌它们的实际索引)

A test run:

一个测试运行:

Sub test()
    ExtendedFileDetails "C:\Users\jcoleman\Music\Pixies\Surfer Rosa", "07 Where Is My Mind-.wma"
End Sub

Output (slightly edited to remove personal details):

输出(稍微编辑以删除个人信息):

0   Name    07 Where Is My Mind-
1   Size    3.60 MB
2   Item type   Windows Media Audio file
3   Date modified   9/25/2014 9:49 AM
4   Date created    9/25/2014 9:49 AM
5   Date accessed   9/25/2014 9:49 AM
6   Attributes  A
7   Offline status  
8   Offline availability    
9   Perceived type  Audio
10  Owner   ******************
11  Kind    Music
12  Date taken  
13  Contributing artists    Pixies
14  Album   Surfer Rosa
15  Year    1988
16  Genre   Alternative
17  Conductors  
18  Tags    
19  Rating  Unrated
20  Authors Pixies
21  Title   Where Is My Mind?
22  Subject 
23  Categories  
24  Comments    
25  Copyright   
26  #   7
27  Length  00:03:53
28  Bit rate    ?128kbps
29  Protected   No
30  Camera model    
31  Dimensions  
32  Camera maker    
33  Company 
34  File description

#1

Sub ExtendedFileDetails(FolderName As String, FileName As String)
    Dim myFolder As Folder
    Dim myItem As FolderItem
    Dim myShell As New Shell
    Dim i As Long
    Dim Headers(0 To 34) As Variant

    Set myFolder = myShell.Namespace(FolderName)
    Set myItem = myFolder.ParseName(FileName)

    For i = 0 To 34
        Headers(i) = myFolder.GetDetailsOf(myFolder.Items, i)
    Next i

    For i = 0 To 34
        Debug.Print i & vbTab & Headers(i) & vbTab & myFolder.GetDetailsOf(myItem, i)
    Next i
End Sub