Merge field from one Word Doc to another Word Doc

[kklaus]

I have a need to feed data from a letter in one Word doc to a letter in another Word doc file. Is this possible? When I try to use the Mail Merge feature in Word it wants a data source like Excel, Access, or a table in Word. I need to feed from one letter to another.

For example, a user would open a template letter and need data filled in from another .doc file that they would select.

Thanks!!

 

[fumei]

Yes it is very possible. You need to spec out in more detail what you want to happen. However, as a general principle, sure, absolutely, you can get text/information from one document and put it into another.

Gerry

 

[kklaus]

I need data entered in the first letter to be feed into a second and eventually a third letter.

The fields are mostly text such as, Name, address, etc. There are some date fields and a couple of currency fields that need to feed over as well.

I'm not sure how to set up the first letter so that the fields have some sort of reference. Also, I'm not sure how to link the 2nd letter to the 1st to pull that data over.

Thanks!

 

[fumei]

The first letter can be set up in a number of ways.

1. FormFields. Use these if the first letter will have data entry. Someone is going to enter information into this original letter. FormFields are a form of bookmark, and you refer to them by name and value.

So, the second letter can:
Open the first letter
Extract the information from the FormField and put that
information into another formfield in letter 2

Something like:

Sub getData()
Dim ThisDoc As Document
Dim ThatDoc As Document
Set ThisDoc = ActiveDocument
Documents.Open Filename:=[i]firstletterFileName[/i}
Set ThatDoc = ActiveDocument
ThisDoc.Activate
ThisDoc.FormFields("Name").Result = _
   ThatDoc.FormFields("Name").Result

etc etc etc
Set ThatDoc = Nothing
Set ThisDoc = Nothing
End Sub

ThisDoc would be letter2, ThatDoc would be letter1.

OR;

2. Bookmarks. An alternative to using formfields. If the information (Name, Address etc etc) in letter 1 is within the Range of a bookmark, again you can:
Open the first letter
Extract the information and put it where you want in
letter 2.
Something like:

Sub getData()
Dim ThisDoc As Document
Dim ThatDoc As Document
Set ThisDoc = ActiveDocument
Documents.Open Filename:=[i]firstletterFileName[/i}
Set ThatDoc = ActiveDocument
ThisDoc.Activate
ThisDoc.Bookmarks("Name").Range.Text = _
   ThatDoc.FormFields("Name").Result

etc etc etc
Set ThatDoc = Nothing
Set ThisDoc = Nothing
End Sub

There are a number of scenarios. Would the other letters have data entry? Does it need to be edited?


Gerry

 

[kklaus]

I like option 1 but have 2 more questions...

1. I have users that will be using this process and do NOT know code. So they will need a way to go select/open "Thatdoc". I can't hard code that path. I'm thinking of some sort of button they could hit that would allow them to go select their doc then it would perform the rest of the code.

2. When I set up a form field in my 1st word doc, and then try to enter data, it keeps removing the form field. I think what I'm doing is hylighting the field and then overwriting it when I type data. Do you know how I can keep the users from doing this?

Thanks!!

 

[fumei]

OK. Something I forgot. What version are you using?

1. Of course your users do not know code, and generally speaking that is a good thing...sort of...kind of...but in any case good design requires that their NEED to know any code = 0.

Absolutely possible to have a process where the user can select the "ThatDoc". Again, you need to spec it out.

Would the ThatDoc be in the same folder as the document being worked on? If not, then you need to be able to browse to the required folder - assuming ( safely I think) that the user can not (not should not need to) know the fully qualified path to the path. Can be done.

Are you going to need more than one ThatDoc? Do you want it to do this process for all files in a folder - ie. pick up information from all files? Just .DOC files? Other criteria? You could, for example, pick up files by their creation date etc.

2. You have a formfield, and when you enter information it removes the formfield? Are you protecting the document for forms? If you are (and you should be), then I can not see how this is happening. If the document is protected then the user can only move between the formfields.

Also, WHICH document is this happening in? The 1st one, or the one in which you are trying to get information from #1?

Gerry

 

[kklaus]

The first letter would be in various folders. I would want the users to Browse and go select the 1st letter. They would just need to select that one .doc file - not multiple.

Thanks!

Kathy

 

[fumei]

Your reply does not quite make sense to me. But I can be rather dense at times.

