本文介绍了如何等待xlwings在vba中完成python代码?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用以下代码从xlwings模块中调用VBA中的RunPython:

  Sub Portfolio_Optimze()
RunPython(import quotes; quotes.get_quote())
SolverAdd CellRef:=$ H $ 18:$ H $ 24,关系:= 1,FormulaText:=$ J $ 18:$ J $ 24
SolverAdd CellRef:=$ B $ 15:$ G $ 15,关系:= 4
SolverOk SetCell:=$ H $ 16,MaxMinVal = 1,ValueOf:= 0,ByChange:=$ B $ 15:$ G $ 15,_
引擎:= 1,EngineDesc:=GRG非线性
SolverSolve

如何使SolverAdd代码等待,直到RunPython代码完成?

解决方案

开始的一些信息:Excel在Mac上只允许外部进程在Excel空闲时写入单元格,即没有宏正在运行。这就是为什么在Mac上运行的 RunPython 调用是作为后台进程运行的,当调用宏完成运行时,该进程才真正进行。



我会建议以下两个解决方案之一:



1)在Python中编程求解器部分,例如使用 scipy.optimize



2)将求解程序VBA放入自己的Sub中,并从Python中调用。这是下一个版本的xlwings的计划功能,请参阅,但您可以现在就这样工作(假设 wb 是你的工作簿):

  app = xlwings.Application(wkb = wb)
app.xl_app.run_VB_macro('SolverMacro')

更新,因为v0.7.1现在被正确支持:

 >> ;> solver_macro = wb.macro('SolverMacro')
>>> solver_macro()


I am calling RunPython in VBA from the xlwings module with the following code:

Sub Portfolio_Optimze()
RunPython ("import quotes; quotes.get_quote()")
SolverAdd CellRef:="$H$18:$H$24", Relation:=1, FormulaText:="$J$18:$J$24"
SolverAdd CellRef:="$B$15:$G$15", Relation:=4
SolverOk SetCell:="$H$16", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$15:$G$15", _
    Engine:=1, EngineDesc:=" GRG Nonlinear "
SolverSolve

How do i make the SolverAdd code wait until the RunPython code is completely finished?

解决方案

Some info to start with: Excel on Mac only allows external processes to write to the cells when Excel is idle, i.e. while no Macros are running. That's why the RunPython call on Mac runs as background process that only really kicks in when the calling macro is finished running.

I would suggest one of the two following solutions:

1) Program the solver part in Python, too, for example using scipy.optimize.

2) Put the solver VBA into its own Sub and call it from Python. This is currently a planned feature for the next version of xlwings, see here, but you can work around for now like so (assuming wb is your Workbook):

app = xlwings.Application(wkb=wb)
app.xl_app.run_VB_macro('SolverMacro')

UPDATE, since v0.7.1 this is now properly supported:

>>> solver_macro = wb.macro('SolverMacro')
>>> solver_macro()

这篇关于如何等待xlwings在vba中完成python代码?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-16 07:41