将numpy数组从VBA移动到Python并返回 [英] Moving numpy arrays from VBA to Python and back

问题描述

我在Microsoft Access中有一个VBA脚本. VBA脚本是具有多个人员的大型项目的一部分，因此无法离开VBA环境.

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.

在脚本的一部分中，我需要快速在表上进行复杂的线性代数运算.因此，我将写为记录集)的VBA表移到Python中做线性代数，然后回到VBA. python中的矩阵表示为numpy数组.

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.

某些线性代数是专有的，因此我们正在使用 pyinstaller 编译专有脚本.

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. VBA脚本创建一个代表表input.csv的csv文件.
2. VBA脚本通过命令行运行python脚本
3. python脚本将csv文件input.csv作为numpy矩阵加载，对其进行线性代数运算，并创建输出csv文件output.csv.
4. VBA等到python完成后，再加载output.csv.
5. VBA删除不再需要的input.csv文件和output.csv文件.

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.

此过程效率低下.

有没有一种方法可以在没有csv混乱的情况下将VBA矩阵加载到Python中(并返回)?这些方法是否可以通过pyinstaller与已编译的python代码一起使用?

我发现了以下有关stackoverflow的示例.但是，它们没有具体解决我的问题.

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

将结果从Python返回到Vba

如何将变量从Python传递到VBA Sub

推荐答案

解决方案1 ​​

要么检索Access的COM运行实例，然后通过COM API使用python脚本直接获取/设置数据:

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])

解决方案2

或创建一个COM服务器以向Access公开某些功能:

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或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)

请注意，您必须在没有任何参数的情况下运行一次脚本才能注册该类并将其提供给VBA.CreateObject.

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.

两个解决方案都可以使用pylauncher进行操作，并且可以使用numpy.array(data)转换python中接收到的数组.

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

依赖性:

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

这篇关于将numpy数组从VBA移动到Python并返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！