Access VBA <-win32 com server-> python script

Question

I've copied the example code https://inneka.com/programming/vba/moving-numpy-arrays-from-vba-to-python-and-back/ (solution 2) to a VBA Sub and a python script.

import sys, os, win32api, win32com.server.localserver, win32com.server.register 
import numpy as np

class MyModule(object):

  _reg_clsid_ = "{5B4A4174-EE23-4B70-99F9-E57958CFE3DF}"
  _reg_desc_ = "My Python COM Server"
  _reg_progid_ = "Python.MyModule"
  _public_methods_ = ['MyFunction']

  def MyFunction(self, data) :
      arr = np.array(data) + 1
      return arr

def register(*classes) :
    regsz = lambda key, val: win32api.RegSetValue(-2147483647, key, 1, val)
    python_path = 'C:/Program Files (x86)/Python/Python37-32/python.exe'
    server_path = 'C:/Program Files (x86)/Python/Python37-32/Lib/site-packages/win32com/server/localserver.py'

    for cls in classes :
        file_path = sys.modules[cls.__module__].__file__
        class_name = '%s.%s' % (os.path.splitext(os.path.basename(file_path))[0], cls.__name__)
        command = '"%s" "%s" %s' % (python_path, server_path, cls._reg_clsid_)

    regsz("SOFTWARE\\Classes\\" + cls._reg_progid_ + '\\CLSID', cls._reg_clsid_)
    regsz("SOFTWARE\\Classes\\AppID\\" + cls._reg_clsid_, cls._reg_progid_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_, cls._reg_desc_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\LocalServer32', command)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\ProgID', cls._reg_progid_)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOM', class_name)
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\PythonCOMPath', os.path.dirname(file_path))
    regsz("SOFTWARE\\Classes\\CLSID\\" + cls._reg_clsid_ + '\\Debugging', "0")

if __name__ == "__main__":
    register(MyModule)

Edit: Added python_path and server_path manually.

Initializing the com server works fine (just run the python script). Also runing a simple function that returns 1 works. When i import numpy it crashes in vba and results in this error message:

Error # -2147467259 was generated by Python COM Server Internal Error
Error Line: 0
Unexpected Python Error: Traceback (most recent call last):
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\win32com\server\policy.py", line 136, in CreateInstance
    return retObj._CreateInstance_(clsid, reqIID)
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\win32com\server\policy.py", line 194, in _CreateInstance_
    myob = call_func(classSpec)
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\win32com\server\policy.py", line 728, in call_func
    return resolve_func(spec)(*args)
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\win32com\server\policy.py", line 717, in resolve_func
    module = _import_module(mname)
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\win32com\server\policy.py", line 736, in _import_module
    __import__(mname)
  File "C:/Users/*user*/python/19-10-29_VBA_python\myserver.py", line 4, in <module>
    import numpy as np
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\numpy\__init__.py", line 140, in <module>
    from . import _distributor_init
  File "C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages\numpy\_distributor_init.py", line 34, in <module>
    from . import _mklinit
ImportError: DLL load failed: Das angegebene Modul wurde nicht gefunden.

Edit: Don't know why it takes this path (this is a 64bit application) and not the defined one: C:\Users\*user*\AppData\Local\Continuum\anaconda3\envs\pyApp\lib\site-packages

Firstly, can you please show us the script you're _actually_ using? Your pasted script does not contain the `import numpy as np` line that's visible in the traceback. — AKX, Oct 29 '19 at 09:20
Secondly, can you show us the output of `print(sys.path)` and `print(os.environ['PATH'])` before the attempted numpy import? I think the import/DLL resolution paths are incorrect in the COM context. Another option is that you're attempting to load an x86 (32-bit) DLL in an x86_64 (64-bit) context, or vice versa. — AKX, Oct 29 '19 at 09:22
You should reduce your example to a *mcve* ([\[SO\]: How to create a Minimal, Reproducible Example (reprex (mcve))](https://stackoverflow.com/help/mcve)). At 1st glance this doesn't have anything to do with *VBA* or any related wrapper used. It looks strictly related to *NumPy*. Check whether it's reproducible with a trivial script `import numpy` try to update it to its latest version (`pip install -upgrade numpy`). — CristiFati, Oct 29 '19 at 09:22
@AKX , edited your first hint. Secondly, the output of the prints is quite long ... so i've added the python_path and the server_path. There is a python 32bit and 64bit on the machine. Access is 32 bit ... as posted, it takes the path to a 64bit python application. Don't understand why its not solved by run the script with a py32 app. — incognito, Oct 29 '19 at 10:21
@CristiFati i've tried to reduce my example. I don't understand why u think it's only numpy related. — incognito, Oct 29 '19 at 10:23
The point in wanting to see `sys.path` and `os.environ['PATH']` was to see if they seemed correct for importing extension modules. Either way, another question: What do you _actually_ need to do? You're probably already having to marshal/unmarshal data as it roundtrips between Access and your COM server; why not use e.g. a subprocess and `sys.stdin`/`sys.stdout`? — AKX, Oct 29 '19 at 11:05
Target: python Script that can take a list of arguments and return the output in a vba session. Alternatively you can use argv [i] as input and print () as output. In Access you can use a shell object for this.. I thought the com server might be easier. — incognito, Oct 29 '19 at 12:16

incognito · Answer 1 · 2019-10-31T07:45:30.700

Could not find a solution for the Com Server. But there is still a solution for the exchange....

python32 sets the Path temporary and pyscripts contains a project folder

Public Const python32 = "cmd.exe /k @echo off && SET PATH=C:\Program Files (x86)\Python\Python37-32;C:\Program Files (x86)\Python\Python37-32\Scripts && python "

In access i run the following function:

Public Function RunPython(scriptsubfolder As String, input_args As Variant) As Variant

    Dim oExec As Object, oOutput As Object, oShell As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim s As String, sLine As String, scmd As String
    Dim i As Integer

    Set oShell = CreateObject("WScript.Shell")

    'define the command string with globalvars
    scmd = python32 & pyscripts & scriptsubfolder

    For i = LBound(input_args) To UBound(input_args)
        scmd = scmd & " " & str(input_args(i))
    Next i

    'execute the shell command
    Set oExec = oShell.Exec(scmd)

    'handle the results as they are written to and read from the StdOut object
    Set oOutput = oExec.StdOut
    While Not oOutput.AtEndOfStream
        sLine = oOutput.ReadLine
        If sLine <> "" Then s = s & sLine & vbCrLf
    Wend

    RunPython = s

End Function

The following python function could be triggered:

import sys

def main(inp1, inp2):
        print(inp1 + inp2)
        print(inp1 * inp2)

if __name__ == '__main__':

    inp1 = int(sys.argv[1])
    inp2 = int(sys.argv[2])

    main(inp1, inp2)

The solution works fine, but unfortunately I can't see any output in the cmd window. Only a black screen is waiting till i close the window. Does anyone have an explanation for this?

Access VBA <-win32 com server-> python script

1 Answers1