Announcement

Collapse
No announcement yet.

Microsoft Excel is waiting for an OLE action to complete

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Microsoft Excel is waiting for an OLE action to complete

    This message occurs occasionally when I am controlling Word from Excel. But when it occurs I have to use Task Manager to shutdown Excel. I think I know why it happens. It probably happens when the user closes the Word document during the While Wend line:


    Code:
    Set wordapp = GetObject(, "Word.Application")
      
    While wordapp.Application.BackgroundPrintingStatus <> 0
    Wend
      
    wordapp.ActiveDocument.Close savechanges:=False
    If wordapp.Documents.Count = 0 Then wordapp.Quit False
    Set wordapp = Nothing
      
    AppActivate "Microsoft Excel"
      
    End If
    
    Exit Sub
    
    printbox_err:
    
    If Err.Number = 462 Then 'remote server does not exist - word closed
      'Resume Next
      Set wordapp = Nothing
      Application.EnableEvents = True
      End
    End If
    
    If Err.Number = 429 Then 'can't create object as word closed
      Set wordapp = Nothing
      Application.EnableEvents = True
      End
    End If
    
    End Sub
    Most of the time if the user shuts Word during background printing then the While Wend line generates error 462 and it closes OK. But sometimes I get this OLE waiting message. I read online that it may be because Word is partially still in memory. Is there anything I can do? Turning off DisplayAlerts will just mean Excel freezes with no warnings I would say.
    Last edited by Dave Hawley; September 16th, 2010, 12:19.

  • #2
    Re: Microsoft Excel is waiting for an OLE action to complete

    This may not be a complete answer, but...

    Instead of using Late Binding, use early binding along the the 'WithEvents' keyword. In case that's double dutch, it simply means declaring an object as a type rather then just 'Dim x' or 'Dim x as variant'

    For example...

    Code:
    Dim WithEvents WordApp as Word.Application
    Then when you want to actually instantiate the object...

    Code:
    Set WordApp = New Word.Application
    I can't remember offhand if 'Set wordapp = GetObject(, "Word.Application")' when using WithEvents will grab any running instance of Word or not, but I'd guess it does.

    You'll have to set a reference to 'Microsoft Word X.X Object Library' using the Tools/References menu first.

    The advantage is that now all the Word events will be available to you. In the 'General' Dropdown at the top of the Code window will be a Reference to WordApp. If you select that, all the Word Application Events will be available in the Declarations drop-down.

    These events will be fired whenever the appropriate event in Word fires, so you can trap the WordApp.BeforeClose/Quit events and cancel them if necessary.

    The same logic can be applied to a variable with Type 'WordDocument' (Dim WithEvents X as New Word.Document).

    Of course, the problem you mentioned is an exception rather than the rule, but if the user kills Word, the BeforeClose event should fire even if a portion of the Word object is still in memory - and Word leaks memory like just about nothing else...

    As an afterthought, I've just noticed the loop WhileBackgroundPrintingStatus <> 0. Put a DoEvents in that otherwise VBA will hog your machine resources - and you never know what else might come to light...

    Hope this helps...

    J
    Last edited by cytop; September 16th, 2010, 18:30. Reason: Added comment re DoEvents

    Comment


    • #3
      Re: Microsoft Excel is waiting for an OLE action to complete

      Thanks very much for your detailed answer. I did not know about DoEvents, I just read up on it, I will put that in. I will also try your other suggestions as I would like to stop the user prematurely closing the Word window.

      Comment


      • #4
        Re: Microsoft Excel is waiting for an OLE action to complete

        You'll certainly be able to cancel any attempt to close Word using that code - I know, I do it all the time

        If your original problem still causes problems, post back.

        J

        Comment


        • #5
          Re: Microsoft Excel is waiting for an OLE action to complete

          I have implemented everything you said and it works, stopping Word from being closed. Hopefully this means no more OLE messages. I will post if they reoccur. Thanks again for your help, much appreciated.

          Comment


          • #6
            Re: Microsoft Excel is waiting for an OLE action to complete

            Glad it works...

            Comment

            Working...
            X