Microsoft Excel - Looping through Word documents using Excel VBA- empty windows left with no document open

Asked By John Wirth on 04-Feb-15 04:52 AM
I am using the following code (simplified version):

Option Explicit

Sub CloseDocs()
Do
Dim wrdApp As Object
Dim wrdD As Object
Set wrdApp = GetObject(, "Word.Application")
wrdApp.Visible = True
 If wrdApp Is Nothing Then
        MsgBox "No instances of Word found"
        Exit Sub
      End If
wrdD.Close SaveChanges = False
Set wrdApp = Nothing
Set wrdD = Nothing
MsgBox ""
Loop
End Sub

I am using Office 2010, so can't use wrdApp.Documents.Count in the loop due to the bug in Word 2010, hence the code above
The code was running fine in 2003. The problem I have is that during the loop, intermitently, a document closes and leaves behind a 'blank' instance of Word with no document, and this causes the code to break before having closed all the documents.
Robbe Morris replied to John Wirth on 04-Feb-15 08:25 AM
I'll be honest, I'm surprised this ever worked.  My experience with COM always required me to use the original reference to the Word.Application (your wrdApp) here that created/opened the doc.  Here, you are getting a new reference in your CloseDocs expecting it to be able to reference the original wrdApp you created to work with the doc.  I would have expected CloseDocs to take an argument to pass in the original reference to wrdApp.  That may ultimately be the solution you have to implement.
John Wirth replied to Robbe Morris on 04-Feb-15 11:38 AM
Hi Robbe- the documents are not opened via code- they are opened separately from the Excel spreadsheet into which they get imported.
Robbe Morris replied to John Wirth on 04-Feb-15 11:59 AM
You may have to resort to killing all the process instances of word.exe.  Something clearly still has a hold on the document.  Perhaps something in Excel during the import.
John Wirth replied to Robbe Morris on 05-Feb-15 04:36 AM
I did consider that it was something in the import code, so I took it all out and still have the same probem. It's annoying because I had code that worked fine before we switched to Office 2010, but am having to rewrite it to get around the bug with Application.Documents.
Harry Boughen replied to John Wirth on 22-Feb-15 02:50 PM
Hello John,
A bit late I know but I have been away.  And I haven't tried anything but it occurs to me that maybe you could just quit each instance of Word rather than closing the document.
So:

wrdD.Close SaveChanges = False
Set wrdApp = Nothing
Set wrdD = Nothing

becomes

wrdApp.Quit
Set wrdApp = Nothing

I haven't tested anything at this stage as still a bit jet lagged.

Harry