From word to an excel vlookup, then result back to word

[jubangy]

Ok, I have a sheet setup in excel, it has a list of clients, in b2 to b10, and in c2-c10 i have their id#'s. in a2 I have a vlookup that looks up the value from a5, (a name) and when it finds it in the list in column b, a2 returns the client Id# from column c.

I need a5, the value to be looked up, to be automatically filled in from whatever word doc is being worked on. I am trying to make this transparent so no matter what word doc is open and being used, a5 is entered in and the vlookup result is entered in the header of the word doc. Is there a way to do this without having to manually open excel each time?

Forgot too mention, am using Office 2003.

Thanks

[jubangy]

Ok, I kind of figured out how to doit without the whole vlookup part, by using mail merge. my question now though, is there a way to make it so:

1. I would open the template and have it so instead of clicking the toolbar button at the top (to select mail merge recipients) I could just click on the word Name (which is where the mailmerge recipients go) and have it do the same as clicking on the mailmerge recipeints button in the toolbar?

2. Have it so when I open the template, I just click on word name as stated above, have it open the mailmerge recipients so I can select who i want, then once i hit ok, i would fill the rest of the document in with my info and then once i hit save have it automatically complete the mailmerge transparently so it is unnoticed and the original template remains unchanged? I am trying to make this so none of the mailmerge stuff is noticeable, and i need it to be able to save each document and only retain the info that was put in through mailmerge not the actual mail merge options.
3. Is there anyway to get rid of the warning that popup when you open a document that has mailmerge in it, the warnign about it running sql commands?

Thankyou for any help anyone is able to provide. I hope this is alittle more doable as my first question I am guessing was not real possible.

[macropod]

Hi jubangy,

You can do this by using your lookup approach and links between the two documents.

If you insert the lookup value/string in the Word document then copy & paste it into Excel, using Edit|Paste Special, checking 'Link' and choosing the plain text option, that will get the lookup value/string from Word into Excel.

If you then copy & paste the returned value from Excel to Word, using Edit|Paste Special, checking 'Link' and choosing the plain text option, that will get the returned value/string from Excel into Word.

Now, if you change the lookup value/string in the Word document, the returned value should likewise change. No vba required.

The only challenge this is likely to present is editing the lookup value/string in the Word document so that it remains contained within the OLE_LINK bookmark that Word uses for this. The easiest way around that is to use a formfield to capture the lookup value/string in the Word document. That may or may not suit your needs, as using it requires the document to be protected for forms.