Vlookup in Word, continued

5429 Views 6 Replies 2 Participants Last post by Yorkshire Guy, Apr 8, 2004

Yorkshire Guy

Discussion Starter · Apr 7, 2004

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

Save

Status: Not open for further replies.

1 - 4 of 7 Posts

Yorkshire Guy

Discussion Starter · #3 · Apr 7, 2004

Thanks XLGuru, I did play with selecting and pasting the name and address from Excel direct into Word, including formatting the address lines to remove blank ones etc, then I saw the Find Entry in the new Office XP Mail Merge (I was on Office2K before) and thought this simpler.
The locking issue isn't really much of a problem since I generally have the spreadsheet open anyway.
I don't want to make Word the primary source since the NAme and Address s/s is part of a workbook with lots of other sheets.

All that would be needed to make this a really neat solution would be the macro to automate opening the Find Entry, not proceeding until a find item had been specified, then merging that result into a new doc - I'm still playing with that.

thanks again Andy.

BR
Michael

Save

Yorkshire Guy

Discussion Starter · #5 · Apr 7, 2004

Andy,

you're a wizz, this works a treat:

Dialogs(wdDialogMailMergeFindRecord).Show
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

I was using:
WordBasic.MailMergeFindEntry
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

This is similar.

Do you know how I could condition the toggle to view the searcgh results?
what is happening is If I search for say 'Smith' , the above displays the N&A for the first Smith (I can't see this until I view the merge as from second line of the macro.
If that's the wrong Smith and I repeat the macro, this time it toggles back to the main doc and I don't see the result.
So what I want is the macro line 2 to only run when I'm on the main doc, and not run when I'm already on the merged doc.

I've got to go now , if you have any suggestions I will follow up tomorrow.

thanks

Michael

See less See more

Save

Yorkshire Guy

Discussion Starter · #7 · Apr 8, 2004

Andy,

tried your macro, however the if toggle / don't toggle doesn't work for me.

thinking sideways, the reason to toggle is to see the results of the Find Entry (because Find Next) doesn't show the results).

if instead I use the 'Mail Merge Receipients' button from the Toolbar, this not only shows me the Source list, and when you use 'Find' from there, positions you in the list at the result. So, if this isn't the one you want you use 'Find' again.

Then when you OK that to return to the main doc and press the View Merged Data button, all is OK.

I can't make all that into a new Macro, don't know what the macro line is for 'Mail Merge Receipients' ?

Anyway, maybe this is as good as it gets!

Thanks for all your help.

See less See more

Save

1 - 4 of 7 Posts

Status: Not open for further replies.

Top Contributors this Month