How to force Excel VBA to use updated COM server

Question

I'm developing a COM server to be used from Excel VBA. When I update the server (edit code, unregister, re-register) Excel seems to carry on using the original version of the COM server, not the updated version. The only way I have found to get it to use the updated version is to close and re-open Excel, which gets a bit irritating. Is there a way to force Excel to use the newly registered version (maybe some kind of "clear cache" option)?

More details:

The server is being developed in Python using win32com.

In VBA I'm doing something like:

set obj=CreateObject("Foo.Bar")
obj.baz()

Where Foo.Bar is the COM server I have registered in the registry.

If I unregister the server then run the VBA code, I get a "can't create object" error from VBA, so it must realise that something is going on. But once I reregister it picks up the old version.

Any hints appreciated!

Thanks,

Andy

Answer 1

Just a suggestion, have you tried to Unload the object? Maybe create a button in your excel spredsheat that force to unload the object.

I hope it helps

Answer 2

I've found a solution to my problem - the general idea is to set things up so that the main COM server class dynamically loads the rest of the COM server code when it is called. So in Python I've created a COM server class that looks something like:

import main_code

class COMInterface:
    _public_methods_ = [ 'method1' ]
    _reg_progid_ = "My.Test"
    _reg_clsid_ = "{D6AA2A12-A5CE-4B6C-8603-7952B711728B}"

    def methods(self, input1,input2,input3):
        # force python to reload the code that does the actual work 
        reload(main_code)
        return main_code.Runner().go(input1,input2,input3)

The main_code module contains the code that does the actual work and is reloaded each time the COM method is called. This works as long as the inputs don't change. There will presumably be a runtime penalty for this, so might want to remove the reload for the final version, but it works for development purposes.