Tech Support Guy banner

Vlookup in Word, continued

5430 Views 6 Replies 2 Participants Last post by  Yorkshire Guy
Hi Dreamboat and XLGuru.

A while ago I posted a request for help with getting addresses into Word that were held in an Excel s/s. (s/s is a list of names and addresses, 1 address per row, coulumns = address lines.
(WinXP and OfficeXP).

You both gave me some approaches but I've been chasing some other problems so have only gotten back around to this just now.

This may now be of use to others as I think I have a working solution!

Mail Merge in Office XP isn't immediately as user friendly as in t'old days but Dreamboat's stuff removes some of the mysticism.

Anyway, this appears to work.

1. Set up a Word Template that is a Mail Merge document using the Excel s/s as source.
Enter the name and address lines as merge fields (what a pain in XP as the Address Block doesn't suit UK style addresses!!).

2. When you want to write a new letter and get the required address from the s/s:

A. File / New and select the above template as the document basis.

B. With the Mail Merge toolbar visible, click on the binoculars to open the 'Find Entry' window.

C. Type in the name or membership no or key that you use to select an address row from your s/s. and CANCEL.

d. ??? this doesn't look to change the open document at all, nor does it look as if anything has happened, however:

e. Now press the View Merged Data buitton on the Mail Mege Tioolbar (<<ABC>>),

and dah dah...

f. The required name and address magically appears in a new document and off you go to edit / save / print it etc.

The only 'problem' I have is that if you open WORD and are doing the above and then also want to open the s/s then Excel tells you that the s/s is locked for editing by someone else , but it works fine if the s/s was already open when you then secondly start the merge.

I'm now looking at a nice macro to do the above steps from one button press, but it seems to halt on the Find Entry window.
See less See more
Not open for further replies.
1 - 3 of 7 Posts
Hi again HEWANM. :)

>> Vlookup in Word, continued

The best way to continue is to post an update in the original thread, else it all gets a bit messy. ;)

I may have suggested before relocating your data to a table in a Word file. You may have said that you needed to keep it in Excel. If you could at all move it to Word you'd solve your "locked for editing" issue.

As I may have said, you can set up your s/s so that one cell has a (data validation) dropdown from which you select a name or ID#, & the act of selecting copies that name (or ID#)'s details (with the help of a few formulas) to the clipboard as a vertical array.

From there, you just ALT+TAB to Word and Paste Special as Unformatted Text.

That said, don't take me up on this until DB has looked at your Word issues. If they can be addressed, she'll tell you how.

(The book kinda stands out on the shelf at the comp store, Anne).

See less See more
>> All that would be needed ... would be ...
>> to automate opening the Find Entry

No idea if it's the same thing/method as Word 2K ; try


>> macro line 2 to only run when I'm on the main doc

Yes, I know where you're coming from. I struck out on Google. Plus it's hard for me to test, suitable data-wise. Plus I've always found the ABC button to be a tad klunky (might be just mine).

What I came up with (a) was about as brute force as it gets (b) produced weird results at times. Notwithstanding, see how (if, more likely) it works for you.


ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
If ActiveDocument.MailMerge.ViewMailMergeFieldCodes = True Then
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End If
See less See more
1 - 3 of 7 Posts
Not open for further replies.