VBA - Python - Type Library Finder - use reflection to find a late bound object's containing library

I love finding rare stuff that works in Excel VBA, this blog is full of it! I love finding new useful type libraries of classes which drive functionality I didn't know was possible for a VBA developer. They turn up infrequently on Stack Overflow and other forum websites. Often the sample code creates an object using late binding and not with the type library. When I discover one of these I instantly want to find the containing type library to see what else is in that library. Such an investigation requires

1. Looking up the ProgID (the text string passed to CreateObject()) to get a CLSID
2. Looking up the CLSID to get the LIBID, the type library's guid
3. Looking up the LIBID to get the type library's full name

So for example an interesting component is created with CreateObject("WIA.ImageFile") that has a clsid of {A2E6DDA0-06EF-4df3-B7BD-5AA224BB06E8}, and type library guid of {94A0E92D-43C0-494E-AC29-FD45948A5221} which has the full name of Microsoft Windows Image Acquisition Library v2.0.

Combing though the registry is a pain so fortunately we can write some Python code and use the reflection interfaces, ITypeInfo and ITypeLib which are usually restricted to C++ developers but which Python developers have access to and in turn we can expose to Excel VBA code.

Below is a Python COM Gateway class, I have supplied many examples on this blog so the procedure should be familiar (to regular readers at least). But to recap you need to run the following script with adminstrator privileges from a command console. Once registered, the class becomes creatable from VBA using CreateObject.

class WhichTypeLibrary(object):
    _reg_clsid_ = "{521B0A5A-4359-4874-AA9E-8F99DB35F4A6}"
    _reg_progid_ = 'PythonInVBA.WhichTypeLibrary'
    _public_methods_ = ['ReportTypeLibrary'] 

    def ReportTypeLibrary(self, o):
        try:
            pt = str(type(o))
            if pt == "<class 'win32com.client.CDispatch'>":
                ti = o._lazydata_[0]
            elif pt == "<class 'PyIDispatch'>" :
                ti = o.GetTypeInfo()
            else:
                return "Error, cannot get ITypeInfo interface."

            typelib = ti.GetContainingTypeLib()[0]

            return (typelib.GetDocumentation(-1)[0] + ":\t\t" +  typelib.GetDocumentation(-1)[1] )

        except Exception as e:
            return  "Error: " + str(e) + "\n"

def RegisterThis():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(WhichTypeLibrary)


if __name__ == '__main__':
    RegisterThis()
    print("End of execution")

VBA Client Code

So the calling VBA code is shown below. It shows passing a Workbook object to the library finder which correctly reports it as belonging the Excel type library (that's obvious but it proves the logic is working). A second object is created which is far rarer (certainly I'd not heard of it) and the Python code correctly reports the library prefix, WIA, and the library description, 'Microsoft Windows Image Acquisition Library v2.0', which can then be found in the Tools References dialog box.

Option Explicit

Sub Test()
    On Error GoTo ErrHandler
    Dim oLibFinder As Object
    Set oLibFinder = CreateObject("PythonInVBA.WhichTypeLibrary")
    
    '* a simple example, find the containing type library of a Workbook, should print "Excel:      Microsoft Excel 15.0 Object Library"
    Debug.Print oLibFinder.ReportTypeLibrary(ThisWorkbook)
    
    '* a rarer example, should print "WIA:        Microsoft Windows Image Acquisition Library v2.0"
    Dim obj As Object
    Set obj = CreateObject("WIA.ImageFile")
    
    Debug.Print oLibFinder.ReportTypeLibrary(obj)
    
SingleExit:
    Exit Sub
ErrHandler:
    Debug.Print Err.Description
    Stop
    'Resume
    
End Sub

So the above program prints...

Excel:      Microsoft Excel 15.0 Object Library
WIA:        Microsoft Windows Image Acquisition Library v2.0

Factoid - ProgIDs have limit of 39 characters

What's Next, can the above Python class report on itself? No (or at least not yet).

If you are a little cheeky and wondered what would happen if you pass the library finder class itself to see what it said you would get an error.

Error: (-2147352567, 'Exception occurred.', None, None)

This is because the standard Python COM Gateway class carries no type information by default. It is capable of shipping type information is you associate it with a type library but then the type library needs to agree with the Python source. We can write some code to read a Python class and generate a type library and that will be the subject of the next post.