First of all, apologies if this has been addressed. I have spent ages looking for specific answers but can't seem to find what I am looking for.

In my profession, I am required to write letters to doctors with information about our mutual patients.

The beginning of the letter starts with the doctor's name and address.
Next the client's name and date of birth are inserted.

Then I write, "Dear Dr xxx, thank you for referring (patient's name)

What I really would like is to be able to type in the doctor's name at the start of the letter, and have the doctor's address appear automatically from lets say an excel file, without me having to go to to that file to physically select it.



I have attached the document.
Thanks, Vic

Attached Files

GP query.docx (17.8 KB, 12 views)

Hi Vic01,

I'd suggest creating a custom autotext or building block entry for each doctor, perhaps containing everything from their address through to the "Dear Dr xxxx,". Then, rather than even having to type the doctor's name, it becomes a simple matter of inputting the relevant keyboard shortcut or choosing the right building block for the occasion. With either method, there is no programming involved and no links to Excel etc. to maintain.

To see how to create autotext/custom building block entries, go to:
http://gregmaxey.com/word_tip_pages/..._autotext.html
See also:
http://word.mvps.org/FAQs/Customization/AutoText.htm

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Unless, of course, the practice is already using a spreadsheet to hold name and address information and the doctor has the impudence to move – then OP will have to check both the spreadsheet and the free-floating Word building block <g>

Thanks so much for your reply.
I have, in fact, an excel sheet with hundreds of doctor's names and addresses.
I'm not keen on having to re-type this information in building blocks or autotext.

Is there a way to retrieve an address automatically, if I type a doctor's name in the document?

Thanks, Vic

It could be done via a selection_change macro, but that would run every time you change the selection (i.e. you type all or part of a name, then select what you've typed). But then there's the question of preventing the macro from updating the document before you'd finished selecting, say, a two word name, and updating after only the first word has been selected.

A better way would be to populate a dropdown with the list of doctors, then choose from that list. For some code to do that, see: https://www.msofficeforums.com/word-...html#post46287. Ideally, that code would reside in the document's template. Some extra work would be required to retrieve the address data as well and to output the chosen set into your document. You might also want to delete the content control upon saving, so the extra data associated with it wouldn't remain in the document.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks so much for your suggestions.
A dropdown list with 200 doctors is not feasible.
Looks like there is no simple way.

I would select the Doctor's name from a Userform list or combobox. You could populate the list or combobox using the excel worksheet.

Some tips for doing this sort of thing is shown here: http://gregmaxey.com/word_tip_pages/..._combobox.html

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Sorry, I must be naive. I just wanted to know a simple way to be able to type in the first one or two letters of a doctor's name, and have corresponding names appear for me to select one of, and have the corresponding address appear. I'd need a step by step set of instructions.
It looks like coding is required. Which is beyond me.
I can add a combo box. That requires me to input each name.

This is all a bit obfuscating.
Thanks for your effort.
I suspect I need to know more about how this works than I do.

Cheers.

Well, it is a little naive.

The names appear...where? Have the addresses appear...where? These things do not appear magically. Word has to be told where and how. YOU have to tell it where and how.

I am not trying to give you a hard time, just trying to give you a stretch of imagination. Say you have Dr. Jones. You type “J” and then “o”. OK. Now where is the “corresponding names” to appear? Right after? So you now have “JoJones”, the Jones following the Jo” you typed?

Now where does the address (assuming you CAN get it) appear? Following the “Jo” (or “JoJones”) ? Someplace else? If so…where?

What you are asking for is not that difficult, it is fairly simple, but it does take effort and planning and very specific instructions. Specifics. Nothing vague.
As you do seem to want to place data gathered from someplace else (lets say your Excel file) at some other defined location, yes coding is likely. How else are you going to tell Word where to put it? Regarding that, part of the build of logic would be to determine if the location to put an address is ALWAYS going to be the same place. No? Then that adds another layer of complexity.

Again, believe it or not it IS (fairly) simple. It just needs to be very very very specific.

As has been mentioned (using a userform) you not need to type in the names. You can definitely pick up your data from Excel.

I use Mail Merge with the information in an Excel sheet. Letter is a new document based on a template linked to the spreadsheet.

"Find Recipient" and put in the last name of the doctor. Press Enter.
Name, address, greeting, all typed into the letter using merge codes.

Then I change it to a normal Word document (under Start Merge) and type your letter.

Thanks for everyone's input. I have enough to try and see if I can achieve my goal.

It's a whole lot more complicated than that. What happens if your database has Dr Robert Jones, Dr Martha Jones, Dr Fred Johns, Dr John Johnson, Dr Ian Johnston and Dr Ann Johnstone? How is typing "Jo" going to get you the required output? Even typing "Jones" or "Johns" won't give you a single match and presumably you don't want your code to choose one at random. The best you'll do in many cases is to obtain a list of possibilities from which you must choose. I ought to know - I worked with electoral rolls for 25 years...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, that would be fine, I could choose from the list of "JOxxxx"

I think you are being a bit sardonic when you say "presumably you don't want your code to choose one at random."

But that's ok, I get that my ignorance might make me out to be simple.

So, "The best you'll do in many cases is to obtain a list of possibilities from which you must choose." would be a fabulous outcome.

Vic

Simple is not the term I'd use. Unaware, perhaps.

As I said "It's a whole lot more complicated". Word has no predictive text functions of its own, so whoever is going to code this would have to do so from scratch. It's not something I've ever contemplated or come across.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

"Unaware", I agree with that definition.
So what I am seeking is ..."a whole lot more complicated".
I can stop trying to solve this quest then.
Mail merge definitely did not give me what I wanted.

Cheers, and thanks for your time.

Vic