Microsoft Excel - Looping through word documents problem

Asked By John Wirth on 18-Apr-12 01:07 PM

I am looping through a series of Word documents from Excel- this has been working fine, however, what I want to do is loop through them and ignore those documents which do not have the Keyword "CWS report (un processed)". I have tried this by adding a line that the main code will ignore a document that does not have this keyword. However, the loop is set to run until documents count = 0. What I want to do is to specify that the loop runs until there are no further documents with the above keyword. What I have so far is below, however, it causes an endless loop as it is. I am completely stumped!!!!

Do

Dim wdApp As Object
Dim wdDoc As Object
Dim NextRow As Long
Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument
wdApp.WindowState = wdWindowStateMinimize

If wdDoc.BuiltinDocumentProperties("Keywords").Value = "CWS report (un processed)" Then

'MAIN CODE GOES HERE

Loop Until wdApp.Documents.Count = 0
End If
Venkat K replied to John Wirth on 18-Apr-12 09:54 PM
Are you decrementing the document count once you iterate each document
The loop will be ended if you decrement the document count once you have been procedded. Try that.

Pichart Y. replied to John Wirth on 18-Apr-12 10:03 PM
Hi John Wirth,

Could you please enclose the whole code? Need more information.

pichart Y.
Somesh Yadav replied to John Wirth on 18-Apr-12 11:54 PM
Hi,

Why do you need to generate a Word document? In my experience it's much easier to keep everything in Excel.
An Excel sheet can be set out to look like a typical word document. Most users I know invariably prefer to receive a finished document in Excel so that where necessary they can use it as the base for other tasks.

And if the ultimate output is a pdf file there's absolutely no need for Word.

Just a thought....

or

You can use mailmerge in Word to assemble any kind of document, using the information in Excel.
Through the mailmergemethod you can establish a direct connection between the Word-document and the Excel workbook.
By using fields in Word you can import, introduce text conditionally on the values in Excel, etc.

To help you any further it would be wise to post an example Word-document and an example Excel workbook.
wally eye replied to John Wirth on 20-Apr-12 12:00 AM
You might try something like:

for each wdDoc in wdApp.documents
  If wdDoc.BuiltinDocumentProperties("Keywords").Value = "CWS report (un processed)" Then

'MAIN CODE GOES HERE

    End If

next wdDoc

I'm assuming you have a set of documents open in Word and want to loop through them.  If you want to open the files from the folder, it will take a bit more.  You will have to get the list, open each one, check if it has the keyword, process it, close it.
John Wirth replied to wally eye on 24-Apr-12 05:39 PM
Wally eye, thanks so much- just what I needed. I got to focussed on doing it using the Loop function that I got blinkered. This works perfectly for what I needed and I have been able to modify it for other VBA routines I have developed.

Many thanks.
wally eye replied to John Wirth on 24-Apr-12 10:26 PM
Cool!  Glad to be of help.