VBA和Python之间的IPC

Question

我面临以下问题:在我们公司，我们使用的软件的GUI是用MS Access/VBA编程的。现在，一部分业务逻辑应该转移到Python中，但MS Access部分应该保留。下面的场景现在实现并工作:用户在Access中键入字符串，该字符串在VBA中读出，并通过命令行调用Python脚本，并将字符串作为命令行参数提供给脚本。Python反过来连接到供应商的数据库，使用传递的字符串作为参数，并将结果存储在我们的MS SQL数据库中。供应商为其数据库提供Python API，因此需要通过Python进行必要的中间步骤。这种情况每天发生几次，每次启动脚本或解释器大约需要3秒钟。这花的时间太长了。以下是不需要的:将Python脚本转换为web服务器或使用Python重新编程GUI。

Sub CallPython()

Dim PythonExe As String, PythonScript As String, PythonArgs As String, PythonOutput As String
Dim PythonCommand As String
Dim objShell As Object
PythonExe = """C:Program FilesPython37python.exe"""
PythonScript = """[path_to_our_script]insert_article.py"""
PythonArgs = "-id 123456"
Set objShell = VBA.CreateObject("Wscript.Shell")
PythonCommand = PythonExe & " " & PythonScript & " " & PythonArgs 
'MsgBox PythonCommand
objShell.Run PythonCommand

End Sub

我看过下面关于IPC技术的页面，但我在这方面没有太多的经验，所以我不能说太多的复杂性。有没有人对上述场景有经验，可以分享他们对更智能的解决方案的知识?

Answer 1

这个主题确实非常广泛和复杂。

我自己通过命名管道在R和Access之间使用双向直接通信，这与Python(或R)端处理文件非常相似。然而，Access端需要许多API声明来设置管道，对于我来说，窥探进度以便我们可以在不锁定应用程序的情况下异步报告进度。

关于命名管道的基础知识可以在这里找到:

https://learn.microsoft.com/en-us/windows/win32/ipc/multithreaded-pipe-server

:

https://learn.microsoft.com/en-us/windows/win32/ipc/named-pipe-client

如果只有一个客户端(你的Python应用程序)，命名管道服务器可以是单线程的，所以你可以忽略大多数多线程的东西。

我在VBA中需要的声明是:

Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As LongPtr
bInheritHandle As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As LongPtr
hThread As LongPtr
dwProcessId As Long
dwThreadId As Long
End Type

Private Type STARTUPINFO
cb As Long
lpReserved As LongPtr
lpDesktop As LongPtr
lpTitle As LongPtr
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As LongPtr
hStdInput As LongPtr
hStdOutput As LongPtr
hStdError As LongPtr
End Type

Private Const STARTF_USESHOWWINDOW  As Long = &H1
Private Const STARTF_USESTDHANDLES  As Long = &H100
Private Const SW_HIDE               As Long = 0&
Private Const ERROR_SUCCESS As Long = 0
Private Const STILL_ACTIVE As Long = 259
Private Const PIPE_TYPE_BYTE As Long = 0
Private Const PIPE_ACCESS_INBOUND = 1
Private Const PIPE_ACCESS_OUTBOUND = 2
Private Const PIPE_ACCESS_DUPLEX As Long = 3
Private Const PIPE_WAIT As Long = 0
Private Const PIPE_NOWAIT As Long = 1
Private Const PIPE_ACCEPT_REMOTE_CLIENTS As Long = 0
Private Const ERROR_PIPE_CONNECTED = 535
Private Const ERROR_PIPE_LISTENING = 536
Private Declare PtrSafe Function CreatePipe Lib "kernel32" (ByRef hReadPipe As LongPtr, ByRef hWritePipe As LongPtr, ByVal lpPipeAttributes As LongPtr, ByVal nSize As Long) As Long
Private Declare PtrSafe Function CreateProcess Lib "kernel32" Alias "CreateProcessW" (ByVal lpApplicationName As LongPtr, ByVal lpCommandLine As LongPtr, ByVal lpProcessAttributes As LongPtr, ByVal lpThreadAttributes As LongPtr, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, ByVal lpEnvironment As LongPtr, ByVal lpCurrentDirectory As LongPtr, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long
Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, lpBuffer As Any, ByVal nNumberOfBytesToRead As Long, lpNumberOfBytesRead As Long, ByVal lpOverlapped As LongPtr) As Long
Private Declare PtrSafe Function WriteFile Lib "kernel32" (ByVal hFile As LongPtr, lpBuffer As Any, ByVal nNumberOfBytesToWrite As Long, ByRef nNumberOfBytesWritten As Long, ByVal lpOverlapped As LongPtr) As Long
Private Declare PtrSafe Function FlushFileBuffers Lib "kernel32" (ByVal hFile As LongPtr) As Long
Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, ByRef lpExitCode As Long) As Long
Private Declare PtrSafe Function TerminateProcess Lib "kernel32" (ByVal hProcess As LongPtr, ByVal uExitCode As Long) As Long
Private Declare PtrSafe Function CreateNamedPipeW Lib "kernel32" (ByVal lpName As LongPtr, ByVal dwOpenMode As Long, ByVal dwPipeMode As Long, ByVal nMaxInstances As Long, ByVal nOutBufferSize As Long, ByVal nInBufferSize As Long, ByVal nDefaultTimeOut As Long, lpSecurityAttributes As Any) As LongPtr
Private Declare PtrSafe Function ConnectNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr, lpOverlapped As Any) As Long
Private Declare PtrSafe Function DisconnectNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr) As Long
Private Declare PtrSafe Function PeekNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr, lpBuffer As Any, ByVal nBufferSize As Long, ByRef lpBytesRead As Long, ByRef lpTotalBytesAvail As Long, ByRef lpBytesLeftThisMessage As Long) As Long

VBA部分的基本内容是:

1. 通过CreateNamedPipeW和PIPE_ACCESS_OUTBOUND创建一个命名管道(或者两个，如果你想输入和输出，一个进一个出)
2. 通过CreateProcess生成一个监听进程(你的Python进程)，这样你就可以得到它的ID
3. 发送命令时，通过GetExitCode检查进程是否处于活动状态，通过ConnectNamedPipe连接到管道，使用WriteFile写入管道，然后使用FlushFileBuffers，通过CloseHandle释放文件句柄，然后使用DisconnectNamedPipe断开与管道的连接

从Python进程，在一个循环中，通过open打开管道，读取并处理消息，然后通过open再次打开。open应该停止，直到下一个消息被发送。

如果你想使用返回消息来显示进度，请确保使用PeekNamedPipe在Python运行缓慢或遇到错误时不会停止Access应用程序。

你可能想把所有这些都包装在一个预先声明的自修复VBA类中，以保持你的Python程序在VBA激活时处于活动状态，而不必等待Python启动/读取你的程序等。

最后，使用本地http要简单得多，因为您可以使用已有的工具来发送和接收http请求。但是VBA和Python/R/任何可以读取命名管道(=读取文件)的编程语言之间的直接IPC可以完成。

很遗憾，我只能带你到这里了。真的，重新考虑"而不是"web服务器"。如果进程在同一台机器上，web服务器可以被防火墙，使用web服务器会比使用命名管道有更多的开销，但这要容易得多。我真希望我能走那条路。