Time-out when using `Use UDF Server` with a long-running task

[patrickcourtot]

Hi Felix I just upgraded to v0.3.0. (pip install --upgrade xlwings) and I copied the .bas file in my Excel file. I also update the python program replacing wb = Workbook(workbook_path) with wp=Workbook.caller()

As reminder : Excel 2013, Windows 7, Python Anaconda 3.4

Now when a process is lasting more than a minute I am getting a popup "Microsoft Excel is waiting for another application to complete an OLE action".

Then I have to click many many times on OK until the end of the process. At the end the program is lasting 4:47 min in place of 1:07 with the previous version.

Replacing back to wb = Workbook(workbook_path) changed nothing.

Any idea how get it fixed ? Best regards Patrick

[fzumstein]

Hi Patrick,

would it be possible that you post a minimal code sample to replicate this? Or alternatively, could you send me a sample workbook (email is on my profile).

[fzumstein]

Set OPTIMIZED_CONNECTION = False and you will be fine again. For long running processes, setting this value to True won't have a real impact: With False there is an overhead of starting the Python interpreter that takes a few seconds, but this doesn't hurt if the process itself is 2 minutes. It would probably be useful to introduce a non-blocking option so that Excel can be used during the duration of the Python call.

[ilrico]

Hello, even with OPTIMIZED_CONNECTION= False, I've got the "Microsoft Excel is waiting for another application to complete an OLE action", then I have a 440 Error Automation Error that crashes the python script. Note: seems 440 Error triggers because my script tries to set a cell value after previous error "MXL is waiting for OLE..." If I remove the cell updating function (xl.Range().value = "blabla"), then I just have the "waiting OLE..." message without crashing python script. But I need to update the cell value during script !

I also tried to check box "Excel/File/Options/Advanced/Ignore_other_applications_that_use_DDE without success... still "OLE waiting..." message Ananonda 4, xlwings.version=0.7.0

[fzumstein]

@ilrico can you add a minimal sample that allows us to replicate the issue please, thanks.

[ilrico]

I have a function decorated with @xl.sub that runs a portfolio optimizer for each week. It takes around 3 minutes to compute the whole serie. After 95s I have the "Microsoft Excel is waiting for another application to complete an OLE action" Within the loop I have A) counter += 1 if counter % 10 == 0: pass

B) counter += 1 if counter % 10 == 0: xl.Range('main', 'D2').value = "computing...{0}%".format(int(100 * counter / total_weeks))

In case A, when error message ("...waiting for an OLE action") appears, the script continues in the background, and if you click on the popup, the error message appears again few (10") seconds after, but still script runs smoothly until the end (I can see it because it writes each run into a database) In case B, when error message appears, script breaks (database not more updates) and if you click on popup then you have a second error message: Error 440, Automation error

note: in fact case A also breaks when at the very end there's a xl.Range('main', 'D10').value = result; I have a 440 Error at this time. So symptons seems : after a "...waiting OLE action", each call to write a value into a cell triggers a 440 error and breaks script.

excel is 2013 (v15)

[fzumstein]

For long running tasks, you should use RunPython in VBA instead of using an xw.sub decorator and importing it.

[ilrico]

isn't there a mean to deactivate this timeout "...waiting OLE action" ? it would be cleaner than calling some functions via RunPython and others via UDFs...

[astrojuanlu]

Yes, you have to set the DisplayAlerts properties of the xl_app object to False.

xl.Application(wkb=my_wb).xl_app.DisplayAlerts = False

[ilrico]

@Juanlu001: muchas gracias that workaround works!

[astrojuanlu]

@ilrico You're welcome! :smile:

[ghost]

I used SwitchOffOLEMessage, after adding the following to my VBA code. I am using tensorflow for neural networks with xlwings and if I recalculate a lot of functions with SHIFT+F9 i used to get the "...waiting OLE action" message, not any more...:

Declare Function CoRegisterMessageFilter Lib "OLE32.dll" _
    (ByVal lFilterIn As Long, ByRef lPreviousFilter) As Long
    

Sub SwitchOffOLEMessage()
    CoRegisterMessageFilter 0&, lPreviousFilter
End Sub

Sub SwitchOnOLEMessage()
    CoRegisterMessageFilter lPreviousFilter, 0&
End Sub