如何将Python脚本的变量值传递至Access VBA？

方法1：通过临时文件传递数据（最简单直接） #

无需复杂API，靠文件中转实现数据传递：

• 修改Python脚本，把变量output写入临时文本文件，替代打印到屏幕：

  output = "你需要的变量值"
  # 选一个有读写权限的路径，比如用户文档目录
  with open(r"{}\Documents\py_temp_output.txt".format(os.environ["USERPROFILE"]), "w", encoding="utf-8") as f:
      f.write(output)
  

• 在Access VBA中，执行bat文件后读取临时文件内容：

  Sub GetPythonValue()
      Dim batPath As String
      Dim tempFile As String
      Dim outputValue As String
      Dim fileNum As Integer
      
      batPath = "C:\你的bat文件路径\run_script.bat"
      tempFile = Environ("USERPROFILE") & "\Documents\py_temp_output.txt"
      
      ' 执行bat并等待完成（0表示隐藏窗口，True表示等待执行结束）
      CreateObject("WScript.Shell").Run batPath, 0, True
      
      ' 读取临时文件
      fileNum = FreeFile()
      Open tempFile For Input As #fileNum
      outputValue = Input$(LOF(fileNum), fileNum)
      Close #fileNum
      
      ' 使用获取到的值，比如赋值给表单控件
      Me.txtResult.Value = outputValue
      
      ' 可选：删除临时文件
      Kill tempFile
  End Sub
  

方法2：直接捕获Shell命令输出（无需临时文件） #

通过Windows API重定向命令行输出，直接在VBA中获取Python脚本的打印内容：

1. 在VBA模块顶部声明API函数（注意区分32/64位Access，以下是64位版本）：

   Private Declare PtrSafe Function CreatePipe Lib "kernel32" (phReadPipe As LongPtr, phWritePipe As LongPtr, lpPipeAttributes As Any, ByVal nSize As Long) As Long
   Private Declare PtrSafe Function GetStdHandle Lib "kernel32" (ByVal nStdHandle As Long) As LongPtr
   Private Declare PtrSafe Function SetStdHandle Lib "kernel32" (ByVal nStdHandle As Long, ByVal hHandle As LongPtr) As Long
   Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
   Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, lpBuffer As Any, ByVal nNumberOfBytesToRead As Long, lpNumberOfBytesRead As Long, lpOverlapped As Any) As Long
   Private Declare PtrSafe Function CreateProcessA Lib "kernel32" (ByVal lpApplicationName As String, ByVal lpCommandLine As String, lpProcessAttributes As Any, lpThreadAttributes As Any, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, lpEnvironment As Any, ByVal lpCurrentDirectory As String, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long
   
   Private Type STARTUPINFO
       cb As Long
       lpReserved As String
       lpDesktop As String
       lpTitle As String
       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 Byte
       hStdInput As LongPtr
       hStdOutput As LongPtr
       hStdError As LongPtr
   End Type
   
   Private Type PROCESS_INFORMATION
       hProcess As LongPtr
       hThread As LongPtr
       dwProcessId As Long
       dwThreadId As Long
   End Type
   

2. 编写捕获输出的函数：

   Function CaptureCmdOutput(cmd As String) As String
       Dim si As STARTUPINFO
       Dim pi As PROCESS_INFORMATION
       Dim hReadPipe As LongPtr, hWritePipe As LongPtr
       Dim buffer As String * 4096
       Dim bytesRead As Long
       Dim output As String
       
       CreatePipe hReadPipe, hWritePipe, ByVal 0&, 0
       
       si.cb = Len(si)
       si.dwFlags = &H100 ' 启用标准句柄重定向
       si.hStdOutput = hWritePipe
       si.hStdError = hWritePipe
       
       If CreateProcessA(vbNullString, cmd, ByVal 0&, ByVal 0&, 1, 0, ByVal 0&, vbNullString, si, pi) Then
           CloseHandle hWritePipe
           
           Do
               ReadFile hReadPipe, buffer, Len(buffer), bytesRead, ByVal 0&
               If bytesRead > 0 Then output = output & Left(buffer, bytesRead)
           Loop While bytesRead > 0
           
           CloseHandle pi.hProcess
           CloseHandle pi.hThread
       End If
       
       CloseHandle hReadPipe
       CaptureCmdOutput = Trim(output) ' 清理多余换行/空格
   End Function
   

3. 在按钮点击事件中调用：

   Sub btnRetrieveValue_Click()
       Dim cmd As String
       Dim pyOutput As String
       
       ' 可以直接调用bat或Python脚本
       cmd = "C:\你的bat文件路径\run_script.bat"
       ' 或者直接调用Python：cmd = "python.exe C:\你的脚本路径\test.py"
       
       pyOutput = CaptureCmdOutput(cmd)
       
       ' 使用获取到的值
       Me.txtResult.Value = pyOutput
   End Sub
   

注意事项 #

• 如果用方法2，确保Python脚本只有print(output)这一行输出，避免捕获到多余的日志或换行。
• 临时文件路径要选当前用户有读写权限的目录，避免权限报错。
• 32位Access需将API声明中的PtrSafe移除，LongPtr替换为Long。

内容的提问来源于stack exchange，提问作者gringomachichi