Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Microsoft Excel is waiting for an OLE action to complete

  1. #1
    Join Date
    21st April 2003
    Posts
    146

    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:


    VB:
    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 at 12:19.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,837

    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...

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

    VB:
    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 at 18:30. Reason: Added comment re DoEvents

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st April 2003
    Posts
    146

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,837

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    21st April 2003
    Posts
    146

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    1st September 2010
    Posts
    7,837

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

    Glad it works...

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. The connection to Microsoft Exchange is unavailable. Outlook must be online or connected to complete this action.
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: July 4th, 2009, 22:52
  2. the connection to microsoft exchange is unavailable. outlook must be online to complete this action error in Vista
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: May 27th, 2009, 03:40

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno