OLE Error: Excel Waiting on Another Application?

[bjsatola]

[Problem]

I sometimes get a 'Microsoft Excel is waiting for another application to complete an OLE action' notification'. The notification comes with an 'OK' button, but pressing this button seems to have no effect and the workbook effectively hangs on this notification.

[Details]

I am using VBA (via Excel) to run optimisations using an external simulation program.

I have a number of case definitions which are contained in an Access database (I also use it to store the optimisation results).

For small batch sizes the program runs fine without error, but if I leave it for too long on larger batches I get 'Microsoft Excel is waiting for another application to complete an OLE action' notification'.

It seems that the simulation program gets stuck. This is not a big deal if, instead of the notification (which requires me to click 'OK'), I could get VBA to throw an error that could be handled, e.g. like re-establishing the connection to the object and the restarting the optimisation where it left off...

As it stands, I get a notification which only comes with an 'OK' button, which does not seem to do anything. Is there anyway to make the program break instead of giving the notification??

As it stands I have to baby-sit the calculations, and I can only stay awake for so lO_ong T_T. If I could get an error to be thrown instead of the notification, I'm thinking I could run a script that kills the simulation program, and then I could just restart the server and pick-up where I left off? Just my thoughts anyway...

Any help would be greatly appreciated :)

[What I've Tried]

Microsoft's description of the error, 'Excel is waiting for another application to complete an OLE action:'
http://support.microsoft.com/kb/2663513/en-US

The simulation program is called/referenced as an ActiveX automation server, so it seems that this is happening:
'Infinite Loop If OLE Automation Server Bus'
http://support.microsoft.com/kb/112030
But there is no way that I can check if some sort of dialogue is happening, because the visibility is turned off (which needs to be to suppress the programs dialogs). There is not enough time between clicking 'OK' on Excel's notification before the next notification pops-up, i.e. I cannot go into the VBA editor and manually break the code and then turn the visibility on the simulation program on to see what the problem is. The task manager shows that the program is still running...

Someone said (ref: http://answers.microsoft.com/en-us/offi ... 29010f2f67) to try choosing File, Options, Advanced, and under General check Ignore other applications that use DDE, but this just mutes the notification. The program still hangs without throwing any sort of error that can be handled. If I didn't have intermediate results outputted to a text file, I wouldn't even know that the program was in limbo, e.g. I cannot even manually break the routine to see anything when this happens.

[HansV]

I'm not sure you can do anything about that from the Excel side. Does the simulation program have options to suppress message boxes, and perhaps to provide feedback in another way?

[bjsatola]

Yes, after attaching to the simulator I turn the invisibility off which suppresses the message boxes. Now that you mention it, I'll try to check the history file of the program to see if it outputs any error messages the next time it happens :)
Thank you

[bjsatola]

Looked at the error. Seems that the simulation program somehow was loosing its own connection to another (integrated) program. Not too sure why though. I think maybe the number of calls I'm making has something to do with it, like a built up or something, i.e. the OLE error notification seemed to be popping up more or less predictably after around 15 cases...

A quick fix that seems to be working is to kill the application that is resulting in the OLE error every 10 cases or so, go back to the initialization part (which re-establishes the connection), and then pick-up where I left off.

In case someone runs into a similar problem, I just call a quick batch file within the loop which kills all the open process for that application, adjust the starting record and then restart the procedure... Something like:

Code: Select all

    Initialize_Procedure:
    
    '// <Initialization stuff which I omitted>

    For irec = irec_start To nrec
        
        If ((irec - irec_start) > 5) Then
        
            irec_start = irec      '// starting value for loop
            Set oApp = Nothing     '// application object
            
            '// Kill processes of the application
            On Error Resume Next
            VBA.Shell "cscript c:\Temp\TerminateAllProcesses.vbs" '// for example
            On Error GoTo 0
            
            Sleep (5000)                 '// found it useful to add a small pause after killing the application before recalling the initialization procedure
            GoTo Initialize_Procedure    '// start the routine from the beginning, where the loop will then start where it left-off at irec_start
            
        End If

        '// <Other procedural stuff here which I've omitted, e.g. calling optimiser>

    Next irec

Then just write a script file (*.vbs) using a text editor like:

Code: Select all

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
    ("SELECT * FROM Win32_Process WHERE Name = 'apwn.exe'")
For Each objProcess in colProcessList
    objProcess.Terminate()
Next

Which will terminate ALL "apwn.exe" applications currently open when executed. Save it in some directory, e.g. c:\Temp, for later use.
Just need to replace "apwn.exe" with your application executable (e.g. MS Access would be 'msaccess.exe'). Not very pretty maybe, but it works :)

[HansV]

Thanks for posting your solution!