[Yorkshire Guy]

Using WinXP with OfficeXP.

This would save me keying time and be more accurate if it could be done:

I have a s/s with a few hundred names and addresses, each has a unique 'id no'; 1 row per N&A: Name, address line 1, address line 2 etc in separate columns.

I routinely have to send individual letters to these people.

I manually look up a N&A in the Excel s/s, make a note of it, then type it into WORD.
(cut & paste isn't much quicker).

What would be really great is if I could type the 'id no' in Word and then for it to do a VLOOKUP against the Excel s/s to find the N&A.

Mail merge is too messy each time to create a single letter.
Typing the letter in Excel doesn't isn't great either!

Any ideas please?

Thanks.

[Rockn]

Why not put them all in an Access database and have a form set up that you can use on an individual or group letter basis? All VBA functionality is in Access and can open templates or word documents from code. A lot more simple than flipping between two dissimilar apps to accomplish a single task.

[Anne Troy]

Or set the document up as a mail merge to the Excel source, and use the QUERY features to get the info you want merged into your doc.

Save the document as a template and hit File-New each time you want to use it. That way, no previous "query" is saved.

[XL Guru]

Or import the Excel file in Outlook, then add the Insert Address button to your Word toolbar.

Rgds,
Andy

[Yorkshire Guy]

Thanks to all of the above:

Rockn - yes I keep coming up with the Access solution, and one day I will have to bite the bullett and convert.

Dreamboat, I didn't want to have to do a Mail Merge with a Query, by the time I've done that I may as well have just typed the N&A.

XLGuru, I hadn't thought about using Outlook address book, it would need constant re-importing as new N&A's are added to the s/s - I'll have to have a look into this.


Please consider this Thread closed as unless there was a simple answer I don't want to waste anyone's time.

Thanks
Michael

PS you know what I really find great with this Forum - I scan a lot of the responses you guys and gals post against other peoples problems and find so much interesting stuff.
How did you get so expert - don't answer that, just keep up what you are doing.
Thanks again.

[XL Guru]

Michael, I didn't get where I am today by not wasting my time.

"Mail merge is too messy each time to create a single letter" suggests you're missing something. I'm far from a Word fan, however I do ALL my form letters AND scratch letters AND envelopes using a table doc as the mailmerge source. You only have to make the "client" docs mailmerge docs one time.

Let's say there's a valid reason for keeping the N&As in Excel. "Each has a unique id no", and you want to "type the 'id no' in Word then have it do a VLOOKUP against the Excel s/s to find the N&A". However, you have "a few hundred names and addresses". How do you know which ID relates to which N&A without physically looking in Excel?

Honestly, if you must keep the Excel link, then make the doc(s) mailmerge(s). You can add the Find Record button to your menu bar & customise its name to &Look. That way, you can hit ALT+L to show it, type the ID#, hit Enter once (or twice at worst), then Esc. Et voila, your ID#/Name/Address is in your doc.

Rgds,
Andy

[Yorkshire Guy]

XL Guru

OK, you've convinced me to try the mailmerge solution, I'll have a go later tonight.

Thanks once more.

PS. Yes, I have to look up the 'id no' first since I generally only have their name to begin with (the s/s is alphabetical by name), [I've seen a few threads re wanting a quick way to 'tab' down to a specific entry, in my case say jump down to the row for Bloggs,Fred - but I don't think there's been an easy way to do that has there? Different sheets per 'tab' isn't a solution for me, I have to have the whole datbase list in one sheet].

Having located the correct row, what I wanted to avoid is:
copy the Name cell, paste into Word, copy the Address Line 1 cell, paste into Word, copy... etc.

Best regards
Michael

[XL Guru]

>> OK, you've convinced me ... I'll have a go later tonight

Good for you ; you won't regret it (I know, I'm gonna regret that).

If you really can't get to grips/comfortable with it, it's dead easy to attach a tiny macro to a button. So that when you click the button,

1. Excel grabs the row #
2. A handful of formulas use the # to create a vertical array of the (horizontal) cells
3. This gets copied to the clipboard.

Then back in Word, you just Paste Special as unformatted text.

Rgds,
Andy

[Yorkshire Guy]

Andy,

Thanks again, I'll try that latest suggestion.

I started on the Mail Merge but as I've just upgraded to Office XP have now discovered how different Mail Merge now is!

I'm away for a few days so won't be able to update 'till next week.

Cheers,
Michael