BoB Sho BoB Sho - 1 year ago 87 Question

Get excel file properties?

Is there a way to distinguish a excel file base on other properties like attributes and not by it's file name? For example, save a excel file and also have a unique attribute that file so that if i wanted to query for that attribute, i can?

I'm saving a excel file with the category attribute: "myCategory"

ThisWorkbook.BuiltinDocumentProperties("category").Value = "myCategory"
ThisWorkbook.SaveAs "myfile.xlsm"

How to query the property category from the file, how do i do that?

Answer Source

Say you have a Workbook for which you set its "Subject" property with BuiltinDocumentProperties like so:

ThisWorkbook.BuiltinDocumentProperties("Subject").Value = "testTag"

Now you could query the items in a folder for existence of this tag like so:

Dim objShell As Object, objFolder As Object, objFile As Object
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\Your\Path\Here")

For Each objFile In objFolder.Items
    Dim tagStr As String
    tagStr  = objFile.ExtendedProperty("DocSubject")
    If tagStr = "testTag" Then
        ' Do Stuff
        Debug.Print objFile.Name & " - " & tagStr 
    End If

I've tested this personally and it works to check the metadata of a saved file through the Windows API, without having to create an instance of Excel.

Do note that I'm using "Subject" (and its connected property for Office Documents, "DocSubject") because I couldn't get "Category" to work as expected. The category gets set correctly in the metadata (it's viewable in Windows Explorer) but I think querying it with .ExtendedProperty("Category") wasn't retrieving the right thing - kind of like "DocSubject" is used to retrieve a "Subject" specified by BuiltinDocumentProperties.