Moving numpy arrays from VBA to Python and back

Question

I have a VBA script in Microsoft Access. The VBA script is part of a large project with multiple people, and so it is not possible to leave the VBA environment.

In a section of my script, I need to do complicated linear algebra on a table quickly. So, I move the VBA tables written as recordsets) into Python to do linear algebra, and back into VBA. The matrices in python are represented as numpy arrays.

Some of the linear algebra is proprietary and so we are compiling the proprietary scripts with pyinstaller.

The details of the process are as follows:

1. The VBA script creates a csv file representing the table input.csv.
2. The VBA script runs the python script through the command line
3. The python script loads the csv file input.csv as a numpy matrix, does linear algebra on it, and creates an output csv file output.csv.
4. VBA waits until python is done, then loads output.csv.
5. VBA deletes the no-longer-needed input.csv file and output.csv file.

This process is inefficient.

Is there a way to load VBA matrices into Python (and back) without the csv clutter? Do these methods work with compiled python code through pyinstaller?

I have found the following examples on stackoverflow that are relevant. However, they do not address my problem specifically.

Return result from Python to Vba

How to pass Variable from Python to VBA Sub

Answer 1

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