VBA宏调用Python打包exe时GLPK求解器运行失败问题

• 基于Excel与Python开发混合整数线性规划（Mixed Integer Linear Programming, MILP）优化工具，优化逻辑采用Pyomo框架搭配GLPK求解器实现
• Python程序负责从Excel文件读取输入数据，求解完成后将结果写回Excel；已通过PyInstaller将Python程序打包为exe可执行文件，直接双击运行该exe时所有功能运行符合预期

• 需要通过Excel内置VBA宏触发该exe文件运行，先后尝试Shell()命令、WScript.Shell对象的Run方法调用exe，调用所用VBA代码如下：

With CreateObject("WScript.Shell")
        .Run """" + NewFilePath + """", 1, True
    End With

• 代码中NewFilePath变量存储exe文件的完整路径。经VBA触发运行exe时，程序抛出GLPK求解器相关错误：

ValueError: Failed to set executable for solver glpk. File with name=glpk-4.65\w64\glpsol.exe either does not exist or it is not executable. To skip this validation, call set_executable with validate=False.

• 测试验证结论：直接运行exe时GLPK求解器可正常工作；VBA触发exe时，除GLPK求解器调用环节外，其余Python程序逻辑均可正常执行，仅GLPK求解器无法正常加载，完整报错信息如下：

WARNING: Failed to create solver with name '_glpk_shell': Failed to set
    executable for solver glpk. File with name=glpk-4.65\w64\glpsol.exe either
    does not exist or it is not executable. To skip this validation, call
    set_executable with validate=False.
Traceback (most recent call last):
  File "pyomo\opt\base\solvers.py", line 152, in __call__
  File "pyomo\solvers\plugins\solvers\GLPK.py", line 119, in __init__
  File "pyomo\opt\solver\shellcmd.py", line 55, in __init__
  File "pyomo\opt\solver\shellcmd.py", line 100, in set_executable
ValueError: Failed to set executable for solver glpk. File with name=glpk-4.65\w64\glpsol.exe either does not exist or it is not executable. To skip this validation, call set_executable with validate=False.
Traceback (most recent call last):
  File "optimization.py", line 459, in <module>
  File "pyomo\opt\base\solvers.py", line 105, in solve
  File "pyomo\opt\base\solvers.py", line 122, in _solver_error
RuntimeError: Attempting to use an unavailable solver.

The SolverFactory was unable to create the solver "_glpk_shell"
and returned an UnknownSolver object.  This error is raised at the point
where the UnknownSolver object was used as if it were valid (by calling
method "solve").

The original solver was created with the following parameters:
        executable: glpk-4.65\w64\glpsol.exe
        type: _glpk_shell
        _args: ()
        options: {}
[15784] Failed to execute script 'optimization' due to unhandled exception!

报错核心是进程工作目录不匹配：VBA调用exe时，默认继承Excel程序的工作目录（通常为Office安装目录，路径类似C:\Program Files\Microsoft Office\root\Office16），而非exe自身的存放目录。代码中GLPK求解器路径写的是相对路径glpk-4.65\w64\glpsol.exe，程序会在当前工作目录下查找该文件，自然匹配失败；直接双击exe时，系统默认将工作目录设为exe所在文件夹，因此可以正常加载求解器。

按部署便捷度从高到低排序：

• 方案1：修改Python源码，固定求解器的绝对路径，重新打包
  在初始化Pyomo求解器前，先获取程序自身的运行目录，拼接出glpsol.exe的完整绝对路径传入，不受外部调用时的工作目录影响。参考代码：

  import os
  import sys
  from pyomo.environ import SolverFactory
  
  # 兼容开发脚本运行、PyInstaller打包后运行两种场景，获取程序所在目录
  if getattr(sys, 'frozen', False):
      app_path = os.path.dirname(sys.executable)
  else:
      app_path = os.path.dirname(os.path.abspath(__file__))
  # 拼接GLPK求解器的完整路径
  glpk_exe_path = os.path.join(app_path, "glpk-4.65", "w64", "glpsol.exe")
  # 初始化求解器时显式传入绝对路径
  solver = SolverFactory("glpk", executable=glpk_exe_path)
  

  修改完成后重新用PyInstaller打包exe即可，无需调整现有VBA代码。
• 方案2：调整VBA调用逻辑，启动exe前先切换工作目录到exe所在文件夹
  不需要修改Python代码，在VBA中调用exe前，先将WScript.Shell的当前目录设为exe所在路径，再执行exe即可。参考代码：

  Dim exeFullPath As String
  Dim exeFolder As String
  exeFullPath = NewFilePath ' 替换为存储exe完整路径的变量
  ' 提取exe所在的文件夹路径
  exeFolder = Left(exeFullPath, InStrRev(exeFullPath, "\"))
  With CreateObject("WScript.Shell")
      .CurrentDirectory = exeFolder ' 切换工作目录
      .Run """" & exeFullPath & """", 1, True
  End With
  

• 方案3：将GLPK求解器所在的glpk-4.65\w64路径添加到部署电脑的系统环境变量PATH中，Pyomo会自动从系统路径检索求解器，不需要代码中指定路径。该方案需要每台使用工具的电脑手动配置环境变量，部署成本较高，适合固定设备使用的场景。

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