Calling custom Python COM object from VBA

Question

I have a python script which i converted into a COM server. Now i want to call it from VBA (Access).

I have tried this:

Sub test()
    Dim PyScript
    Dim var

    Set PyScript = CreateObject("PythonDemos.CodeScript")    
    var = PyScript.CodeReader()    
    Debug.Print var    
End Sub

But i get an error at CreateObject(...): Automation error 2147024770

From what i read this means that the module "PythonDemos" can not be found.

Here is the python code:

class Main:
    _public_methods_ = ['CodeReader']
    _reg_progid_ = "PythonDemos.CodeScript"
    _reg_clsid_ = "{B74B241B-0699-4332-8145-145512D332D1}"

    def CodeReader(self, item=None):
        #do stuff here and return values


if __name__ == '__main__':
    win32com.server.register.UseCommandLine(Main)

It runs well on its own and was registered without problems as COM server.

My Question is: How to call this python script correctly from vba? Where is my mistake?

Martin · Accepted Answer · 2014-05-10 18:30:47Z

2

After a lot of trial and error I got it to work. The syntax in VBA was correct, but i updated the Python script. I am not sure, but i think the

pythoncom.CLSCTX_LOCAL_SERVER

did the trick. I was just a little confused because PyCharm told me that it is not a valid command. Just ignored that and expanded the header like this

class Main:
    _reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER
    _reg_clsid_ = "{F223CC90-AB60-442C-BE81-C79701C47059}" 
    _reg_desc_ = "Python Code Reader"
    _reg_progid_ = "PythonDemos.CodeScript"
    _public_methods_ = ['CodeReader', 'test']
    _readonly_attrs_ = []
    _public_attrs_ = []


    def test(self):
        return "Test Erfolgreich"


    def CodeReader(self):
        #calculate and return stuff...


if __name__ == '__main__':
    if hasattr(sys, 'importers'):
        if '--register' in sys.argv[1:] or '--unregister' in sys.argv[1:]:
            win32com.server.register.UseCommandLine(Main)
        else:
            from win32com.server import localserver
            localserver.serve('{F223CC90-AB60-442C-BE81-C79701C47059}')
    else:
        win32com.server.register.UseCommandLine(Main)

I can't yet say if the code at the bottom is working like it is supposed to for an .exe file but for the .py file it is.

answered May 10, 2014 at 18:30

Martin

1651 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Early Over a year ago

in 2023, i get NameError: name 'pythoncom' is not defined. This would be with Python 3.6.

Collectives™ on Stack Overflow

Calling custom Python COM object from VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related