Calling Excel macros/Excel Solver from Python over com

Question

I have a macro inside a 2007 xlsm file that, when called, creates a solver configuration (target, constraints, ...) and performs the solve without any user interaction. When run manually from Excel, this works fine.

I now want to script this from Python over com. However, when the Python code calls the solver macro:

app.Run("runSolver()")

It fails with:

foobar.xlsm failed to solve, error message: Error in model. Please verify that all cells and constraints are valid.

If I set a breakpoint in my Python environment at the call to Run() and then run the macro manually from Excel, it works fine, so the solver configuration can't be wrong.

The error message is listed on the solver website but I don't think it applies as the sheet solves fine manually.

This page suggests that the solver environment is not yet set up when calling through com. However, adding

Application.Run "Solver.xla!Solver.Solver2.Auto_open"

As the first line of my solver macro results in the more generic:

  File "c:\python26\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x6.py", line 35061, in Run
    , Arg26, Arg27, Arg28, Arg29, Arg30
  File "c:\python26\lib\site-packages\win32com\client\__init__.py", line 456, in _ApplyTypes_
        self._oleobj_.InvokeTypes(dispid, 0, wFlags, retType, argTypes, *args),
com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

So what is the right thing to do?

Edit:

I isolated it down to a reproducable test case:

Python code:

from win32com.client import Dispatch
if __name__ == '__main__':
    app = Dispatch("Excel.Application")
    app.Visible = True
    app.Workbooks.Open(r'C:\path\to\testsolver.xlsm')
    app.Run("runsolver()")

Excel file: http://dl.dropbox.com/u/3142953/testsolver.xlsm (you can open it with Macros disabled and inspect the simple sub in module1 to verify it's safe).

dgorissen · Accepted Answer · 2011-07-21 11:40:55Z

4

I finally found a solution after posting on the MSDN Excel Developers forum. And the solution was maddingly trivial:

Instead of running the solver like so:

app.Run("runsolver()")

You apparently have to drop the brackets and run it like so:

app.Run("runsolver")

Go figure.

answered Jul 21, 2011 at 11:40

dgorissen

6,3253 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling Excel macros/Excel Solver from Python over com

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related