1. "The first letter would be in various folders."
2. "would just need to select that one .doc file - not multiple"

Sorry, but it it is in "various" folders, then it IS mulitple....at least if it is the same document.

Could you start again and describe exactly what you want to happen?

Are you going to be changing the "source" document?
You have a new document, and you want to pull information out of ONE source document? But that document could be in various versions in various folders?

Gerry

 

[kklaus]

Sorry about that...

I have various users that work on their own accounts. They write a letter to a client with information specific to that client (name, address, etc). Then they write a completely different letter to the same client. Currently, they are retyping all that data into the 2nd letter. Then they turn around later and retype all that same data into a 3rd letter. That's alot of duplicate entry. I thought I could just automate it, so they just have to type the info in the 1st letter and the information needed for the 2nd and 3rd letters would just be copied over.

I was saying "various" folders because every user has their own "filing" system, so the first letter would not always be in the same folder.

Some users save the 1st letter to their "My Documents" on their hard drive. Other users are afraid of losing everything if their PC crashes, so they save the letters on the server. Some users set up folders by month. So accounts they are working on for December go in the December folder. That's why I'd need the users to have the ability to Browse and go select that 1st letter.

I know that was long but I hope it was helpful.

 

[fumei]

Yes it was helpful. Just one more serious question.

"they write a completely different letter to the same client. Currently, they are retyping all that data "

What is different? OK, I can make some guesses. Certain text may be different, but the basic info (Name, Address etc) is the same. Correct?

OK. Again....you have to spec this stuff out.

Is essentially Letter1 the same for everyone? Is it a boilerplate letter, and they just are entering name, address etc.

Can Letter2 and Letter3 be structured with formfields?

Will they be writing these 2nd and 3rd letters right away?

Forgetting about finding Letter1 for the moment, let's say they have Letter1 open.

1. Have formfields for the chunks they are entering (Name, Address etc. Please explicitly name them. Do not accept the default names Word uses. Make the formfield name something like "Name", and "Address".

2. user enters the information, and let's say they also type in blah blah blah.

3. The code below fires as an OnExit macro in the last formfield in Letter1. In any case, Letter2 opens and IT has formfields also named "Name", "Address". If you have declared the documents as object, you can retrieve the information easily between them. The following would be code in Letter1.... something like:

Sub FillLetter2()
Dim Letter1 As Document
Dim Letter2 As Document
Dim myFieldResults() As String
Dim myFieldNames() As String
Dim var, var2
Dim i As Integer
Set Letter1 = ActiveDocument
' in Letter1 build array of both formfield results
' AND names.  Have to Redim array as do not know
' how many formfields there are.  NOTE that the 
' array is 0 based, but formfield index is 1 based
' so need to add 1 to the variable i for formfields

For var = 1 To Letter1.FormFields.Count
    ReDim Preserve myFieldResults(i)
    myFieldResults(i) = Letter1.FormFields(i + 1).Result
    ReDim Preserve myFieldNames(i)
    myFieldNames(i) = Letter1.FormFields(i + 1).Name
    i = i + 1
Next
i = 0
' open Letter2 - assume in same folder
' as Letter1
Documents.Open FileName:="Letter2.doc"
Set Letter2 = ActiveDocument

' using array of names, match formfield
' names in Letter2 to array of results in Letter1
For var2 = 0 To UBound(myFieldResults())
    Letter2.FormFields(myFieldNames(i)).Result = myFieldResults(i)
    i = i + 1
Next
Set Letter2 = Nothing
Set Letter1 = Nothing
End Sub

This is VERY simple stuff here. There are a LOT of things that could go wrong, depending on your design.

Again, you have to spec it out. I suggest you try it out. Make two files. Put in two formfields and rename them Name and Address.

Copy the above and put it in a code module. In the Properties of the Address formfield, make the Sub the OnExit macro. Protect the document for forms. Save the file as Letter1.doc. Then do a SaveAs and save it as Letter2.doc. Exit Letter2. Reopen Letter1 and type in whatever to the Name field and Address field and press Tab (which moves between formfield.

Letter2 will be opened and the fields automatically filled in with the results from Letter1.

I must repeat that this is pretty straightforward, and you HAVE TO be clear about what you are doing with the design of the letters. I do not know what else you need to be able to enter into them.

Gerry