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"
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 Next
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