VBA Make Explorer.exe Remove File Properties

Question

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…

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:

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.

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?

Thanks in advance!!…

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

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:

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)

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