当Python可执行的VBA宏触发时，无法使用GLPK求解器

发布于 2025-02-04 04:35:13 字数 2185 浏览 4 评论 0原文

我正在尝试将混合整数线性编程（MILP）优化工具与Excel和Python开发。带有GLPK求解器的PYOMO用于MILP优化。 Python程序从Excel文件中读取输入，并将输出写入Excel文件。我使用Pyinstaller创建EXE文件。当EXE文件按预期打开时，该程序起作用。

问题 - 我想使用Excel文件中的VBA宏触发EXE文件。当使用shell（）命令从vba宏触发exe文件时，程序给出以下错误 - “ valueerror：无法为solver glpk。使用name = glpk -4.65 \ w64 \ glpsol.exe设置可执行文件。存在或不可行。

当直接使用EXE时，该程序运行良好，并且相同的程序通过VBA宏触发EXE文件时会出现上述错误。请帮助解决此问题。

编辑：

调用EXE

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

NewFilePath的VBA代码的一部分是EXE文件的位置。

请注意，直接执行EXE时，GLPK求解器起作用。当从VBA宏中调用时，Python程序的其他部分除GLPK外，还可以使用。唯一不起作用的是当从宏调用EXE时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!

原文

I am trying to develop an Mixed Integer Linear Programming (MILP) optimization tool with Excel and Python. Pyomo with GLPK solver are being used for MILP optimization. Python program reads inputs from the excel file and write output to an excel file. I used Pyinstaller to create exe file. The program works when exe file is opened as expected.

The Problem -
I want to trigger the exe file using VBA Macros from the excel file. When the exe file is triggered from the VBA Macro using the Shell() command, the program gives following error - "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."

The program works well when exe is directly used and the same program gives the above error when exe file is triggered through VBA Macro. Please help solving this problem.

Edit:

Part of VBA code that calls the exe

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

NewFilePath is the location of the exe file.

Please note, GLPK solver works when the exe is executed directly. When called from VBA macro, other sections of python program also works except for the GLPK. The only thing that does not work is GLPK when the exe is called from Macro.

Error Message -

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

被你宠の有点坏 2025-02-11 04:35:13

我弄清楚了这个问题。 VBA触发了CMD默认文件夹的EXE文件。 Python/Pyomo在同一文件夹中寻找求解器。从VBA，通过转到EXE和GLPK求解器来自CMD的文件夹并触发EXE使程序正常工作。以下是@Tim Williams的更改

With CreateObject("WScript.Shell")
        Cmd1 = "cd " & DistFolder
        Cmd2 = """" + NewFilePath + """"
        Connector = " & "
        Commands = "cmd.exe /K " & Cmd1 & Connector & Cmd2
        .Run Commands, 1, True
    End With

可能是正确的。

I figured out the problem. VBA triggers exe file from the cmd default folder. Python/Pyomo looks for the solver in the same folder. From VBA, by going to the folder in which exe and GLPK solver are from cmd and triggering the exe make the program work. Following is the change

With CreateObject("WScript.Shell")
        Cmd1 = "cd " & DistFolder
        Cmd2 = """" + NewFilePath + """"
        Connector = " & "
        Commands = "cmd.exe /K " & Cmd1 & Connector & Cmd2
        .Run Commands, 1, True
    End With

@Tim Williams might be right with his comment.

回复收藏 0 原文

~没有更多了~