Retrieving data from a list or database.

[Vic01]

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

[macropod]

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

[eNGiNe]

Originally Posted by macropod

… With either method, there is no programming involved and no links to Excel etc. to maintain.

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>

[Vic01]

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

[Charles Kenyon]

Originally Posted by Vic01

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

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.

[macropod]

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.

[Vic01]

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

[gmaxey]

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

[Vic01]

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.

Originally Posted by gmaxey

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

[fumei]

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.

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.

It looks like coding is required.

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.

[Vic01]

Originally Posted by fumei

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?
If I type in a list box (or similar appropriate object) "Jo", the name "Robert Jones" appears in the list box and I click on it and the name "Robert Jones" is retrieved from the excel file and 'appears' in that place where the list box is, along with that doctor's address (which is in the excel file)

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? I figured that the placing of a list box (or similar appropriate object) would be where the name would 'appear'.

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. Yes


As has been mentioned (using a userform) you not need to type in the names. You can definitely pick up your data from Excel.
If I could not have to go to excel, but just get excel come to me, that would make it easier.

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

[macropod]

Originally Posted by Vic01

If I type in a list box (or similar appropriate object) "Jo", the name "Robert Jones" appears in the list box and I click on it and the name "Robert Jones" is retrieved from the excel file and 'appears' in that place where the list box is, along with that doctor's address (which is in the excel file)

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...

[Vic01]

Originally Posted by macropod

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...

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

[macropod]

Originally Posted by Vic01

I get that my ignorance might make me out to be simple.

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.