Opening a series of Word docs using VBA

[fun4go]

I am trying to create a macro that will import various elements from previous Word documents into a new Word document. A little bit of background is necessary in order to explain the problem. Over time, a number of Word documents will be generated regarding certain customers. The documents will all have names that contain the customer's name and customer number followed by numbers for the date of the document e.g. "Doe, John 123456_070327" would be a document generated on 3/27/07 about customer number 123456 whose name is John Doe. All of the documents are stored within the same folder.

Frequently, I need to bring information from an earlier note on a given customer into the current note. The various types of info that this might apply to are all bookmarked within the documents but not every note has all of the different types of info/bookmarks. Thus, when trying to bring in the last entry for a certain type of info, it might not be in the newest of the earlier notes. I would like to be able to write code that sequentially searches a given customer's documents (in reverse chronologic order) for the appropriate bookmark in order to bring the info into the current document.

So far, the best I have been able to do is use a built-in wdFileOpen dialog box to bring up the list of documents for the customer and then have the user repeatedly manually choose from the list until a document is found that has the appropriate info/bookmark.

Any help would be much appreciated.

Also, while we are on the subject, is there any way to have the current document that is being worked on excluded from the list of documents in the wdFileOpen dialog box?

Thanks.

[bobp0303]

As I understand your question/problem, you already can select the files using a mask or some criteria, so you're half-way home.
The way I've been keeping track of open files is to Dim a collection within a static function (just so I'm not polluting the global name space).

I then use the following code to determine the current document's name: -- I keep my task relatively simple by requiring the user to only have a single document open when my overall process starts. (The errata file is generated when my process encounters a difficulty it can't resolve automatically by applying a rule. -- at the end of the whole process or when a fatal error is encountered I present the errata file to the user.) Anyway, but capturing your working document name before any other files are opened you know what file(s) to skip to avoid double-processing. I presume you have logic to determine what file should be included. By adding each fragment file to the collection you can avoid searching the same document more than once.

        For i = 1 To Windows.Count
            Windows(i).Activate
            If ActiveDocument.Name = ErrataFile Then
                errataDocIndex = i
            ElseIf ActiveDocument.Name = workingDocumentName Then
                userDocIndex = i
                fname = ActiveDocument.Name
            End If
        Next

Hope this helps,
Bob.
(p.s. still looking for how to add a field to an existing form in word Basic 2003)

[Siddharth Rout]

The documents will all have names that contain the customer's name and customer number followed by numbers for the date of the document e.g. "Doe, John 123456_070327" would be a document generated on 3/27/07 about customer number 123456 whose name is John Doe. All of the documents are stored within the same folder.

Hi

The file naming convention and the fact being that they are stored in the same folder will make your work very easy.

1) Use a loop to open the relevant files from that folder in invisible mode using a filename which is generated by concatenating the username and customer number (date as well if required) and the relevant mask.
2) Search the document for relevant bookmarks. If not found, close the file and then move on to the next one and so on...

The coding part won't be easy but this is one way that you can go about it...

Check this site, it tells you on how to work with bookmarks...
http://word.mvps.org/FAQS/MacrosVBA/index.htm

To get the code on how to open a word document simply record a macro and modify it...

I suggest that you give it a shot and if you have any queries simply post it back. There are plenty of members who can help you out

[fun4go]

Thank you both for your suggestions.

Bob: if I understand your post correctly, you are recommending the following sequence of steps:
1) Close all open documents except for the active document in question
2) Open all of the documents that exist for the customer as a collection (an object that I have never used and will have to look in to), using the mask that I have already created for the fileopen dialog box.
3) Once I have all of the files open, it is relatively simple to search through them and find the latest one that has the appropriate bookmark (I can handle it from here).
Seems like a good idea. Let me know if I have understood you correctly.

KoolSid:
The problem I have is that the customer's list of documents includes multiple documents with different date codes at the end of the name. How can I open the files in a loop if I don't know the date codes in advance. Is there any way to loop through the documents in sequence? I think that is what Bob is recommending (i.e. the collection object).

Further suggestions/clarifications would be welcome. I will keep working on it along Bob's lines, in the meantime. I will post updates as needed (i.e. successes and failures).

Thanks again.
Thank you both.

[bobp0303]

Hi -- I'll answer your question properly when I get back to work tomorrow -- don't have Word2003 installed at home. You've got the right idea, but I want to explain the collection object and show how easy it is.

Bob.

[fun4go]

Thank you again for your reply. I may have found somewhat of a solution on a different website ("CodeItBetter" - see below). I extracted the guts of a function that was posted on that website. It allows me to step through the documents of the current customer one by one and to save the filenames in to a string variable array . I have used this code to open each document using the array, and to then check each document for the presence of the appropriate bookmark. It seems to be working so far. I suspect that you had intended to do the same thing using a collection rather than a variable array.

Here is the part of the code that I extracted from the function (ALLOC_CHUNK is a constant that is set at 50):


'Home > How-To Library > File/Folders Handling

'How to retrieve all files from a given directory?

'****************************************************************
'* © 2007 CodeItBetter http://codeitbetter.com *
'* This notice MUST stay intact for legal use *
'****************************************************************
'An array of filenames in a given directory and also demonstrates the correct way to
'set up the loop:


ReDim strFilesToCheck(0 To ALLOC_CHUNK) As String
filename = Dir$(strFileFindName, 0)
Do While Len(filename)
count = count + 1
If count > UBound(strFilesToCheck) Then
' Resize the result array if necessary.
ReDim Preserve strFilesToCheck(0 To count + ALLOC_CHUNK) As String
End If
strFilesToCheck(count) = filename

' Get ready for the next iteration.
filename = Dir$
Loop
' Trim the result array.
ReDim Preserve strFilesToCheck(0 To count) As String

I then added the code that opens the documents and searches for the appropriate bookmark:

blnFound = False
Do Until blnFound
If strFilesToCheck(count) <> ActiveDocument.Name Then
strDocToSearch = strPathName & "\" & strFilesToCheck(count)
Documents.Open filename:=strDocToSearch, Visible:=False, readonly:=True
If Documents(strDocToSearch).Bookmarks.Exists("medications") = True Then
blnFound = True
End If
intDocToClose = Documents.count - 1 'set documents(index) to number that corresponds to the newly opened document
Documents(intDocToClose).Close savechanges:=wdDoNotSaveChanges 'close the document

End If
count = count - 1
Loop




Let me know what you think.
Thanks.