This post was prompted by a bounty on a Stack Overflow question which details VBA code that can read file tags; questioner wants to know if VBA can write these tags as well. Sorry to say that I do not know of any current VBA referencable type libraries (there was once a DSOFiles.dll but not on my current machine). Instead we can leverage the comprehensive Python ecosystem and create a Python class callable from Excel VBA, below is a Python listing for the FilePropertiesSetter.py script.
The Python COM gateway class pattern is one I give dozens of times on this blog so I will skip details of that. The key magic is in the FilePropertiesSetter.changeSingleProperty() method which I give now without error handling ...
1 properties = propsys.SHGetPropertyStoreFromParsingName(self.filename,
None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
2 propKey = propsys.PSGetPropertyKeyFromName(propName)
3 newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
4 propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
5 properties.SetValue(propKey,propVariantNewValue)
6 properties.Commit()
So, essentially this code opens up the file's tag property store (line 1). On line 2 we find the property's key from the property's friendly name (e.g. "System.Category"). Line 3 determines if we're dealing we an array or not to help construct the new property. Line 4 constructs the new value in a form acceptable to that particular library. Line 5 actually makes the change. Line 6 commits the change and closes the file store.
The rest of the script is essentially helper methods.
The scripts has a number of libraries and so be prepared for some installation, some pip install commands. One pitfall I had to code for is when user passes in a two dimensional variant array such as if they had lifted values off a block of cells (I think the StackOverflow questioner was wanting this), the code to handle this is in FilePropertiesSetter.ensureList() which also split a comma separated string into an array (list).
FilePropertiesSetter.py needs to run once, and will request Administrator privileges to register the class with the COM registry.
FilePropertiesSetter.py
import os
import pythoncom
from win32com.propsys import propsys, pscon
from win32com.shell import shellcon
from win32com.server.exception import COMException
import winerror
import numpy
class FilePropertiesSetter(object):
_reg_clsid_ = "{5ED433A9-C5F9-477B-BF0A-C1643BBAE382}"
_reg_progid_ = 'MyPythonProject3.FilePropertiesSetter'
_public_methods_ = ['setFilename','setTitle','setKeywords','setCategory','setSubject','setComment']
def __init__(self):
pass
def setFilename(self, filename: str):
if not os.path.exists(filename):
raise COMException(description="Specified file '" + filename + "' does not exist! Subsequent calls will fail!",
scode=winerror.E_FAIL, source = "FilePropertiesSetter")
else:
self.filename = filename
def ensureList(self, arg):
# despite hinting that one pass a string it seems Python will accept a VBA array as a tuple
# so I need to convert tuples to a list, and split csv strings to a list
# and flatten any 2d tables to a list
try:
if type(arg) is tuple:
list2 = list(arg)
if numpy.ndim(list2)>1:
# flatten any two dimension table to a one dimensional list
return [item for sublist in list2 for item in sublist]
else:
return list2
else:
if isinstance(arg,list):
return arg
else:
return arg.split(",")
except Exception as ex:
raise COMException(description="error in ensureList for arg '" + str(arg) + "'\n" + (getattr(ex, 'message', repr(ex))) ,
scode=winerror.E_FAIL, source = "FilePropertiesSetter")
def setTitle(self, title: str):
# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-title
self.changeSingleProperty( "System.Title", title)
def setKeywords(self, keywords: str):
# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-keywords
self.changeSingleProperty( "System.Keywords", self.ensureList(keywords))
def setCategory(self, category: str):
# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-category
self.changeSingleProperty( "System.Category", self.ensureList(category) )
def setSubject(self, subject: str):
# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-subject
self.changeSingleProperty( "System.Subject", subject)
def setComment(self, comment: str):
# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-comment
self.changeSingleProperty( "System.Comment", comment)
def changeSingleProperty(self, propName: str, newValue):
propKey = None
if hasattr(self,'filename') and self.filename is not None:
try:
properties = propsys.SHGetPropertyStoreFromParsingName(self.filename,
None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
except Exception as ex:
raise COMException(description="Could not open properties for file '" + self.filename + "'\n" + (getattr(ex, 'message', repr(ex))) ,
scode=winerror.E_FAIL, source = "FilePropertiesSetter")
try:
propKey = propsys.PSGetPropertyKeyFromName(propName)
except Exception as ex:
raise COMException(description="Could not find property key for property named '" + propName + "'\n" + (getattr(ex, 'message', repr(ex))) ,
scode=winerror.E_FAIL, source = "FilePropertiesSetter")
if propKey is not None:
try:
newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
properties.SetValue(propKey,propVariantNewValue)
properties.Commit()
except Exception as ex:
raise COMException(description="Error whilst setting value ...'\n" + (getattr(ex, 'message', repr(ex))) ,
scode=winerror.E_FAIL, source = "FilePropertiesSetter")
def RegisterThis():
print("Registering COM servers...")
import win32com.server.register
win32com.server.register.UseCommandLine(FilePropertiesSetter)
if __name__ == '__main__':
RegisterThis()
pass
def Test():
propsSetter=FilePropertiesSetter()
propsSetter.setFilename(r"N:\FileProps\Newbook.xlsx") # use your own filename (obviously)
propsSetter.setKeywords("python,keywords")
propsSetter.setCategory("python,category")
propsSetter.setTitle("python title")
propsSetter.setSubject("python subject")
propsSetter.setComment("python comment")
After FilePropertiesSetter.py is run once and correctly registered (don't forget you may need some installation) then it is scriptable from VBA with code like the following ...
Option Explicit
Sub TestPythonClass()
Dim objPropsSetter As Object
Set objPropsSetter = VBA.CreateObject("MyPythonProject3.FilePropertiesSetter")
objPropsSetter.setFilename "N:\FileProps\Newbook.xlsx"
objPropsSetter.setTitle "VBA Title"
objPropsSetter.setSubject "VBA Subject"
objPropsSetter.setComment "VBA Comment"
objPropsSetter.setKeywords "Foo2,bar"
'objPropsSetter.setKeywords Array("Foo1", "bar1")
objPropsSetter.setCategory Sheet1.Range("A1:B2").Value2
End Sub
You will need to call setFilename because that is how the script knows which file to operate upon.
Enjoy!
Good Program it is helpful
ReplyDeletepython training in hyderabad