Excel is waiting for a Word document to complete an OLE action

Question

I have created a macro that reads an Excel source file and loads all of the data in to arrays. Then it opens a Word document used as a template. There are 7 bookmarks in the template. Each bookmark is updated with data from a specific line in the array. Next, using "save as", the template is saved with a unique name and 2 pages. These steps are repeated until there are 100 unique Word documents. So far so good.

(Side note: the macro currently stops at 100 documents because of the issue I've been having. I originally started at 200 documents but changed to 100 because I thought that number (200 docuuments / 400 pages) was the issue.)

Next, the macro closes the Word template and opens another Word document called "MainDoc". This is formatted in a specific way so macro can insert each unique Word document (2 pages) into the "MainDoc" file. Then the macro uses "Save As" to save the "MainDoc" file as a PDF document 200 pages.

Occasionally and not every time, the macro will throw an error message that says: "Microsoft Excel is waiting for "MainDoc.doc - Word" to completed an OLE action."

It doesn't happen every time or at the same place in the code. Sometimes it doesn't throw an error at all and continues on to the next batch of 100 records.

When I click "OK" on the error message, the macro continues running until either it throws it again or the macro completes.

Currently the macro repeats this process and creates 13 unique PDF files. The goal is for the macro to run without any issues until completion.

Here is the code where the error message is displayed:

Sub Create_PDF_File()
    
    Set WrdApp = CreateObject("Word.Application")
    WrdApp.Visible = True
    
    vTmp2 = ""
    vTmp2 = FilePath1 & "MainDoc.doc"
    
    Set WrdDoc = WrdApp.Documents.Open(vTmp2)
    
    FirstRecHolder = 0
    FirstRecHolder = FirstRec
    
   Z = 1
    
   Do Until FirstRec > LastRec
    
        DoEvents
        
        WrdDoc.Activate

'       FIRST LINE WHERE THE ERROR MESSAGE IS DISPLAYED
        WrdApp.Selection.InsertFile Filename:=FilePath2 & "Flyer Row " & FirstRec & ".doc", Range:="", _
            ConfirmConversions:=False, Link:=False, Attachment:=False
        
        Z = Z + 1
        FirstRec = FirstRec + 1
        
    Loop
    
    Z = Z - 1
    
    PageCount = 0
    PageCount = (Z * 2)
    PageCount = PageCount + 1
        
    WrdApp.Selection.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Count:=PageCount

    WrdApp.Selection.Delete
    
    WrdApp.DisplayAlerts = wdAlertsNone

'   SECOND LINE WHERE THE ERROR MESSAGE IS DISPLAYED
    WrdApp.ActiveDocument.SaveAs2 Filename:=FilePath2 & "Flyers_" & PgCnt & ".pdf", _
        FileFormat:=wdFormatPDF, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14

    WrdDoc.Close (False)
    
    FirstRec = 0
    FirstRec = FirstRecHolder
    
    Do Until FirstRec > LastRec
    
        DoEvents
        
        Kill FilePath2 & "Flyer Row " & FirstRec & ".doc"
        
        FirstRec = FirstRec + 1
        
    Loop
    
    FirstRec = LastRec + 1
    
End Sub

I noticed each of these lines has a path and a file name. Would I be better off combining the path and file name before executing this line of code?

Filename:=FilePath2 & "Flyer Row " & FirstRec & ".doc" Filename:=FilePath2 & "Flyers_" & PgCnt & ".pdf"

Does it matter if the Word document has 200 or 400 pages when saving it to a PDF file? I'd like 400 pages as the client would like 4 - 6 PDF files and 200 records / 400 pages gets us there.

I turn DisplayAlets off and on when the unique Word documents are being created but I don't use it when the PDF file is being created. Should I?

I also notice that there is a message in the lower right hand corner of Word that says "Word is publishing "Flyer_(unique number).pdf and there is a running count of the pages numbers. Is this have any bearing on the issues i'm having.

Is there a better way to do what I'm trying to do with Excel, Word, and PDF?

Any suggestions on getting this code to run with out stopping would be greatly appreciated. I sure would like to kick this off at night and have all of the PDF files created when I get up in the morning.

Thanks for your help an suggestions. Let me know if you have any questions or need any other info from me.....Shaves

Answer 1

As a complement to @kamocyc's answer:

After instantiating WrdApp, add WrdApp.Options.SaveInterval = 0 to turn off AutoRecover, which could otherwise kick in anytime. See Options.SaveInterval property (Word).

If your "template" document is hosted in OneDrive or SharePoint, then after instantiating WrdDoc, add WrdDoc.AutoSaveOn = False so Word does not save it after each modification. See Document.AutoSaveOn property (Word). Note that your version of Word might not support this property.

Note that unlike Excel's, Word's Undo stack builds up as modifications are made programmatically. I regularly call WrdApp.ActiveDocument.UndoClear when automating Word, but it may be overkill (I do think it helps). See Document.UndoClear method (Word). In your case, I would call it after WrdApp.Selection.InsertFile and after WrdApp.Selection.Delete.

Answer 2

The error message "Microsoft Excel is waiting for xxx to completed an OLE action." occurs when another application (Word, in your case) is not responding in a timely manner.

This answer is a solution of the same problem.

As for your additional questions.

I noticed each of these lines has a path and a file name. Would I be better off combining the path and file name before executing this line of code?

No, this makes no difference.

Does it matter if the Word document has 200 or 400 pages when saving it to a PDF file?

Fewer pages are easier to handle for Word and programmers. Nevertheless, Word can handle hundreds of pages of documents.

I turn DisplayAlets off and on when the unique Word documents are being created but I don't use it when the PDF file is being created. Should I?

Maybe. But some people says DisplayAlerts = False doesn't work.

I also notice that there is a message in the lower right hand corner of Word that says "Word is publishing "Flyer_(unique number).pdf and there is a running count of the pages numbers. Is this have any bearing on the issues i'm having.

I think this is normal behavior.

Is there a better way to do what I'm trying to do with Excel, Word, and PDF?

Maybe, creating 2 page PDF files first, then combining them is modular and faster way. But to combine PDF files, a third-party PDF tool is necessary.