Moving numpy arrays from VBA to Python and back

Solution 1

Either retrieve the COM running instance of Access and get/set the data directly with the python script via the COM API:

VBA:

Private Cache

Public Function GetData()
  GetData = Cache
  Cache = Empty
End Function

Public Sub SetData(data)
  Cache = data
End Sub

Sub Usage()
  Dim wshell
  Set wshell = VBA.CreateObject("WScript.Shell")

  ' Make the data available via GetData()'
  Cache = Array(4, 6, 8, 9)

  ' Launch the python script compiled with pylauncher '
  Debug.Assert 0 = wshell.Run("C:\dev\myapp.exe", 0, True)

  ' Handle the returned data '
  Debug.Assert Cache(3) = 2
End Sub

Python (myapp.exe):

import win32com.client

if __name__ == "__main__":

  # get the running instance of Access
  app = win32com.client.GetObject(Class="Access.Application")

  # get some data from Access
  data = app.run("GetData")

  # return some data to Access
  app.run("SetData", [1, 2, 3, 4])

Solution 2

Or create a COM server to expose some functions to Access :

VBA:

Sub Usage()
  Dim Py As Object
  Set Py = CreateObject("Python.MyModule")

  Dim result
  result = Py.MyFunction(Array(5, 6, 7, 8))
End Sub

Python (myserver.exe or myserver.py):

import sys, os, win32api, win32com.server.localserver, win32com.server.register

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) :
    return [(1,2), (3, 4)]


def register(*classes) :
  regsz = lambda key, val: win32api.RegSetValue(-2147483647, key, 1, val)
  isPy = not sys.argv[0].lower().endswith('.exe')
  python_path = isPy and win32com.server.register._find_localserver_exe(1)
  server_path = isPy and win32com.server.register._find_localserver_module()

  for cls in classes :
    if isPy :
      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_)
    else :
      file_path = sys.argv[0]
      class_name = '%s.%s' % (cls.__module__, cls.__name__)
      command = '"%s" %s' % (file_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")

    print('Registered ' + cls._reg_progid_)


if __name__ == "__main__":
  if len(sys.argv) > 1 :
    win32com.server.localserver.serve(set([v for v in sys.argv if v[0] == '{']))
  else :
    register(MyModule)

Note that you'll have to run the script once without any argument to register the class and to make it available to VBA.CreateObject.

Both solutions work with pylauncher and the array received in python can be converted with numpy.array(data).

Dependency :

https://pypi.python.org/pypi/pywin32