Autofill info on a Word Doc with VBA?

[apryor]

OK, I have about 10-15 Word Doc's that users manually enter info in, and I'd like to see if there's a way to automate this process. Aside from Mail merge, is there a way (maybe using VBA) to type in a name (or select it from a drop-down box) and based on the name entered, have it populate the address, city, state and zip automatically? And pull this info from either an access database, or Excel spreadsheet?

AP

 

[TonyJollans]

Hi apryor,

If you have a dropdown you can run code when an item is selected. That code can do whatever you want, certainly including getting data from an external source. When you have your data you can populate your Word document at bookmarked points (or elsewhere, but bookmarks make it easier). Not sure what else to say without a bit more info. How are your Documents set up? As Word Forms?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[apryor]

Right now, each document is set up as a template. Nothing special, just formating. I don't think I've ever used a bookmark in Word. I'll look it up.

Basically, When a user needs that particular doc, they manually enter info into it, then do a save as to create their own copy. I just want a way, kinda like in access, to set up some type of drop down box in a word doc that will automatically fill in the address, city, state, and zip based on that selection.



APryor

 

[fumei]

Hi apryor,

Right now, each document is set up as a template. Nothing special, just formating. I don't think I've ever used a bookmark in Word. I'll look it up.

Basically, When a user needs that particular doc, they manually enter info into it, then do a save as

This is NOT using documents as templates. This is using documents as documents, then saving them as a different name. Templates function differently.

As Tony stated, this is very possible.

Say you a dropdown in a REAL template, although you could do it the same way in a document, as you have been. It would be better (and Word is actually designed to function this way), though, if you used a proper template.

In any case, what you could do is have your dropdown return a single value , say the number in the order of the dropdown list. As in, a list like:

name number 1
name number 2
name number 3
name number 4
name number 5
name number 6

User selects #4. This returns a value of 4. Now you could get incredibly complicated and do all sorts of stuff, but to keep it simple, lets say if the user selects the fourth name, it means "4".

AND, in your Excel file, 4 means the fourth row. In that row, the Column A has the name, Col B has address, Col C has the phone number ....etc etc...or whatever. So your logic, and I can not stress enough that it IS logic, simply states;

1. Determine the order in the list the users selects (#4)
2. Go to Excel
3. Get all the column information in the row of that number.

You can use bookmarks, but I would use form fields (which are avatars of bookmarks). Depending on whether you need to allow user editing (or not) you will have to protect the document for forms. If you are just dumping information into places, you do not need to use protect document to use form fields as receivers.

It is, of course, not quite as easy in the doing as that, but essentially, that is it.


Gerry